求一下sql文

[dragon2078]

table1：

A B
001 a1
001 a2
001 a3
001 a1
001 a1
002 a5
002 a5
002 a6
003 a7
004 a8
004 a8

请问SQL文该怎写 可以得到一下的结果：
A B
001 3
002 2
003 1
004 1

很急 拜托了啊！！

[ws_hgo]

select A,sum(distinct B) from tb group by A

• 打赏
• 举报

回复 赞

[dawugui]

select a , count(distinct b) b from table1 group by a

• 打赏
• 举报

回复 赞

[ws_hgo]

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 行受影响)

• 打赏
• 举报

回复 赞

[--小F--]

----------------------------------------------------------------
-- 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 行）
*/

• 打赏
• 举报

回复 赞

[dragon2078]

非常感谢各位！

• 打赏
• 举报

回复 赞