--B表有没有主键(与userid可以构成复合主键就可以了)? 有主键可以这样处理select a.userid,a.username,b.* from A表 a join( select a.* from B表 a join( select userid,主键=min(主键) from B表 group by userid )b on a.userid=b.userid and a.主键=b.主键 )b on a.userid=b.userid
--如果没有的话,用临时表: select id=identity(int,1,1),* into #t from B表select a.userid,a.username,b.* from A表 a join( select a.* from #t a join( select id=min(id) from #t group by userid )b on a.d=b.id )b on a.userid=b.useriddrop table #t
(select top 1 useritem from tabB where userid=A.userid) as useritem
from tabA A
我不晓得是不是这个意思!!
from A join B on a.userid=b.userid
group by a.username,a.userid
useritem userid itemnum
CCC 1 10
BBB 1 20
我要把itemnum 也选出来,那么max min函数都不能用的........
from A表 a join(
select a.* from B表 a join(
select userid,主键=min(主键) from B表 group by userid
)b on a.userid=b.userid and a.主键=b.主键
)b on a.userid=b.userid
select id=identity(int,1,1),* into #t from B表select a.userid,a.username,b.*
from A表 a join(
select a.* from #t a join(
select id=min(id) from #t group by userid
)b on a.d=b.id
)b on a.userid=b.useriddrop table #t
所以,如果表B没有主键的话,建议在表B中增加一个标识字段做主键