sql语句合并兼求和

[zycsoftware123456789]

有一表内容如下：

a b

IV-01-002-01 HKD
IV-01-002-01 RMB
IV-01-002-02 HKD
IV-01-002-02 HKD
IV-01-002-03 HKD

请问如何用一条select语句得到下面结果:

a 记录数
IV-01-002-01 2
IV-01-002-02 1
IV-01-002-03 1

[zhangle861010]

declare @t table(a varchar(12),b varchar(3))
insert @t
select 'IV-01-002-01','HKD' union all
select 'IV-01-002-01','RMB' union all
select 'IV-01-002-02','HKD' union all
select 'IV-01-002-02','HKD' union all
select 'IV-01-002-03','HKD'


select a,count(distinct b) from @t group by a

[xuejiecn]

SELECT A,COUNT(DISTINCT B)AS 记录数 FROM TB GROUP BY A

[ws_hgo]

declare @TT table([a] varchar(12),[b] varchar(3))
insert @TT
select 'IV-01-002-01','HKD' union all
select 'IV-01-002-01','RMB' union all
select 'IV-01-002-02','HKD' union all
select 'IV-01-002-02','HKD' union all
select 'IV-01-002-03','HKD'

select distinct [a],count([a]) '记录数' from @TT group by [a]

a            记录数
------------ -----------
IV-01-002-01 2
IV-01-002-02 2
IV-01-002-03 1

[happybuttom]

-- Author :YD(学习SQL2000，ASP.NET,DELPHI)
-- Date :2009-09-11 10:15
--
----------------------------------------------------------------
--> 测试数据：[t]
if object_id('[t]') is not null drop table [t]
go
create table [t]([a] varchar(20),[b] varchar(10))
insert [t]
select 'IV-01-002-01','HKD' union all
select 'IV-01-002-01','RMB' union all
select 'IV-01-002-02','HKD' union all
select 'IV-01-002-02','HKD' union all
select 'IV-01-002-03','HKD'
--查询语句如下：
select a,count(distinct b) from t group by a

[水族杰纶]

-->Title:生成測試數據
-->Author:wufeng4552
-->Date :2009-09-11 10:18:08
 
declare @b table([a] nvarchar(12),[b] nvarchar(3))
Insert @b
select N'IV-01-002-01',N'HKD' union all
select N'IV-01-002-01',N'RMB' union all
select N'IV-01-002-02',N'HKD' union all
select N'IV-01-002-02',N'HKD' union all
select N'IV-01-002-03',N'HKD'
SELECT [A],
       COUNT(*)紀錄數
FROM(SELECT DISTINCT * FROM @B )T
GROUP BY [A]
/*
A            紀錄數
------------ -----------
IV-01-002-01 2
IV-01-002-02 1
IV-01-002-03 1
*/

[luoyoumou]

支持1楼，明白作者意图：排除重复记录！

[luoyoumou]

支持13楼，做事一丝不苟，实战第一！

[13abyKnight]

===================要分

[soft_wsx]

这个贴子是那里来的，没有看到，气死我了

[jayqean]

select a,count(distinct b) as 记录数 from tb group by a
分这么多呀

[king269]

路过，带分离开~~~

[baiyicanggou]

if object_id('[TB]') is not null drop table [TB]
create table [TB]([a] varchar(12),[b] varchar(3))
insert [TB]
select 'IV-01-002-01','HKD' union all
select 'IV-01-002-01','RMB' union all
select 'IV-01-002-02','HKD' union all
select 'IV-01-002-02','HKD' union all
select 'IV-01-002-02','HKD' union all
select 'IV-01-002-03','HKD'

select a,记录数=count( b) from TB group by a
结果
IV-01-002-01	2
IV-01-002-02	3
IV-01-002-03	1

[jinjazz]

SELECT A,COUNT(DISTINCT B)AS 记录数 FROM TB GROUP BY A

[feixianxxx]

[guguda2008]

我是进来蹭分的

[cxmcxm]

declare @tmp table (a varchar(20),  b varchar(20))
insert into @tmp (a,b)
select 'IV-01-002-01',      'HKD'
union all select 'IV-01-002-01' ,     'RMB'
union all select 'IV-01-002-02'  ,    'HKD'
union all select 'IV-01-002-02'  ,    'HKD'
union all select 'IV-01-002-03'  ,    'HKD'




select a,count(distinct b) from @tmp group by a

[alan0128]

[Quote=引用 21 楼 zhaoweiting0609 的回复:]
现在挣点分,真是不易啊,O(∩_∩)O~
[/Quote]
是非常不容易，尤其在SQL版块，郁闷

[dawugui]

这个蹭个分.

[无锡阔微信息科技]

[Quote=引用 5 楼 jiangshun 的回复:]
SQL code--> 测试数据：[TB]
--> 我的淘宝：http://shop36766744.taobao.com/ifobject_id('[TB]')isnotnulldroptable[TB]createtable[TB]([a]varchar(12),[b]varchar(3))insert[TB]select'IV-01-002-01','HKD'unionallselect'IV-01-002-01','RMB'unionallselect'IV-01-002-02','HKD'unionallselect'IV-01-002-02','HKD'unionallselect'IV-01-002-03','HKD'select a,记录数=count(distinct b)from TBgroupby a/*
a 记录数
------------ -----------
IV-01-002-01 2
IV-01-002-02 1
IV-01-002-03 1

(3 行受影响)*/droptable[TB]

[/Quote]

正解

[xuejiecn]

SELECT A,COUNT(DISTINCT B)AS 记录数 FROM TB GROUP BY A