看看我这个sql 语句 怎么简化下。。。

wanfeng 2018-08-02 01:48:19


with 汇总数据 as(
select COALESCE( a.def_一级单位id, b.def_一级单位id, c.def_一级单位id, d.def_一级单位id ) AS 项目id,
isnull( SUM (三穿_导流渠 ), 0 ) AS 三穿_导流渠,
isnull( SUM (三穿_围堰 ), 0 ) AS 三穿_围堰,
isnull( SUM (三穿_垫层 ), 0 ) AS 三穿_垫层,
isnull( SUM (三穿_稳管 ), 0 ) AS 三穿_稳管,
isnull( SUM (三穿_管沟回填 ), 0 ) AS 三穿_管沟回填,
isnull( SUM (隧道_导流渠 ), 0 ) AS 隧道_导流渠,
isnull( SUM (隧道_初期支护 ), 0 ) AS 隧道_初期支护,
isnull( SUM (隧道_弃渣场挡墙 ), 0 ) AS 隧道_弃渣场挡墙,
isnull( SUM (土石方_扫线 ), 0 ) AS 土石方_扫线,
isnull( SUM (土石方_石方爆破 ), 0 ) AS 土石方_石方爆破,
isnull( SUM (土石方_管沟开挖 ), 0 ) AS 土石方_管沟开挖,
isnull( SUM (土石方_地貌恢复 ), 0 ) AS 土石方_地貌恢复,
isnull( SUM (土石方_一次回填 ), 0 ) AS 土石方_一次回填,
isnull( SUM (土石方_二次回填 ), 0 ) AS 土石方_二次回填,
isnull( SUM (防腐_补口数量 ), 0 ) AS 防腐_补口数量
FROM
(
SELECT
t.[def_一级单位id],
SUM ( t.[导流渠] ) AS 三穿_导流渠,
SUM ( t.[围堰] ) AS 三穿_围堰,
SUM ( t.[垫层] ) AS 三穿_垫层,
SUM ( t.[稳管] ) AS 三穿_稳管,
SUM ( t.[管沟回填] ) AS 三穿_管沟回填
FROM
dbo.[V_198_38918_三穿施工信息填报] AS t
GROUP BY

t.[def_一级单位id]
) AS a
FULL JOIN (
SELECT
sd.[def_一级单位id],
SUM ( sd.[导流渠] ) AS 隧道_导流渠,
SUM ( sd.[初期支护] ) AS 隧道_初期支护,
SUM ( sd.[弃渣场挡墙] ) AS 隧道_弃渣场挡墙
FROM
dbo.[V_198_38919_隧道施工信息填报] AS sd
GROUP BY
sd.[def_一级单位id]
) AS b ON a.[def_一级单位id] = b.[def_一级单位id]
FULL JOIN (
SELECT
ts.[def_一级单位id],
SUM ( ts.[扫线] ) AS 土石方_扫线,
SUM ( ts.[石方爆破] ) AS 土石方_石方爆破,
SUM ( ts.[管沟开挖] ) AS 土石方_管沟开挖,
SUM ( ts.[地貌恢复] ) AS 土石方_地貌恢复,
SUM ( ts.[一次回填] ) AS 土石方_一次回填,
SUM ( ts.[二次回填] ) AS 土石方_二次回填
FROM
dbo.[V_198_38920_土石方施工信息填报] AS ts
GROUP BY
ts.[def_一级单位id]
) AS c ON a.[def_一级单位id] = c.[def_一级单位id]
FULL JOIN ( SELECT ff.[def_一级单位id], SUM ( ff.[补口数量] ) AS 防腐_补口数量
FROM dbo.[V_198_38921_防腐补口信息填报] AS ff
GROUP BY ff.[def_一级单位id]) AS d ON a.[def_一级单位id] = d.[def_一级单位id]
where
COALESCE( a.def_一级单位id, b.def_一级单位id, c.def_一级单位id, d.def_一级单位id )=1432
GROUP BY
COALESCE( a.def_一级单位id, b.def_一级单位id, c.def_一级单位id, d.def_一级单位id)

),
昨日数据 as(
select COALESCE( a.def_一级单位id, b.def_一级单位id, c.def_一级单位id, d.def_一级单位id ) AS 项目id,
isnull( SUM (三穿_导流渠 ), 0 ) AS 三穿_导流渠,
isnull( SUM (三穿_围堰 ), 0 ) AS 三穿_围堰,
isnull( SUM (三穿_垫层 ), 0 ) AS 三穿_垫层,
isnull( SUM (三穿_稳管 ), 0 ) AS 三穿_稳管,
isnull( SUM (三穿_管沟回填 ), 0 ) AS 三穿_管沟回填,
isnull( SUM (隧道_导流渠 ), 0 ) AS 隧道_导流渠,
isnull( SUM (隧道_初期支护 ), 0 ) AS 隧道_初期支护,
isnull( SUM (隧道_弃渣场挡墙 ), 0 ) AS 隧道_弃渣场挡墙,
isnull( SUM (土石方_扫线 ), 0 ) AS 土石方_扫线,
isnull( SUM (土石方_石方爆破 ), 0 ) AS 土石方_石方爆破,
isnull( SUM (土石方_管沟开挖 ), 0 ) AS 土石方_管沟开挖,
isnull( SUM (土石方_地貌恢复 ), 0 ) AS 土石方_地貌恢复,
isnull( SUM (土石方_一次回填 ), 0 ) AS 土石方_一次回填,
isnull( SUM (土石方_二次回填 ), 0 ) AS 土石方_二次回填,
isnull( SUM (防腐_补口数量 ), 0 ) AS 防腐_补口数量
FROM
(
SELECT
t.[def_一级单位id],
SUM ( t.[导流渠] ) AS 三穿_导流渠,
SUM ( t.[围堰] ) AS 三穿_围堰,
SUM ( t.[垫层] ) AS 三穿_垫层,
SUM ( t.[稳管] ) AS 三穿_稳管,
SUM ( t.[管沟回填] ) AS 三穿_管沟回填
FROM
dbo.[V_198_38918_三穿施工信息填报] AS t
where DateDiff(dd,t.填报时间,getdate())=1
GROUP BY

t.[def_一级单位id]
) AS a
FULL JOIN (
SELECT
sd.[def_一级单位id],
SUM ( sd.[导流渠] ) AS 隧道_导流渠,
SUM ( sd.[初期支护] ) AS 隧道_初期支护,
SUM ( sd.[弃渣场挡墙] ) AS 隧道_弃渣场挡墙
FROM
dbo.[V_198_38919_隧道施工信息填报] AS sd
where DateDiff(dd,sd.填报时间,getdate())=1
GROUP BY
sd.[def_一级单位id]
) AS b ON a.[def_一级单位id] = b.[def_一级单位id]
FULL JOIN (
SELECT
ts.[def_一级单位id],
SUM ( ts.[扫线] ) AS 土石方_扫线,
SUM ( ts.[石方爆破] ) AS 土石方_石方爆破,
SUM ( ts.[管沟开挖] ) AS 土石方_管沟开挖,
SUM ( ts.[地貌恢复] ) AS 土石方_地貌恢复,
SUM ( ts.[一次回填] ) AS 土石方_一次回填,
SUM ( ts.[二次回填] ) AS 土石方_二次回填
FROM
dbo.[V_198_38920_土石方施工信息填报] AS ts
where DateDiff(dd,ts.填报时间,getdate())=1
GROUP BY
ts.[def_一级单位id]
) AS c ON a.[def_一级单位id] = c.[def_一级单位id]
FULL JOIN ( SELECT ff.[def_一级单位id],
SUM ( ff.[补口数量] ) AS 防腐_补口数量
FROM dbo.[V_198_38921_防腐补口信息填报] AS ff
where DateDiff(dd,ff.填报时间,getdate())=1
GROUP BY ff.[def_一级单位id]) AS d ON a.[def_一级单位id] = d.[def_一级单位id]
where
COALESCE( a.def_一级单位id, b.def_一级单位id, c.def_一级单位id, d.def_一级单位id )=1432
GROUP BY
COALESCE( a.def_一级单位id, b.def_一级单位id, c.def_一级单位id, d.def_一级单位id)

),
今日数据 as(
select COALESCE( a.def_一级单位id, b.def_一级单位id, c.def_一级单位id, d.def_一级单位id ) AS 项目id,
isnull( SUM (三穿_导流渠 ), 0 ) AS 三穿_导流渠,
isnull( SUM (三穿_围堰 ), 0 ) AS 三穿_围堰,
isnull( SUM (三穿_垫层 ), 0 ) AS 三穿_垫层,
isnull( SUM (三穿_稳管 ), 0 ) AS 三穿_稳管,
isnull( SUM (三穿_管沟回填 ), 0 ) AS 三穿_管沟回填,
isnull( SUM (隧道_导流渠 ), 0 ) AS 隧道_导流渠,
isnull( SUM (隧道_初期支护 ), 0 ) AS 隧道_初期支护,
isnull( SUM (隧道_弃渣场挡墙 ), 0 ) AS 隧道_弃渣场挡墙,
isnull( SUM (土石方_扫线 ), 0 ) AS 土石方_扫线,
isnull( SUM (土石方_石方爆破 ), 0 ) AS 土石方_石方爆破,
isnull( SUM (土石方_管沟开挖 ), 0 ) AS 土石方_管沟开挖,
isnull( SUM (土石方_地貌恢复 ), 0 ) AS 土石方_地貌恢复,
isnull( SUM (土石方_一次回填 ), 0 ) AS 土石方_一次回填,
isnull( SUM (土石方_二次回填 ), 0 ) AS 土石方_二次回填,
isnull( SUM (防腐_补口数量 ), 0 ) AS 防腐_补口数量
FROM
(
SELECT
t.[def_一级单位id],
SUM ( t.[导流渠] ) AS 三穿_导流渠,
SUM ( t.[围堰] ) AS 三穿_围堰,
SUM ( t.[垫层] ) AS 三穿_垫层,
SUM ( t.[稳管] ) AS 三穿_稳管,
SUM ( t.[管沟回填] ) AS 三穿_管沟回填
FROM
dbo.[V_198_38918_三穿施工信息填报] AS t
where DateDiff(dd,t.填报时间,getdate())=0
GROUP BY

t.[def_一级单位id]
) AS a
FULL JOIN (
SELECT
sd.[def_一级单位id],
SUM ( sd.[导流渠] ) AS 隧道_导流渠,
SUM ( sd.[初期支护] ) AS 隧道_初期支护,
SUM ( sd.[弃渣场挡墙] ) AS 隧道_弃渣场挡墙
FROM
dbo.[V_198_38919_隧道施工信息填报] AS sd
where DateDiff(dd,sd.填报时间,getdate())=0
GROUP BY
sd.[def_一级单位id]
) AS b ON a.[def_一级单位id] = b.[def_一级单位id]
FULL JOIN (
SELECT
ts.[def_一级单位id],
SUM ( ts.[扫线] ) AS 土石方_扫线,
SUM ( ts.[石方爆破] ) AS 土石方_石方爆破,
SUM ( ts.[管沟开挖] ) AS 土石方_管沟开挖,
SUM ( ts.[地貌恢复] ) AS 土石方_地貌恢复,
SUM ( ts.[一次回填] ) AS 土石方_一次回填,
SUM ( ts.[二次回填] ) AS 土石方_二次回填
FROM
dbo.[V_198_38920_土石方施工信息填报] AS ts
where DateDiff(dd,ts.填报时间,getdate())=0
GROUP BY
ts.[def_一级单位id]
) AS c ON a.[def_一级单位id] = c.[def_一级单位id]
FULL JOIN ( SELECT ff.[def_一级单位id],
SUM ( ff.[补口数量] ) AS 防腐_补口数量
FROM dbo.[V_198_38921_防腐补口信息填报] AS ff
where DateDiff(dd,ff.填报时间,getdate())=0
GROUP BY ff.[def_一级单位id]) AS d ON a.[def_一级单位id] = d.[def_一级单位id]
where
COALESCE( a.def_一级单位id, b.def_一级单位id, c.def_一级单位id, d.def_一级单位id )=1432
GROUP BY
COALESCE( a.def_一级单位id, b.def_一级单位id, c.def_一级单位id, d.def_一级单位id)
)
SELECT 序号,分项名称,累加进度值,isnull(今日进度,0) as 今日进度,isnull(昨日进度,0) as 昨日进度 from
(SELECT ROW_NUMBER() OVER(order by 分项名称 desc) as 序号 ,累加进度值,分项名称 from 汇总数据
UNPIVOT
(
累加进度值 FOR 分项名称 IN
(
[三穿_导流渠],
[三穿_围堰],
[三穿_垫层],
[三穿_稳管],
[三穿_管沟回填],
[隧道_导流渠],
[隧道_初期支护],
[隧道_弃渣场挡墙],
[土石方_扫线],
[土石方_石方爆破],
[土石方_管沟开挖],
[土石方_地貌恢复],
[土石方_一次回填],
[土石方_二次回填],
[防腐_补口数量]
)
)P) as e
LEFT JOIN (SELECT 昨日进度,昨日数据名称 from 昨日数据
UNPIVOT
(
昨日进度 FOR 昨日数据名称 IN
(
[三穿_导流渠],
[三穿_围堰],
[三穿_垫层],
[三穿_稳管],
[三穿_管沟回填],
[隧道_导流渠],
[隧道_初期支护],
[隧道_弃渣场挡墙],
[土石方_扫线],
[土石方_石方爆破],
[土石方_管沟开挖],
[土石方_地貌恢复],
[土石方_一次回填],
[土石方_二次回填],
[防腐_补口数量]
)
)P) as f on f.昨日数据名称=e.分项名称
LEFT JOIN (SELECT 今日进度,今日数据名称 from 今日数据
UNPIVOT
(
今日进度 FOR 今日数据名称 IN
(
[三穿_导流渠],
[三穿_围堰],
[三穿_垫层],
[三穿_稳管],
[三穿_管沟回填],
[隧道_导流渠],
[隧道_初期支护],
[隧道_弃渣场挡墙],
[土石方_扫线],
[土石方_石方爆破],
[土石方_管沟开挖],
[土石方_地貌恢复],
[土石方_一次回填],
[土石方_二次回填],
[防腐_补口数量]
)
)P) as h on h.今日数据名称=e.分项名称

...全文
239 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
kaerme 2018-08-03
  • 打赏
  • 举报
回复
这个语句太长,建议写函数吧。为了维护与升级
xiaoxiangqing 2018-08-03
  • 打赏
  • 举报
回复
太长了,看得眼都花了。
shinger126 2018-08-03
  • 打赏
  • 举报
回复
这样的脚本,可读性太差了。里面反复调用或者经常调用的子查询做成视图。或者用存储过程来做,使用临时表存储中间表数据来关联。
丰云 2018-08-02
  • 打赏
  • 举报
回复
你们慢慢玩。。。。
我就随便逛逛。。。。

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧