还记得年前的一个SQL问题,当时对SQL刚接触,因此绕开了它。用了别的办法。昨天看SQL突然想起了这个问题。百思不得其解,然后去SQL Server技术交流群,也请教了,大神高文佳,何志勇提示我因为先分组然后再链接。但由于小弟技术是在是太菜,因此没能弄出我想要的结果来。后来由于太晚的原因或者是问题太简单(当然大神们实在是很热情),大神们都睡觉了。今天还是在纠结这个问题的解决方法。
先给出测试用的初始化表和数据:
CREATE TABLE [dbo].tab_1( [Id] [int] NOT NULL, [SkillId] [int] NOT NULL ) ON [PRIMARY] CREATE TABLE [dbo].tab_2( [Id] [int] NOT NULL, [SkillId] [int] NOT NULL ) ON [PRIMARY] GO insert into tab_1 values(1,1) insert into tab_1 values(1,2) insert into tab_1 values(2,1) insert into tab_1 values(2,3) insert into tab_1 values(2,6) insert into tab_2 values(5,1) insert into tab_2 values(5,2) insert into tab_2 values(5,4) insert into tab_2 values(7,1) insert into tab_2 values(7,3) insert into tab_2 values(7,6) |
查询表数据如下:
问题1:现在的问题是想查出这部分数据(语言表达能力不好,我就直接给图了^_^):
也就是每组根据Id分组后,取tab_1和tab_2有完全相同的SkillId分组项。
开始就一直思考如何用分组、联接来实现。后来发现有各种不同的问题(当然是我的问题),最终换了思路,将每个表根据主键分组(为了测试方便我没有设主键),然后使用合并列的方法来曲线实现了一下:
with cte1 as(
select Id, stuff((select ','+ CONVERT(varchar,SkillId) from tab_1 t where t.Id=t1.Id for xml path('')),1,1,'') as Skills from tab_1 t1
group by Id
),
cte2 as(
select Id, stuff((select ','+ CONVERT(varchar,SkillId) from tab_2 tt where tt.Id=tt1.Id for xml path('')),1,1,'') as Skills from tab_2 tt1
group by Id
)select * from cte1 join cte2 on cte1.Skills=cte2.Skills
但是我想这个方法是不好的。因为如果此时我给表tab_1添加一列:
insert into tab_1 values(2,5)