参考:create table A(Score int ,Shift varchar(10)) insert A select 20,'B' union all select 30,'B' union all select 10,'B' union all select 78,'A' union all select 19,'A' union all select 5 ,'A' union all select 40,'C' union all select 25,'C' union all select 6 ,'C' --select * from Acreate table B(Score int,Shift varchar(10)) insert B select 20,'B' --select * from Bselect * from A i where score in ( select top 2 score from A where Shift=i.Shift and score not in(select score from B where Shift=A.Shift) )drop table A,B
select * from tablename t1 where column2 in (select top 2 column2 from tablename where column1 = t1.column1)
我可能还没明白你的意思: 是不是这个: select top 2 * from tablename where 列名1="A" union all select top 2 * from tablename where 列名1="B"
CREATE TABLE #a ( [id] [char] (10), [value] [int] )insert into #a(id,value) values('A',1) insert into #a(id,value) values('A',2) insert into #a(id,value) values('A',3) insert into #a(id,value) values('B',4) insert into #a(id,value) values('B',5) insert into #a(id,value) values('B',6) select * from #a t where value in ( select top 2 value from #a where id=t.id order by value ) drop table #aid value ---------- ----------- A 1 A 2 B 4 B 5(所影响的行数为 4 行)
insert A
select 20,'B' union all
select 30,'B' union all
select 10,'B' union all
select 78,'A' union all
select 19,'A' union all
select 5 ,'A' union all
select 40,'C' union all
select 25,'C' union all
select 6 ,'C'
--select * from Acreate table B(Score int,Shift varchar(10))
insert B select 20,'B'
--select * from Bselect * from A i
where score in
(
select top 2 score from A where Shift=i.Shift and score not in(select score from B where Shift=A.Shift)
)drop table A,B
select * from tablename t1
where column2 in (select top 2 column2 from tablename where column1 = t1.column1)
是不是这个:
select top 2 * from tablename where 列名1="A"
union all
select top 2 * from tablename where 列名1="B"
[id] [char] (10),
[value] [int]
)insert into #a(id,value) values('A',1)
insert into #a(id,value) values('A',2)
insert into #a(id,value) values('A',3)
insert into #a(id,value) values('B',4)
insert into #a(id,value) values('B',5)
insert into #a(id,value) values('B',6)
select * from #a t
where value in
(
select top 2 value from #a where id=t.id order by value
)
drop table #aid value
---------- -----------
A 1
A 2
B 4
B 5(所影响的行数为 4 行)