22,206
社区成员
发帖
与我相关
我的任务
分享
select 学号,次数=sum(case 状态 when 1 else 0 end)
from tb
group by 学号
select 学号,COUNT(1) 次数 from tb where 状态=1 group by 学号
SELECT 学号,sum(case when 状态=1 then 1 else 0 end) from tb group by 学号
/*
1 5
2 2
*/
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 学号
select
学号, 次数=sum(case 状态 when 1 else 0 end)
from tb group by 学号 order by 学号
declare @TB table([序号] 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([状态]) [状态] from @TB group by [学号]
学号 状态
----------- -----------
1 5
2 2
(2 行受影响)
select 学号,sum(次数) 次数 from tb group by 学号
select 学号,sum(次数) 次数
from
tb
select 学号,次数=sum(case 状态 when 1 else 0 end)
from tb
group by 学号
---不好意思 1,2楼都有点问题
----------------------------------------------------------------
-- 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
学号
select
学号,
次数=sum(case 状态 when 1 else 0 end)
from
tb