---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-09-22 15:28:29 -- 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]([id] int,[student] varchar(1),[sub] varchar(3)) insert [tb] select 1,'A','1笔' union all select 1,'A','2笔' union all select 1,'C','1笔' union all select 1,'D','3笔' union all select 1,'P','1笔' union all select 1,'E','4笔' union all select 1,'A','3笔' --------------开始查询-------------------------- select sub,count(1) as cnt from tb group by sub ----------------结果---------------------------- /* sub cnt ---- ----------- 1笔 3 2笔 1 3笔 2 4笔 1(4 行受影响) */
虽然例子很简单,但严谨考虑的话,应该这样些,不然重复的情况会造成统计出错。select sub,count(distinct student) as num from tb group by sub order by sub
select sub count(id) from tb group by sub order by sub
select sub,count(id) from tb group by sub order by sub
加一个null的限制select sub,count(distinct isnull(student,'')) as num from tb where sub is not null group by sub order by sub
想请问楼主,表里会不会出现student、sub都相同的记录,如会,是只记1,还是记n呢?? 如不会,如前面的答案,如会并且有多少条就记多少个,也如前面,如只记1:select sub,count(*) as num from ( select distinct * from tb ) a group by a.sub order by a.sub
use master gocreate table tb ( id int not null, student char(10) not null, sub char(10) ) goinsert into tb values(1,'A','1笔') insert into tb values(1,'A','2笔') insert into tb values(1,'C','1笔') insert into tb values(1,'D','3笔') insert into tb values(1,'P','1笔') insert into tb values(1,'E','4笔') insert into tb values(1,'A','3笔') select sub,count(*) as 使用人数 from tb group by sub
from tb group by sub
from tb
group by sub
from tb group by sub
from tb group by sub
select
sub,count(1) as cnt
from
tb
group by
sub
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-22 15:28:29
-- 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]([id] int,[student] varchar(1),[sub] varchar(3))
insert [tb]
select 1,'A','1笔' union all
select 1,'A','2笔' union all
select 1,'C','1笔' union all
select 1,'D','3笔' union all
select 1,'P','1笔' union all
select 1,'E','4笔' union all
select 1,'A','3笔'
--------------开始查询--------------------------
select
sub,count(1) as cnt
from
tb
group by
sub
----------------结果----------------------------
/* sub cnt
---- -----------
1笔 3
2笔 1
3笔 2
4笔 1(4 行受影响)
*/
from tb
group by sub
order by sub
select sub,count(id)
from tb
group by sub
order by sub
where sub is not null
group by sub order by sub
如不会,如前面的答案,如会并且有多少条就记多少个,也如前面,如只记1:select sub,count(*) as num
from (
select distinct *
from tb ) a
group by a.sub
order by a.sub
gocreate table tb
(
id int not null,
student char(10) not null,
sub char(10)
)
goinsert into tb values(1,'A','1笔')
insert into tb values(1,'A','2笔')
insert into tb values(1,'C','1笔')
insert into tb values(1,'D','3笔')
insert into tb values(1,'P','1笔')
insert into tb values(1,'E','4笔')
insert into tb values(1,'A','3笔')
select sub,count(*) as 使用人数 from tb group by sub