sql

最新推荐文章于 2024-07-05 10:44:41 发布

xianya1013

最新推荐文章于 2024-07-05 10:44:41 发布

阅读量154

点赞数

本文链接：https://blog.csdn.net/Outsider_Lily/article/details/106193014

版权

1. group后不同列相加：直接栏位名相加即可

SELECT TO_CHAR(TMDT, 'yyyy-mm-dd hh24:mi') TMDT, 
       SUM(M1_COL+M2_COL) as sum_col
FROM "table_name" 
WHERE TMDT> SYSDATE-60 
      AND "NAME" in ('condition1', 'condition2') 
GROUP BY TO_CHAR(TMDT, 'yyyy-mm-dd hh24:mi');

2. 根据条件筛选后，行转列；有group, 必须用聚合函数

SELECT TO_CHAR(TMDT, 'yyyy-mm-dd hh24:mi') TMDT,
       MAX(CASE WHEN "NAME" = 'M1_COL' THEN V_COL END) AS V1_COl,
       SUM(CASE WHEN "NAME" = 'M2_COL' THEN V_COL END) AS V2_COl,
       COUNT(CASE WHEN "NAME" = 'M3_COL' THEN V_COL END) AS V3_COl
FROM "table_name" 
WHERE TMDT> SYSDATE-60 
      AND "NAME" in ('G11_CT01_TW_OUT', 'G11_CT01_OUT_TI_OUT','G11_CT01_TT_OUT')
GROUP BY TO_CHAR(TMDT, 'yyyy-mm-dd hh24:mi') ;