两个表
t1
ID
1t2
ID Feild Type
1 2 0
1 3 0用内联查询
select t2.ID,t2.Type from t1 inner join t2 on t1.ID=t2.ID
结果得到两条记录,我只要得到一条记录,具体那条无所谓,可有办法实现此功能?
t1
ID
1t2
ID Feild Type
1 2 0
1 3 0用内联查询
select t2.ID,t2.Type from t1 inner join t2 on t1.ID=t2.ID
结果得到两条记录,我只要得到一条记录,具体那条无所谓,可有办法实现此功能?
from t1 as t
cross apply
(select top 1 * from t2 where id=t.id order by newid()) t2
select t2.ID,t2.Type from t1 inner join t2 on t1.ID=t2.ID
where not exists(select 1 from t2 a where id=a.id and Feild >a.Feild )
aa cc
1 aaaaa
1 bbbbb
2 ccccc
2 ddddd
3 eeeee
3 fffff
想得到如下结果:
——————————
aa cc
1 aaaaa或bbbbb
2 ccccc或ddddd
3 eeeee或fffff
***********************
是不是跟这个问题差不多,写出来供大家讨论~~~
select top 1 t2.ID,t2.Type from t1 inner join t2 on t1.ID=t2.ID order by newid()
select top 1 t2.ID,t2.Type from t1 inner join t2 on t1.ID=t2.ID order by newid()
如是用select distinct .....
在select 后加上distinct即可
ID
1 t2
ID Feild Type
1 2 0
1 3 0select * from t2 tt where Exists (select ID from t2 where t2.ID=tt.ID and tt.Feild>t2.Feild)
这样是随机抽取一条
select top 1 from (select t2.ID,t2.Type from t1 inner join t2 on t1.ID=t2.ID)
取第一条