不使用crosstab實現PostgreSQL的行轉列應用

標籤：postgresql   行轉列   

表結構及資料

select logdatetime,sum(login) as login,sum(logo) as logo

from(
SELECT date(logdatetime) AS "logdatetime", case logfrom when ‘login‘ then COUNT(id) else 0 end AS "login",
case logfrom when ‘logo‘ then COUNT(id) else 0 end AS "logo"
FROM "log" WHERE date(logdatetime) >= ‘2014-11-04‘ AND date(logdatetime) <= ‘2014-11-05‘ GROUP BY "logdatetime", "logfrom"
) T
GROUP BY "logdatetime"

ORDER BY "logdatetime" ASC

執行結果

不使用crosstab實現PostgreSQL的行轉列應用