我有一個表的值，其中“TAHUN”列按 date_part('year'::text, insert_date) 分組，“TAHUN”列中的值 1000 作為總數
我想將行轉換為這樣的列（但“TAHUN”列保持動態，因此我不必在年末手動插入它”：
我對使用 unnest 按“TAHUN”列過濾感到困惑。這是我的查詢：
SELECT
unnest(array['PENDAPATAN', 'PENJUALAN 1', 'PENJUALAN 2', 'PENDAPATAN LAIN-LAIN', 'TOTAL PENDAPATAN', 'HARGA POKOK PENJUALAN (HPP)', 'PEMBELIAN', 'ONGKOS KIRIM']) AS "title",
unnest(array["PENDAPATAN", "PENJUALAN 1", "PENJUALAN 2", "PENDAPATAN LAIN-LAIN", "TOTAL PENDAPATAN", "HARGA POKOK PENJUALAN (HPP)", "PEMBELIAN", "ONGKOS KIRIM"]) FILTER (WHERE "TAHUN" != 1000) AS "dynamic year",
unnest(array["PENDAPATAN", "PENJUALAN 1", "PENJUALAN 2", "PENDAPATAN LAIN-LAIN", "TOTAL PENDAPATAN", "HARGA POKOK PENJUALAN (HPP)", "PEMBELIAN", "ONGKOS KIRIM"]) FILTER (WHERE "TAHUN" = 1000) AS "total"
FROM vw_blm_dashboard_hasil_usaha
GROUP BY "title", "dynamic year", "total";
可以取消嵌套過濾嗎？或者您有其他方法可以解決此問題嗎？
之前謝謝
uj5u.com熱心網友回復：
您需要將取消嵌套移動到 FROM 子句中，以便獲得可以參考的列。
我個人發現使用 VALUES 子句寫這個更容易閱讀：
SELECT
x.title ,
max(x.value) FILTER (WHERE v."TAHUN" = 2019) AS "2019",
max(x.value) FILTER (WHERE v."TAHUN" = 2020) AS "2020",
max(x.value) FILTER (WHERE v."TAHUN" = 2021) AS "2021",
max(x.value) FILTER (WHERE v."TAHUN" = 1000) AS "total"
FROM vw_blm_dashboard_hasil_usaha v
CROSS JOIN LATERAL (
values ("PENDAPATAN", 'PENDAPATAN'),
("PENJUALAN 1", 'PENJUALAN 1'),
("PENJUALAN 2", 'PENJUALAN 2'),
... all other columns come here ...
) as x(value, title)
GROUP BY x.title;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/340191.html
標籤：PostgreSQL 枢 交叉表 取消嵌套
上一篇：為什么這個SQL有效？[復制]