access如何去重_sql语句：一对多表查询时如何去重只显示主表中的一条数据

一、场景一：

主 表 t_main，二个子表，子表1：t_relatedPeopleOrganization，子表2：t_coverageSpatial
主表通过 autoid分别与子表的 autoparentid 建立一对多的关联，

全文检索所有主表和子表的字段，查询结果可能有多条数据，但需求：只显示主表的一条数据。

SELECT m.autoid,mainID, m.artificialID,titleProper,otherTitle,ethnicGroup,timesProperty,m.artSchool,keywords,m.hits,firstName,secondName,thirdName,forthName,fifthName

,socialAttribute,formatTypeName,publisher,publicationDate

,z_spatial.spatialType,z_spatial.placeName,z_spatial.oneName

,z_people.name,z_people.role,z_people.nationality

FROM t_main m

LEFT JOIN (

SELECT number = ROW_NUMBER() OVER(PARTITION BY autoparentid ORDER BY relatedPeopleOrganizationID ),* FROM t_relatedPeopleOrganization

) z_people ON m.autoid =z_people.autoparentid AND z_people.number =1

LEFT JOIN (

SELECT number = ROW_NUMBER() OVER(PARTITION BY autoparentid ORDER BY coverageSpatialID ),* FROM t_coverageSpatial

) z_spatial ON m.autoid =z_spatial.autoparentid AND z_spatial.number =1

可以将以上代码建立视图名为：View_qw_main_spatial_people，然后全文检索各字段得到你想要的结果。

select * from View_qw_main_spatial_people where (z_people.name LIKE '%永海%' or ...)

二、场景二：

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

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

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

三、场景三：

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

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