我有如下一个表ID XH CJ DW KC
1 001 25 NJ WL
2 001 44 BJ HX
3 001 62 BJ WL
4 001 49 SH ZW
5 002 32 NJ ZW
6 002 33 SH SX
7 002 65 NJ WL
8 003 18 SH HX
9 003 22 NJ WL
10 003 75 BJ ZW我现在要写一个SQL语句,然后一执行就能直接显示如下的结果
ID XH CJ DW KC
1 001 25 NJ WL
6 002 33 SH SX
9 003 22 NJ WL
也就是以XH为GROUP BY的条件,然后随机列出记录
比如第二次执行的结果也可以是
ID XH CJ DW KC
3 001 62 BJ WL
6 002 33 SH SX
10 003 75 BJ ZW
1 001 25 NJ WL
2 001 44 BJ HX
3 001 62 BJ WL
4 001 49 SH ZW
5 002 32 NJ ZW
6 002 33 SH SX
7 002 65 NJ WL
8 003 18 SH HX
9 003 22 NJ WL
10 003 75 BJ ZW我现在要写一个SQL语句,然后一执行就能直接显示如下的结果
ID XH CJ DW KC
1 001 25 NJ WL
6 002 33 SH SX
9 003 22 NJ WL
也就是以XH为GROUP BY的条件,然后随机列出记录
比如第二次执行的结果也可以是
ID XH CJ DW KC
3 001 62 BJ WL
6 002 33 SH SX
10 003 75 BJ ZW
(
tId int,
XH varchar(3),
CJ int
)goinsert tb
select 1,'001',25 union all
select 2,'001',50 union all
select 3,'001',60 union all
select 4,'001',100 union all
select 5,'002',25 union all
select 6,'002',75 union all
select 7,'002',45 union all
select 8,'003',95 union all
select 9,'003',100
select xh into tbGrp from tb group by XHselect b.* from tb b inner join (
select a.XH,(select top 1 tId from tb where a.XH=XH order by newId()) as tId from tbGrp a)c on b.tId=c.tId楼主认为对的话,请给分啊.
insert test(XH,CJ,DW,KC)
select '001', 25,'NJ','WL' union all
select '001', 44,'BJ','HX' union all
select '001', 62,'BJ','WL' union all
select '001', 49,'SH','ZW' union all
select '002', 32,'NJ','ZW' union all
select '002', 33,'SH','SX' union all
select '002', 65,'NJ','WL' union all
select '003', 18,'SH','HX' union all
select '003', 22,'NJ','WL' union all
select '003', 75,'BJ','ZW'
--select * from testselect * from test
where id in
(
select
ID=(select top 1 id from test where XH=t.XH order by newid())
from test t
group by XH
)drop table test