您好，我創建了一個視圖，但我想用動態年份來旋轉它。
旋轉前的輸出：
預期輸出：
我的查詢：
SELECT *
FROM crosstab(
' select b.jenisiuran,
date_part(''year''::text, a.insertdate) AS tahun,
sum(b.jumlah_amt) AS jumlah
FROM blm_dpembayaraniuran a
JOIN blm_dpembayaraniuranline b ON a.blm_dpembayaraniuran_key::text = b.blm_dpembayaraniuran_key::text
GROUP BY date_part(''year''::text, a.insertdate), b.jenisiuran'
) AS (TRANSAKSI TEXT, "2019" NUMERIC, "2020" NUMERIC, "2021" numeric);
我收到這樣的錯誤：
ERROR: invalid return type
Detail: SQL rowid datatype does not match return rowid datatype.
謝謝你幫助我
uj5u.com熱心網友回復：
我發現使用過濾聚合比使用過濾聚合更容易 crosstab()
select b.jenisiuran as transaksi,
sum(b.jumlah_amt) filter (where extract(year from a.insertdate) = 2019) as "2019",
sum(b.jumlah_amt) filter (where extract(year from a.insertdate) = 2020) as "2020",
sum(b.jumlah_amt) filter (where extract(year from a.insertdate) = 2021) as "2021",
sum(b.jumlah_amt) as total
FROM blm_dpembayaraniuran a
JOIN blm_dpembayaraniuranline b ON a.blm_dpembayaraniuran_key::text = b.blm_dpembayaraniuran_key::text
WHERE a.insertdate >= date '2019-01-01'
AND a.insertdate < date '2022-01-01'
GROUP b.jenisiuran;
添加范圍條件inserdate應該可以提高性能，因為只需要對所需范圍內的行進行分組，而不是對兩個表中的所有行進行分組。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/317106.html
標籤：PostgreSQL 枢 交叉表