适用场景:在项目中,一个项目会有很多相同的属性,它们分别存放在不同的表,而我们在做关联统计报表的时候,如果直接用主表关联3个关系表,会使数据出现大量的重复,单元格合并问题也不好处理,经过个人研究发现一种写法可以实现我们想要的最终结果,如各位有更好更简单的写法可以评论指教,谢谢。
下面是场景表和查询语句,与最终得到的结果,大家可以根据自身的情况替换表名,表字段,增加多个关系表,逻辑套路一样:
示例:
主表:
表名 | 字段名 | 字段说明 | 标识 | 主键 | 类型 | 允许空 | 默认值 |
StProjec | Id | √ | √ | int | 否 | ||
StProjec | StProjectName | 项目名称 | nvarchar(2000) | 否 | ('') |
关系表1:
表名 | 字段名 | 字段说明 | 标识 | 主键 | 类型 | 允许空 | 默认值 |
STPatent | ID | √ | √ | int | 否 | ||
STPatent | StProjecZtbId | 关联ID | int | 否 | ((0)) | ||
STPatent | PatentName | 专利名称 | nvarchar(500) | 否 | ('') |
关系表2:
表名 | 字段名 | 字段说明 | 标识 | 主键 | 类型 | 允许空 | 默认值 |
STThesis | ID | √ | √ | int | 否 | ||
STThesis | StProjecZtbId | 关联ID | int | 否 | ((0)) | ||
STThesis | ThesisName | 论文名称 | nvarchar(500) | 否 | ('') |
关系表3:
表名 | 字段名 | 字段说明 | 标识 | 主键 | 类型 | 允许空 | 默认值 |
STOther | ID | √ | √ | int | 否 | ||
STOther | StProjecZtbId | 关联ID | int | 否 | ((0)) | ||
STOther | Name | 其他名称 | nvarchar(500) | 否 | ('') |
SQL语句:复杂表连接查询
--单独查出4个关系表,根据关联主键分组排序
with Patent as (select row_number() over(partition by StProjecZtbId order by StProjecZtbId) rowNumber,StProjecZtbId,PatentName from STPatent),
Thesis as(select row_number() over(partition by StProjecZtbId order by StProjecZtbId) rowNumber,StProjecZtbId,ThesisName from STThesis),
Other as(select row_number() over(partition by StProjecZtbId order by StProjecZtbId) rowNumber,StProjecZtbId,[Name] from STOther)
--主表关联4个关系表组成的零时表
select st.Id,st.StProjectName,T.* from StProjec st
left join (select isnull(Patent.StProjecZtbId,isnull(Thesis.StProjecZtbId,Other.StProjecZtbId)) StProjecZtbId,PatentName,ThesisName,Other.[Name]
from Patent
full join Thesis on Patent.rowNumber = Thesis.rowNumber and Patent.StProjecZtbId = Thesis.StProjecZtbId --根据主键和同主键数据序号关联,做到数据并行
full join Other on Patent.rowNumber = Other.rowNumber and Patent.StProjecZtbId = Other.StProjecZtbId --根据主键和同主键数据序号关联,做到数据并行
) T on st.Id=T.StProjecZtbId --最后关联主表Id
where 1=1
order by StProjecZtbId
查询结果:
主表主键ID Id | 主表 StProjectName | 关系表1 PatentName | 关系表2 ThesisName | 关系表3 Name |
1 | 项目名称1 | 专利名称1 | 论文名称1 | 其他名称1 |
1 | 项目名称1 | 专利名称2 | 论文名称2 | 其他名称2 |
1 | 项目名称1 | 专利名称3 | 论文名称3 | NULL |
1 | 项目名称1 | NULL | 论文名称4 | NULL |