您正在寻找的关键-将行数据转换为列式。
使用：
WITH summary AS (
SELECT TRUNC(ls.started,'HH') AS dt,
ls.depot,
COUNT(*) AS num_depot
FROM logstats ls
GROUP BY TRUNC(ls.started,'HH'), ls.depot)
SELECT s.dt,
MAX(CASE WHEN s.depot = 'foo' THEN s.num_depot ELSE 0 END) AS "count_of_foo",
MAX(CASE WHEN s.depot = 'bar' THEN s.num_depot ELSE 0 END) AS "count_of_bar"
FROM summary s
GROUP BY s.dt
ORDER BY s.dt
使用：
SELECT s.dt,
MAX(CASE WHEN s.depot = 'foo' THEN s.num_depot ELSE 0 END) AS "count_of_foo",
MAX(CASE WHEN s.depot = 'bar' THEN s.num_depot ELSE 0 END) AS "count_of_bar"
FROM (SELECT TRUNC(ls.started,'HH') AS dt,
ls.depot,
COUNT(*) AS num_depot
FROM LOGSTATS ls
GROUP BY TRUNC(ls.started, 'HH'), ls.depot) s
GROUP BY s.dt
ORDER BY s.dt
在Oracle9i之前的版本中，需要将CASE语句更改为DECODEOracle特定的IF / ELSE逻辑。
未经测试：
SELECT *
FROM (SELECT TRUNC(ls.started, 'HH') AS dt,
ls.depot
FROM LOGSTATS ls
GROUP BY TRUNC(ls.started, 'HH'), ls.depot)
PIVOT (
COUNT(*) FOR depot
)
ORDER BY 1
602392714
清零编程群