这种查询该怎么写
表结构如下
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
[解决办法]
CREATE TABLE TEST(classid INT,tid INT)
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 TEST
SELECT * FROM TEST A
WHERE TID=
(
SELECT TOP 1 TID FROM TEST WHERE CLASSID=A.CLASSID ORDER BY NEWID()
)
DROP TABLE TEST
[解决办法]
declare @TEST TABLE (classid INT,tid INT)
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,4
select classid,
tid=(select top 1 tid from @test where classid=t.classid order by newid())
from @test t group by classid
