SELECT
t.CLOCK_IN_DATE,
t.AUTO_CODE,
SUM ( one ) one,
SUM ( two ) two,
SUM ( three ) three ,
sum(one+two+three)
FROM
(
SELECT SUBSTRING
( asw.CLOCK_IN_DATE, 0, 11 ) CLOCK_IN_DATE,
asw.AUTO_CODE,
CASE
WHEN gqs.recordType = 0 THEN
CONVERT ( DECIMAL ( 9, 1 ), gqs.deskTotal ) ELSE 0
END one,
CASE
WHEN gqs.recordType = 1 THEN
CONVERT ( DECIMAL ( 9, 1 ), gqs.deskTotal ) ELSE 0
END two,
CASE
WHEN gqs.recordType = 2 THEN
CONVERT ( DECIMAL ( 9, 1 ), gqs.deskTotal ) ELSE 0
END three
FROM
AFFOREST_SUPPLY_WATER asw
LEFT JOIN greenQsSetting gqs ON asw.waterClass= gqs.id
WHERE
asw.waterClass IS NOT NULL
GROUP BY
SUBSTRING ( asw.CLOCK_IN_DATE, 0, 11 ),
asw.AUTO_CODE,
asw.waterClass,
gqs.recordType,
gqs.deskTotal
) t
GROUP BY
t.CLOCK_IN_DATE,
t.AUTO_CODE
sqlserver合并拆分数据
最新推荐文章于 2023-05-22 16:38:55 发布