数据库版本:oralce 11g
表名WM_TD_WATER_DAILY 结构如下,我把没用到的字段隐藏了,全放出来不太好哈.
MONITOR_ID为BI_TB_MONITOR_BI的ID ,用于关联站点名称(SHORT_NAME).
业务需求 :现有日水量表,每条数据记录着一天的用水量,bi_tb_monitor_bi中有一下九个站点
全部SQL如下,下面有SQL分解,看起来能清晰很多
SELECT MON, CASE
WHEN MON IS NULL AND DATETIME IS NULL THEN
'累计水量'
WHEN MON IS NOT NULL AND DATETIME IS NULL THEN
'小计'
ELSE
CASE
WHEN INSTR(DATETIME, '1旬') > 0 THEN
REPLACE(DATETIME, '1旬', '上旬')
WHEN INSTR(DATETIME, '2旬') > 0 THEN
REPLACE(DATETIME, '2旬', '中旬')
WHEN INSTR(DATETIME, '3旬') > 0 THEN
REPLACE(DATETIME, '3旬', '下旬')
END
END DATETIME,
SUM(NVL(车逻洞, 0)) 车逻洞,
SUM(NVL(车逻闸, 0)) 车逻闸,
SUM(NVL(车逻, 0)) 车逻,
SUM(NVL(南关, 0)) 南关,
SUM(NVL(头闸, 0)) 头闸,
SUM(NVL(周山洞, 0)) 周山洞,
SUM(NVL(界首小闸, 0)) 界首小闸,
SUM(NVL(子英闸, 0)) 子英闸,
SUM(NVL(周山, 0)) 周山,
SUM(NVL(车逻洞, 0)) + SUM(NVL(车逻闸, 0)) + SUM(NVL(南关, 0)) +
SUM(NVL(头闸, 0)) + SUM(NVL(周山洞, 0)) + SUM(NVL(界首小闸, 0)) +
SUM(NVL(子英闸, 0)) + SUM(NVL(周山, 0)) 合计
FROM
(
SELECT TO_CHAR(MONITOR_DATE, 'yyyy') YEAR,
TO_CHAR(MONITOR_DATE, 'yyyy') || TO_CHAR(MONITOR_DATE, 'mm') MON,
TO_CHAR(MONITOR_DATE, 'mm') || '月' ||
DECODE(TRUNC((TO_CHAR(MONITOR_DATE, 'dd') - 1) / 10), 0, '1旬' ,1, '2旬', '3旬') DATETIME,
SUM(车逻洞) / 10000 车逻洞,
SUM(车逻闸) / 10000 车逻闸,
SUM(车逻) / 10000 车逻,
SUM(南关) / 10000 南关,
SUM(头闸) / 10000 头闸,
SUM(周山洞) / 10000 周山洞,
SUM(界首小闸) / 10000 界首小闸,
SUM(子英闸) / 10000 子英闸,
SUM(周山) / 10000 周山
FROM
(
SELECT B.SHORT_NAME, T.MONITOR_DATE, T.WATER_DAILY
FROM WM_TD_WATER_DAILY T
LEFT JOIN BI_TB_MONITOR_BI B
ON T.MONITOR_ID = B.ID
WHERE T.REGION_ID = '6-100-018'
AND T.MONITOR_DATE BETWEEN TO_DATE('2018-10', 'yyyy-MM') AND
TO_DATE('2018-12', 'yyyy-MM')
)
PIVOT(SUM(WATER_DAILY)
FOR SHORT_NAME IN('车逻洞' 车逻洞,
'车逻闸' 车逻闸,
'车逻' 车逻,
'南关' 南关,
'头闸' 头闸,
'周山洞' 周山洞,
'界首小闸' 界首小闸,
'子英闸' 子英闸,
'周山' 周山))
GROUP BY TO_CHAR(MONITOR_DATE, 'yyyy'),
TO_CHAR(MONITOR_DATE, 'mm'),
DECODE(TRUNC((TO_CHAR(MONITOR_DATE, 'dd') - 1) / 10),
0,
'1旬' ,1,
'2旬',
'3旬')
ORDER BY MON
)
GROUP BY ROLLUP(MON, DATETIME)
SQL分解
第一层SQL:
只做最基本的查询,并把条件都写好
SELECT B.SHORT_NAME, T.MONITOR_DATE, T.WATER_DAILY
FROM WM_TD_WATER_DAILY T
LEFT JOIN BI_TB_MONITOR_BI B
ON T.MONITOR_ID = B.ID
WHERE T.REGION_ID = '6-100-018'
AND T.MONITOR_DATE BETWEEN TO_DATE('2018-10', 'yyyy-MM') AND
TO_DATE('2018-12', 'yyyy-MM')
查询结果如下(只插入了两个站点的数据<车逻洞>,<车逻闸>):
第二层SQL:
用decode(trunc((to_char(monitor_date,'dd')-1)/10),0,'1旬',1,'2旬','3旬') 把旬分组,
行转列用了 PIVOT (sum(water_daily) for short_name in ('车逻洞' 车逻洞, '车逻闸' 车逻闸,'车逻' 车逻,
'南关' 南关,'头闸' 头闸,'周山洞' 周山洞,'界首小闸' 界首小闸,'子英闸' 子英闸,'周山' 周山))
每个sum后除以10000是业务需求
SELECT TO_CHAR(MONITOR_DATE, 'yyyy') YEAR,
TO_CHAR(MONITOR_DATE, 'yyyy') || TO_CHAR(MONITOR_DATE, 'mm') MON,
TO_CHAR(MONITOR_DATE, 'mm') || '月' ||
DECODE(TRUNC((TO_CHAR(MONITOR_DATE,'dd') - 1) / 10), 0, '1旬' ,1, '2旬', '3旬')DATETIME,
SUM(车逻洞) / 10000 车逻洞,
SUM(车逻闸) / 10000 车逻闸,
SUM(车逻) / 10000 车逻,
SUM(南关) / 10000 南关,
SUM(头闸) / 10000 头闸,
SUM(周山洞) / 10000 周山洞,
SUM(界首小闸) / 10000 界首小闸,
SUM(子英闸) / 10000 子英闸,
SUM(周山) / 10000 周山
FROM
(
SELECT B.SHORT_NAME, T.MONITOR_DATE, T.WATER_DAILY
FROM WM_TD_WATER_DAILY T
LEFT JOIN BI_TB_MONITOR_BI B
ON T.MONITOR_ID = B.ID
WHERE T.REGION_ID = '6-100-018'
AND T.MONITOR_DATE BETWEEN TO_DATE('2018-10', 'yyyy-MM') AND
TO_DATE('2018-12', 'yyyy-MM')
)
PIVOT(SUM(WATER_DAILY)
FOR SHORT_NAME IN('车逻洞' 车逻洞,
'车逻闸' 车逻闸,
'车逻' 车逻,
'南关' 南关,
'头闸' 头闸,
'周山洞' 周山洞,
'界首小闸' 界首小闸,
'子英闸' 子英闸,
'周山' 周山))
GROUP BY TO_CHAR(MONITOR_DATE, 'yyyy'),
TO_CHAR(MONITOR_DATE, 'mm'),
DECODE(TRUNC((TO_CHAR(MONITOR_DATE, 'dd') - 1) / 10),
0,
'1旬' ,1,
'2旬',
'3旬')
ORDER BY MON
查询结果如下:
最后一层SQL:
用了 GROUP BY ROLLUP (MON,DATETIME) 按MON,DATETIME分组合计
SELECT CASE
WHEN MON IS NULL AND DATETIME IS NULL THEN
'累计水量'
WHEN MON IS NOT NULL AND DATETIME IS NULL THEN
'小计'
ELSE
CASE
WHEN INSTR(DATETIME, '1旬') > 0 THEN
REPLACE(DATETIME, '1旬', '上旬')
WHEN INSTR(DATETIME, '2旬') > 0 THEN
REPLACE(DATETIME, '2旬', '中旬')
WHEN INSTR(DATETIME, '3旬') > 0 THEN
REPLACE(DATETIME, '3旬', '下旬')
END
END DATETIME,
SUM(NVL(车逻洞, 0)) 车逻洞,
SUM(NVL(车逻闸, 0)) 车逻闸,
SUM(NVL(车逻, 0)) 车逻,
SUM(NVL(南关, 0)) 南关,
SUM(NVL(头闸, 0)) 头闸,
SUM(NVL(周山洞, 0)) 周山洞,
SUM(NVL(界首小闸, 0)) 界首小闸,
SUM(NVL(子英闸, 0)) 子英闸,
SUM(NVL(周山, 0)) 周山,
SUM(NVL(车逻洞, 0)) + SUM(NVL(车逻闸, 0)) + SUM(NVL(南关, 0)) +
SUM(NVL(头闸, 0)) + SUM(NVL(周山洞, 0)) + SUM(NVL(界首小闸, 0)) +
SUM(NVL(子英闸, 0)) + SUM(NVL(周山, 0)) 合计
FROM
(
SELECT TO_CHAR(MONITOR_DATE, 'yyyy') YEAR,
TO_CHAR(MONITOR_DATE, 'yyyy') || TO_CHAR(MONITOR_DATE, 'mm') MON,
TO_CHAR(MONITOR_DATE, 'mm') || '月' ||
DECODE(TRUNC((TO_CHAR(MONITOR_DATE,'dd') - 1) / 10), 0, '1旬' ,1, '2旬', '3旬')DATETIME,
SUM(车逻洞) / 10000 车逻洞,
SUM(车逻闸) / 10000 车逻闸,
SUM(车逻) / 10000 车逻,
SUM(南关) / 10000 南关,
SUM(头闸) / 10000 头闸,
SUM(周山洞) / 10000 周山洞,
SUM(界首小闸) / 10000 界首小闸,
SUM(子英闸) / 10000 子英闸,
SUM(周山) / 10000 周山
FROM
(
SELECT B.SHORT_NAME, T.MONITOR_DATE, T.WATER_DAILY
FROM WM_TD_WATER_DAILY T
LEFT JOIN BI_TB_MONITOR_BI B
ON T.MONITOR_ID = B.ID
WHERE T.REGION_ID = '6-100-018'
AND T.MONITOR_DATE BETWEEN TO_DATE('2018-10', 'yyyy-MM') AND
TO_DATE('2018-12', 'yyyy-MM')
)
PIVOT(SUM(WATER_DAILY)
FOR SHORT_NAME IN('车逻洞' 车逻洞,
'车逻闸' 车逻闸,
'车逻' 车逻,
'南关' 南关,
'头闸' 头闸,
'周山洞' 周山洞,
'界首小闸' 界首小闸,
'子英闸' 子英闸,
'周山' 周山))
GROUP BY TO_CHAR(MONITOR_DATE, 'yyyy'),
TO_CHAR(MONITOR_DATE, 'mm'),
DECODE(TRUNC((TO_CHAR(MONITOR_DATE, 'dd') - 1) / 10),
0,
'1旬' ,1,
'2旬',
'3旬')
ORDER BY MON
)
GROUP BY ROLLUP(MON, DATETIME)
查询结果如下:
select后的不加case when的效果如下
mon用于第二层的排序 省略掉了
总结
这段SQL中用到了以下函数:
TO_DATE(),TO_CHAR(),BETWEEN,TRUNC(),DECODE(),NVL(),SUM(),PIVOT( FOR ),CASE WHEN THEN ELSE END,ROLLUP()
欢迎指正并优化SQL!