--序号-------学号----------状态--------
1 1 1
2 1 1
3 1 0
4 1 0
5 1 0
6 2 1
7 2 0
8 2 0
9 1 1
10 2 0
11 1 1
12 1 1
13 1 0
14 2 1
想要获的效果
-----学号----------次数-----------
1 5
2 2
说明一下,次数是1出现的次数
求解谢谢~
1 1 1
2 1 1
3 1 0
4 1 0
5 1 0
6 2 1
7 2 0
8 2 0
9 1 1
10 2 0
11 1 1
12 1 1
13 1 0
14 2 1
想要获的效果
-----学号----------次数-----------
1 5
2 2
说明一下,次数是1出现的次数
求解谢谢~
学号,
次数=sum(case 状态 when 1 else 0 end)
from
tb
学号,
次数=sum(case 状态 when 1 else 0 end)
from
tb
group by
学号
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-14 15:11:12
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([序号] int,[学号] int,[状态] int)
insert [tb]
select 1,1,1 union all
select 2,1,1 union all
select 3,1,0 union all
select 4,1,0 union all
select 5,1,0 union all
select 6,2,1 union all
select 7,2,0 union all
select 8,2,0 union all
select 9,1,1 union all
select 10,2,0 union all
select 11,1,1 union all
select 12,1,1 union all
select 13,1,0 union all
select 14,2,1
--------------开始查询--------------------------
select
学号,
次数=sum(case 状态 when 1 then 1 else 0 end)
from
tb
group by
学号
----------------结果----------------------------
/* 学号 次数
----------- -----------
1 5
2 2(2 行受影响)
*/
select 学号,次数=sum(case 状态 when 1 else 0 end)
from tb
group by 学号
from
tb
insert @TB
select 1,1,1 union all
select 2,1,1 union all
select 3,1,0 union all
select 4,1,0 union all
select 5,1,0 union all
select 6,2,1 union all
select 7,2,0 union all
select 8,2,0 union all
select 9,1,1 union all
select 10,2,0 union all
select 11,1,1 union all
select 12,1,1 union all
select 13,1,0 union all
select 14,2,1select [学号],sum([状态]) [状态] from @TB group by [学号]学号 状态
----------- -----------
1 5
2 2(2 行受影响)
select
学号, 次数=sum(case 状态 when 1 else 0 end)
from tb group by 学号 order by 学号
from tb
group by 学号
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([序号] int,[学号] int,[状态] int)
insert [tb]
select 1,1,1 union all
select 2,1,1 union all
select 3,1,0 union all
select 4,1,0 union all
select 5,1,0 union all
select 6,2,1 union all
select 7,2,0 union all
select 8,2,0 union all
select 9,1,1 union all
select 10,2,0 union all
select 11,1,1 union all
select 12,1,1 union all
select 13,1,0 union all
select 14,2,1
--------------开始查询--------------------------
select
学号,
次数=(select count([状态]) from tb where 学号=t.学号 and [状态]=1)
from
tb t
group by
学号
/*
学号 次数
----------- -----------
1 5
2 2(2 行受影响)
*
/
SELECT 学号,sum(case when 状态=1 then 1 else 0 end) from tb group by 学号
/*
1 5
2 2
*/
select 学号,次数=sum(case 状态 when 1 else 0 end)
from tb
group by 学号