在线等SQL语句

[cynthia]

表T1
字段A 字段B
a 1
b 1
c 1
d 1
e 2
f 2
g 2
g 2
ss 3
wqw 3
we1 3
qw 3
.
.
.
要根据字段B的唯一值，每个值取n条纪录，放到一张表中，想要得到的结果集如下,假设n = 3
a 1
b 1
c 1
e 2
f 2
g 2
ss 3
wqw 3
we1 3
55，不知道我说清楚了没有。

[cynthia]

顶一下

[cynthia]

其中的n条纪录可以是随机的，只要取出来就行。字段A的值无任何规律。

[子陌红尘]

select
a.*
from
T1 a
where
a.字段A = (select top n 字段A from T1 where 字段B=a.字段B order by newid)

[子陌红尘]

select
a.*
from
T1 a
where
a.字段A = (select top n --替换成具体的数值
字段A
from
T1
where
字段B=a.字段B
order by
newid())

[子陌红尘]

这个查询语句还存在一点问题，当字段A值重复时可能会取得多于N条记录：

--创建测试数据
create table T1(字段A varchar(20),字段B int)
insert into T1 select rtrim('a '),1
insert into T1 select rtrim('b '),1
insert into T1 select rtrim('c '),1
insert into T1 select rtrim('d '),1
insert into T1 select rtrim('e '),2
insert into T1 select rtrim('f '),2
insert into T1 select rtrim('g '),2
insert into T1 select rtrim('g '),2
insert into T1 select rtrim('ss '),3
insert into T1 select rtrim('wqw'),3
insert into T1 select rtrim('we1'),3
insert into T1 select rtrim('qw '),3


--执行查询
select
a.*
from
T1 a
where
a.字段A = (select top 2
字段A
from
T1
where
字段B=a.字段B)

--输出结果
字段A 字段B
----- -----
a 1
b 1
e 2
f 2
ss 3
wqw 3

[子陌红尘]

比较稳妥的办法是借助临时表：

----------------------------------------------------------------------------
--创建测试数据
create table T1(字段A varchar(20),字段B int)
insert into T1 select rtrim('a '),1
insert into T1 select rtrim('b '),1
insert into T1 select rtrim('c '),1
insert into T1 select rtrim('d '),1
insert into T1 select rtrim('e '),2
insert into T1 select rtrim('f '),2
insert into T1 select rtrim('g '),2
insert into T1 select rtrim('g '),2
insert into T1 select rtrim('ss '),3
insert into T1 select rtrim('wqw'),3
insert into T1 select rtrim('we1'),3
insert into T1 select rtrim('qw '),3


--执行查询
select identity(int,1,1) as id,* into #T from T1

select
a.字段A,
a.字段B
from
#T a
where
a.id in (select
b.id
from
#T b
where
b.字段B=a.字段B
and
(select
count(*)
from
#t
where
字段B=b.字段B
and
id<=b.id)<=2)


--输出结果
字段A 字段B
----- -----
a 1
b 1
e 2
f 2
ss 3
wqw 3

[cynthia]

select
a.*
from
T1 a
where
a.字段A in (select top n 字段A from T1 where 字段B=a.字段B order by newid)

[cynthia]

thanks a lot,结贴了