一对多查询只返回一条数据

1.表与表一对多的关系，连表查询只返回多表中的一条记录
SELECT a.*,b.xcid,b.tplj
FROM Landscape a LEFT JOIN (
SELECT number = ROW_NUMBER() OVER(PARTITION BY jqid ORDER BY xcid ),* FROM Landscape_Image
) b ON a.jqid =b.jqid AND b.number =1 where a.xmlb like '%滨海资源%'

 

2.表与表一对多的关系，连表查询只返回多表中的一条记录且返回他的数量，并应用分页

 

select * from 
(select  sm.userid, sm.ip,e.id,e.userid as ui ,num=(select count(*) from Staff_Education where userid=e.userid ),row_number() over(order by sm.userid)as serialNum from dbo.Staff_Main sm left join (select *,number=row_number() over(partition by userid order by Id) from dbo.Evaluation )e on sm.userid=e.userid and e.number=1) T where T.serialNum>5 and T.serialNum<=10

num  表示表Evaluation的数量，serialNum表示序列用于分页

3.表与表一对多的关系，连表查询只返回多表中的一条记录且返回他的数量

SELECT num=(select count(*) from Staff_Education where userid=e.userid ), sm.userid,sm.department,e.* 
  FROM [Staff_Main] sm left join
  (select orderid=row_number() over(partition by userid order by userid),* 
  from  dbo.Staff_Education ) e
  on sm.userid=e.userid and orderid=1
GO