sql从表数据汇总问题
表 people
id username catalog create_time Change
1 a_1 1 2012-1-10 16:16:26.123 0
2 a_2 2 2012-2-11 16:16:26.123 0
3 a_3 2 2012-2-5 16:16:26.123 1
4 a_4 3 2012-2-7 16:16:26.123 2
5 a_5 4 2012-1-12 16:16:26.123 1
6 a_6 3 2012-3-17 16:16:26.123 1
7 a_7 4 2012-4-5 16:16:26.123 2
8 a_8 1 2012-4-10 16:16:26.123 2
9 a_9 2 2012-5-10 16:16:26.123 0
10 a_10 3 2012-6-10 16:16:26.123 0
11 a_11 4 2012-7-20 16:16:26.123 1
12 a_12 3 2012-7-10 16:16:26.123 2
13 a_13 2 2012-8-10 16:16:26.123 1
14 a_14 1 2012-8-10 16:16:26.123 0
15 a_15 1 2012-7-10 16:16:26.123 0
16 a_16 2 2012-9-10 16:16:26.123 1
17 a_17 3 2012-8-10 16:16:26.123 1
18 a_17 3 2012-10-10 16:16:26.123 2
19 a_18 4 2012-11-10 16:16:26.123 1
20 a_19 2 2012-12-10 16:16:26.123 0
catalog：1代表工人，2代表知青，3代表干部,4代表先进分子
Change：0无变化，1调入，2调出
原有人数是change=0,不包含调入和调出，现有人数是原有人+上调入人数-调出
x表示汇总数如果没有显示空
*结果
------------------------
季度 项目 原有数 调出 调入 现有数
------------------------
| | 工人总数 X个 X个 X个 X个
| | 知青总数 X个 X个 X个 X个
| 一 | 干部总数 X个 X个 X个 X个
| | 先进分子总数 X个 X个 X个 X个
| | 工人总数 X个 X个 空 X个
| | 知青总数 X个 X个 X个 X个
| 二 | 干部总数 X个 空 X个 X个
| | 先进分子总数 X个 X个 X个 X个
| | 工人总数 X个 X个 X个 X个
| | 知青总数 X个 X个 X个 X个
| 三 | 干部总数 X个 空 X个 X个
| | 先进分子总数 X个 X个 X个 X个
| | 工人总数 X个 X个 X个 X个
| | 知青总数 X个 X个 X个 X个
| 三 | 干部总数 X个 空 X个 X个
| | 先进分子总数 X个 X个 X个 X个
[解决办法]
;with T1 as
( select datepart(qq,create_time) as jd, catalog,Change,count(1)as rs from people
group by datepart(qq,create_time),catalog,Change )
select jd,
catalog,
sum(case when Change = 0 then rs else 0 end ) as '原有数',
sum(case when Change = 1 then rs else 0 end ) as '调入',
sum(case when Change = 2 then rs else 0 end ) as '调出',
sum(rs) as '现有数'
from T1
group by jd,
catalog
order by jd,
catalog
[解决办法]
你这个不难，问题是太长了。你试试数据是不是你要的。
SELECT * ,
CASE WHEN ( ISNULL([原有数], 0) + ISNULL([调出], 0) - ISNULL([调入], 0) ) <= 0
THEN NULL
ELSE ISNULL([原有数], 0) + ISNULL([调出], 0) - ISNULL([调入], 0)
END AS '现有数'
FROM ( SELECT '一' AS '季度' ,
CASE WHEN [catalog] = 1 THEN '工人总数'
WHEN [catalog] = 2 THEN '知青总数'
WHEN [catalog] = 3 THEN '干部总数'
WHEN [catalog] = 4 THEN '先进分子总数'
END '项目' ,
SUM(CASE WHEN [catalog] = 1
AND change = 0 THEN 1
WHEN [catalog] = 2
AND change = 0 THEN 1
WHEN [catalog] = 3
AND change = 0 THEN 1
WHEN [catalog] = 4
AND change = 0 THEN 1
END) [原有数] ,
SUM(CASE WHEN [catalog] = 1
AND change = 2 THEN 1
WHEN [catalog] = 2
AND change = 2 THEN 1
WHEN [catalog] = 3
AND change = 2 THEN 1
WHEN [catalog] = 4
AND change = 2 THEN 1
END) [调出] ,
SUM(CASE WHEN [catalog] = 1
AND change = 1 THEN 1
WHEN [catalog] = 2
AND change = 1 THEN 1
WHEN [catalog] = 3
AND change = 1 THEN 1
WHEN [catalog] = 4
AND change = 1 THEN 1
END) [调入]
FROM people
WHERE DATEPART(mm, create_time) BETWEEN 1 AND 3
GROUP BY CASE WHEN [catalog] = 1 THEN '工人总数'
WHEN [catalog] = 2 THEN '知青总数'
WHEN [catalog] = 3 THEN '干部总数'
WHEN [catalog] = 4 THEN '先进分子总数'
END
UNION ALL
SELECT '二' AS '季度' ,
CASE WHEN [catalog] = 1 THEN '工人总数'
WHEN [catalog] = 2 THEN '知青总数'
WHEN [catalog] = 3 THEN '干部总数'
WHEN [catalog] = 4 THEN '先进分子总数'
END '项目' ,
SUM(CASE WHEN [catalog] = 1
AND change = 0 THEN 1
WHEN [catalog] = 2
AND change = 0 THEN 1
WHEN [catalog] = 3
AND change = 0 THEN 1
WHEN [catalog] = 4
AND change = 0 THEN 1
END) [原有数] ,
SUM(CASE WHEN [catalog] = 1
AND change = 2 THEN 1
WHEN [catalog] = 2
AND change = 2 THEN 1
WHEN [catalog] = 3
AND change = 2 THEN 1
WHEN [catalog] = 4
AND change = 2 THEN 1
END) [调出] ,
SUM(CASE WHEN [catalog] = 1
AND change = 1 THEN 1
WHEN [catalog] = 2
AND change = 1 THEN 1
WHEN [catalog] = 3
AND change = 1 THEN 1
WHEN [catalog] = 4
AND change = 1 THEN 1
END) [调入]
FROM people
WHERE DATEPART(mm, create_time) BETWEEN 4 AND 6
GROUP BY CASE WHEN [catalog] = 1 THEN '工人总数'
WHEN [catalog] = 2 THEN '知青总数'
WHEN [catalog] = 3 THEN '干部总数'
WHEN [catalog] = 4 THEN '先进分子总数'
END
UNION ALL
SELECT '三' AS '季度' ,
CASE WHEN [catalog] = 1 THEN '工人总数'
WHEN [catalog] = 2 THEN '知青总数'
WHEN [catalog] = 3 THEN '干部总数'
WHEN [catalog] = 4 THEN '先进分子总数'
END '项目' ,
SUM(CASE WHEN [catalog] = 1
AND change = 0 THEN 1
WHEN [catalog] = 2
AND change = 0 THEN 1
WHEN [catalog] = 3
AND change = 0 THEN 1
WHEN [catalog] = 4
AND change = 0 THEN 1
END) [原有数] ,
SUM(CASE WHEN [catalog] = 1
AND change = 2 THEN 1
WHEN [catalog] = 2
AND change = 2 THEN 1
WHEN [catalog] = 3
AND change = 2 THEN 1
WHEN [catalog] = 4
AND change = 2 THEN 1
END) [调出] ,
SUM(CASE WHEN [catalog] = 1
AND change = 1 THEN 1
WHEN [catalog] = 2
AND change = 1 THEN 1
WHEN [catalog] = 3
AND change = 1 THEN 1
WHEN [catalog] = 4
AND change = 1 THEN 1
END) [调入]
FROM people
WHERE DATEPART(mm, create_time) BETWEEN 7 AND 9
GROUP BY CASE WHEN [catalog] = 1 THEN '工人总数'
WHEN [catalog] = 2 THEN '知青总数'
WHEN [catalog] = 3 THEN '干部总数'
WHEN [catalog] = 4 THEN '先进分子总数'
END
UNION ALL
SELECT '四' AS '季度' ,
CASE WHEN [catalog] = 1 THEN '工人总数'
WHEN [catalog] = 2 THEN '知青总数'
WHEN [catalog] = 3 THEN '干部总数'
WHEN [catalog] = 4 THEN '先进分子总数'
END '项目' ,
SUM(CASE WHEN [catalog] = 1
AND change = 0 THEN 1
WHEN [catalog] = 2
AND change = 0 THEN 1
WHEN [catalog] = 3
AND change = 0 THEN 1
WHEN [catalog] = 4
AND change = 0 THEN 1
END) [原有数] ,
SUM(CASE WHEN [catalog] = 1
AND change = 2 THEN 1
WHEN [catalog] = 2
AND change = 2 THEN 1
WHEN [catalog] = 3
AND change = 2 THEN 1
WHEN [catalog] = 4
AND change = 2 THEN 1
END) [调出] ,
SUM(CASE WHEN [catalog] = 1
AND change = 1 THEN 1
WHEN [catalog] = 2
AND change = 1 THEN 1
WHEN [catalog] = 3
AND change = 1 THEN 1
WHEN [catalog] = 4
AND change = 1 THEN 1
END) [调入]
FROM people
WHERE DATEPART(mm, create_time) BETWEEN 10 AND 12
GROUP BY CASE WHEN [catalog] = 1 THEN '工人总数'
WHEN [catalog] = 2 THEN '知青总数'
WHEN [catalog] = 3 THEN '干部总数'
WHEN [catalog] = 4 THEN '先进分子总数'
END
) a
[解决办法]
改漏了个地方
SELECT DATEPART(qq, create_time) AS jd ,
catalog ,
Change ,
COUNT(1) AS rs
INTO T1
FROM people
GROUP BY DATEPART(qq, create_time) ,
catalog ,
Change
SELECT * ,
CASE WHEN ( ISNULL([原有数], 0) + ISNULL([调出], 0)
- ISNULL([调入], 0) ) <= 0 THEN NULL
ELSE ISNULL([原有数], 0) + ISNULL([调出], 0) - ISNULL([调入],
0)
END AS '现有数'
FROM ( SELECT jd ,
catalog ,
SUM(CASE WHEN Change = 0 THEN rs
ELSE 0
END) AS [原有数] ,
SUM(CASE WHEN Change = 1 THEN rs
ELSE 0
END) AS [调入] ,
SUM(CASE WHEN Change = 2 THEN rs
ELSE 0
END) AS [调出]
FROM T1
GROUP BY jd ,
catalog
) a