27,582
社区成员




----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-22 11:42:55
-- Verstion:
-- Microsoft SQL Server 2000 - 8.00.2055 (Intel X86)
-- Dec 16 2008 19:46:53
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] varchar(3),[B] varchar(2))
insert [tb]
select '001','a1' union all
select '001','a2' union all
select '001','a3' union all
select '001','a1' union all
select '001','a1' union all
select '002','a5' union all
select '002','a5' union all
select '002','a6' union all
select '003','a7' union all
select '004','a8' union all
select '004','a8'
--------------开始查询--------------------------
select a,count(distinct b) as b from [tb] group by a
----------------结果----------------------------
/* (所影响的行数为 11 行)
a b
---- -----------
001 3
002 2
003 1
004 1
(所影响的行数为 4 行)
*/
create table #table1
(
A varchar(20),
B varchar(20)
)
insert into #table1 select '001','a1'
insert into #table1 select '001','a2'
insert into #table1 select '001','a3'
insert into #table1 select '001','a1'
insert into #table1 select '001','a1'
insert into #table1 select '002','a5'
insert into #table1 select '002','a6'
insert into #table1 select '003','a7'
insert into #table1 select '004','a8'
insert into #table1 select '004','a8'
select A,count(distinct B) B from #table1 group by A
A B
-------------------- -----------
001 3
002 2
003 1
004 1
(4 行受影响)
select A,sum(distinct B) from tb group by A