【PostGresql】-----PG按本月、本年数据统计并且行数据转列字段数据查询

select t.* from 
(
SELECTwx."TBDW" AS "CNAME",'本月' "TYPE",SUM(CASE WHEN zy."QYTSMC" = '废乳化液' THEN  CAST("SJJSL" as numeric)  ELSE 0 END) AS "废乳化液",SUM(CASE WHEN zy."QYTSMC" = '废包装物（吨）' THEN CAST("SJJSL" as numeric)  ELSE 0 END) AS "废包装物（吨）",SUM(CASE WHEN zy."QYTSMC" = '含油残渣（吨）' THEN CAST("SJJSL" as numeric)  ELSE 0 END) AS "含油残渣（吨）",SUM(CASE WHEN zy."QYTSMC" = '废润滑油（吨）' THEN CAST("SJJSL" as numeric)  ELSE 0 END) AS "废润滑油（吨）",SUM(CASE WHEN zy."QYTSMC" = '废油桶(个)' THEN CAST("SJJSL" as numeric)  ELSE 0 END) AS "废油桶(个)"
FROM "JT_WXFWZYQKXX" wx LEFT JOIN "JT_WXFWZYQKXX_ZYMXB" zy ON zy."MASTER_DATA_ID"= wx."ID" WHERE TO_DATE(wx."NY", 'YYYY-MM-DD') BETWEEN '2025-04-01' AND '2025-04-30' 
GROUP BY  wx."TBDW" UNION ALLSELECTwx."TBDW" AS "CNAME",'本年' "TYPE",SUM(CASE WHEN zy."QYTSMC" = '废乳化液' THEN  CAST("SJJSL" as numeric)  ELSE 0 END) AS "废乳化液",SUM(CASE WHEN zy."QYTSMC" = '废包装物（吨）' THEN CAST("SJJSL" as numeric)  ELSE 0 END) AS "废包装物（吨）",SUM(CASE WHEN zy."QYTSMC" = '含油残渣（吨）' THEN CAST("SJJSL" as numeric)  ELSE 0 END) AS "含油残渣（吨）",SUM(CASE WHEN zy."QYTSMC" = '废润滑油（吨）' THEN CAST("SJJSL" as numeric)  ELSE 0 END) AS "废润滑油（吨）",SUM(CASE WHEN zy."QYTSMC" = '废油桶(个)' THEN CAST("SJJSL" as numeric)  ELSE 0 END) AS "废油桶(个)"
FROM "JT_WXFWZYQKXX" wx LEFT JOIN "JT_WXFWZYQKXX_ZYMXB" zy ON zy."MASTER_DATA_ID"= wx."ID"  WHERE TO_DATE(wx."NY", 'YYYY-MM-DD') BETWEEN '2025-01-01' AND '2025-12-31' 
GROUP BY  wx."TBDW"  
)
t
order by t."CNAME" desc