create table #b(id int identity(1,1), title char(3), isbn varchar(10), type int) insert #b select 'adc', '7531327481', 1 insert #b select 'efg', '5536692702', 1 insert #b select 'hij', '7531327481', 2 insert #b select 'klm', '5536692702', 2 insert #b select 'nop', '1121451421', 1 insert #b select 'qrs', ' ', 1 select id, title, isbn, type from (select *, (select count(1) from #b t2 where t1.isbn = t2.isbn and t1.id>= t2.id) count_ from #b t1) a where a.count_ = 1select isbn, count(1) [type_count] from #b group by isbn/* id title isbn type ----------- ----- ---------- ----------- 1 adc 7531327481 1 2 efg 5536692702 1 5 nop 1121451421 1 6 qrs 1(4 row(s) affected)isbn type_count ---------- ----------- 1 1121451421 1 5536692702 2 7531327481 2 */ drop table #b
-- (1)将相同isbn的多条数据只取第一条select * from b as t1 where not exists(select 1 from b where isbn = t1.isbn and id > t1.id )-- (2)根据相同isbn统计出type不同的数量, select isbn,count(1) as typecount from ( select isbn,type from b group by isbn,type )a group by isbn
sekect * from b a where not exists(select 1 from b b where a.isbn=b.isbn and a.id>b.id)select isbn,type,count(1) as num from b group by isbn,type
--(1) select * from b A where not exists(select 1 from b B where B.id < A.id AND B.isbn = B.isbn) --(2) select isbn ,type, icount = count(1) from b group by isbn ,type
insert #b select 'adc', '7531327481', 1
insert #b select 'efg', '5536692702', 1
insert #b select 'hij', '7531327481', 2
insert #b select 'klm', '5536692702', 2
insert #b select 'nop', '1121451421', 1
insert #b select 'qrs', ' ', 1
select id, title, isbn, type
from (select *, (select count(1) from #b t2 where t1.isbn = t2.isbn and t1.id>= t2.id) count_ from #b t1) a
where a.count_ = 1select isbn, count(1) [type_count] from #b
group by isbn/*
id title isbn type
----------- ----- ---------- -----------
1 adc 7531327481 1
2 efg 5536692702 1
5 nop 1121451421 1
6 qrs 1(4 row(s) affected)isbn type_count
---------- -----------
1
1121451421 1
5536692702 2
7531327481 2
*/
drop table #b
-- (1)将相同isbn的多条数据只取第一条select * from b as t1 where not exists(select 1 from b where isbn = t1.isbn and id > t1.id )-- (2)根据相同isbn统计出type不同的数量,
select isbn,count(1) as typecount from
(
select isbn,type from b
group by isbn,type
)a group by isbn
sekect * from b a
where not exists(select 1 from b b where a.isbn=b.isbn and a.id>b.id)select isbn,type,count(1) as num from b
group by isbn,type
select * from b A where not exists(select 1 from b B where B.id < A.id AND B.isbn = B.isbn)
--(2)
select isbn ,type, icount = count(1) from b group by isbn ,type