表 test里面有数据如下:
item shipper
--------------------------
1200 000009
1200 000009
1200 000089
1200 234567
1100 123090
1100 123498
1000 889900
... ....需要得到的结果是:
item count
1200 3
1100 2
1000 1
item shipper
--------------------------
1200 000009
1200 000009
1200 000089
1200 234567
1100 123090
1100 123498
1000 889900
... ....需要得到的结果是:
item count
1200 3
1100 2
1000 1
item,
COUNT(DISTINCT shipper) AS [count]
FROM test
GROUP BY item
create table test (item int, shipper varchar(10))
insert into test values(1200 , '000009')
insert into test values(1200 , '000009')
insert into test values(1200 , '000089')
insert into test values(1200 , '234567')
insert into test values(1100 , '123090')
insert into test values(1100 , '123498')
insert into test values(1000 , '889900')
goselect item , count(*) [count] from test group by item order by item desc
/*
item count
----------- -----------
1200 4
1100 2
1000 1(所影响的行数为 3 行)
*/select item , count(distinct shipper ) [count] from test group by item order by item desc
/*
item count
----------- -----------
1200 3
1100 2
1000 1(所影响的行数为 3 行)
*/drop table test
select item, count(distinct(shipper)) [counta] from test group by item order by item descitem counta
----------- -----------
1200 3
1100 2
1000 1(所影响的行数为 3 行)