表结构如下
classid tid
1 1
1 2
2 1
2 3
2 4
3 3
3 4
我想按classid每个类里随机取一条数据,该怎么写查询
想得到的结果如下classid tid
1 n
2 n
3 n
classid tid
1 1
1 2
2 1
2 3
2 4
3 3
3 4
我想按classid每个类里随机取一条数据,该怎么写查询
想得到的结果如下classid tid
1 n
2 n
3 n
INSERT TEST SELECT 1,1
INSERT TEST SELECT 1,2
INSERT TEST SELECT 2,1
INSERT TEST SELECT 2,3
INSERT TEST SELECT 2,4
INSERT TEST SELECT 3,3
INSERT TEST SELECT 3,4
--SELECT * FROM TESTSELECT * FROM TEST A
WHERE TID=
(
SELECT TOP 1 TID FROM TEST WHERE CLASSID=A.CLASSID ORDER BY NEWID()
)DROP TABLE TEST
INSERT @TEST
SELECT 1,1
union all SELECT 1,2
union all SELECT 2,1
union all SELECT 2,3
union all SELECT 2,4
union all SELECT 3,3
union all SELECT 3,4select classid,
tid=(select top 1 tid from @test where classid=t.classid order by newid())
from @test t group by classid
(
classid smallint,
tid smallint
)
insert into Tselect 1, 1 union all
select 1, 2 union all
select 2, 1 union all
select 2, 3 union all
select 2, 4 union all
select 3, 3 union all
select 3, 4
select * from T a where not exists (select 1 from T where a.classid=T.classid and a.tid<T.tid)
classid tid
------- ------
1 2
2 4
3 4(3 row(s) affected)