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
5 个解决方案
#1
select
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
#2
--> 测试数据:[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([studentID] INT,[state] INT)
INSERT [tb]
SELECT 1,1 UNION ALL
SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 2,3 UNION ALL
SELECT 3,4
--------------开始查询--------------------------
select * from
(
SELECT *,row= row_number() OVER(partition by [state] ORDER BY [state])FROM [tb]
) a
PIVOT (count(row) FOR [state] IN ([1],[2],[3],[4])) b
----------------结果----------------------------
/*
studentID 1 2 3 4
----------- ----------- ----------- ----------- -----------
1 2 1 0 0
2 0 0 1 0
3 0 0 0 1
(3 行受影响)
*/
#3
select * from student pivot(count(state) for state in ([1],[2],[3],[4]))t
#4
成功了,谢谢!可是还有一个问题,就是我如何把这个结果绑定在asp.net上的Gridview数据绑定控件上?把这个存储过程的表绑到Gridview
#5
谢谢大家,结贴了
#1
select
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
#2
--> 测试数据:[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([studentID] INT,[state] INT)
INSERT [tb]
SELECT 1,1 UNION ALL
SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 2,3 UNION ALL
SELECT 3,4
--------------开始查询--------------------------
select * from
(
SELECT *,row= row_number() OVER(partition by [state] ORDER BY [state])FROM [tb]
) a
PIVOT (count(row) FOR [state] IN ([1],[2],[3],[4])) b
----------------结果----------------------------
/*
studentID 1 2 3 4
----------- ----------- ----------- ----------- -----------
1 2 1 0 0
2 0 0 1 0
3 0 0 0 1
(3 行受影响)
*/
#3
select * from student pivot(count(state) for state in ([1],[2],[3],[4]))t
#4
成功了,谢谢!可是还有一个问题,就是我如何把这个结果绑定在asp.net上的Gridview数据绑定控件上?把这个存储过程的表绑到Gridview
#5
谢谢大家,结贴了