表 A
a b c
1 sf t1
2 s t1
3 dd t2
4 dd t3
6 dd t6
现在想一次查询出,每个c类的前2条 , 就是2个t1 ,2个t2 ,.这样的记录 ,c的类别不确定
如果类别确定可以用union 但是类别不确定 如果写?
------解决方案--------------------------------------------------------
在存储过程里通过
insert into #ls select distinct(c) from A
对#ls使用游标也可
------解决方案--------------------------------------------------------
- SQL code
declare @A table(a int, b varchar(2), c nchar(2))insert into @Aselect 1 ,'sf','t1' union all select 2 ,'s','t1' union all select 3 ,'dd','t2' union all select 4 ,'dd','t3' union all select 6 ,'dd','t6' union all select 7 ,'dd','t2' union all select 8 ,'cc','t1' union all select 9 ,'a','t2' union all select 10 ,'s','t2' select * from @A m where a in (select top 2 a from @A where c=m.c)order by m.c a b c----------- ---- ----1 sf t12 s t13 dd t27 dd t24 dd t36 dd t6(6 行受影响)
------解决方案--------------------------------------------------------
- C# code
create table tb
(
a int,
b nvarchar(50),
c nvarchar(50),
)
insert tb select 1,'sf','t1'
insert tb select 2,'s','t1'
insert tb select 3,'dd','t2'
insert tb select 4,'dd','t3'
insert tb select 6,'dd','t6'
select * from tb t where a in
(select top 2 a from tb where c=t.c)
drop table tb
(1 個資料列受到影響)
(1 個資料列受到影響)
(1 個資料列受到影響)
(1 個資料列受到影響)
(1 個資料列受到影響)
a b c
----------- -------------------------------------------------- --------------------------------------------------
1 sf t1
2 s t1
3 dd t2
4 dd t3
6 dd t6
(5 個資料列受到影響)