就是在一个数据库的表中,有两个字段,其中一个学生ID,另一个是学生当前状态。学生当前状态分为四种,1234;我要一次查询出每个学生ID对应的每个状态的个数!
studentID state
1 1
1 1
1 2
2 3
3 4
然后我要查出ID为1的每个状态的次数
例如:
ID 1 2 3 4
1 2 1 0 0
2 0 0 1 0
3 0 0 0 1
studentID state
1 1
1 1
1 2
2 3
3 4
然后我要查出ID为1的每个状态的次数
例如:
ID 1 2 3 4
1 2 1 0 0
2 0 0 1 0
3 0 0 0 1
studentid
,sum(case when state=1 then 1 else 0 end ) as [1]
,sum(case when state=2 then 1 else 0 end ) as [2]
,sum(case when state=3 then 1 else 0 end ) as [3]
,sum(case when state=4 then 1 else 0 end ) as [4]
from t
group by studentid
studentid
,sum(case when state=1 then 1 else 0 end ) as "1"
,sum(case when state=2 then 1 else 0 end ) as "2"
,sum(case when state=3 then 1 else 0 end ) as "3"
,sum(case when state=4 then 1 else 0 end ) as "4"
from t
group by studentid
--if object_id('t1') is not null
-- drop table t1
--Go
Create table t1([studentID] smallint,[state] smallint)
Insert into t1
Select 1,1
Union all Select 1,1
Union all Select 1,2
Union all Select 2,3
Union all Select 3,4
SELECT
studentid,[1],[2],[3],[4]
FROM t1
PIVOT (
COUNT(state) FOR state IN([1],[2],[3],[4])
) AS P
select * from student pivot(count(status) for status in ([1],[2],[3],[4]))t