这样写可能会造成重复的。如果表A 的A1,A2在表B的B1中都存在。就会关联2次。 举个例子with cte as (select 1 as id,2 as id1 union all select 2 as id,9 as id1 union all select 0 as id,3 as id1 union all select 4 as id,10 as id1 union all select 10 as id,20 as id1 ),cte1 as (select 1 as id,'a' as name union all select 2 as id,'b' as name union all select 3 as id,'c' as name union all select 4 as id,'d' as name union all select 5 as id,'e' as name union all select 6 as id,'f' as name ) select * from cte as a join cte1 as b on a.id=b.id or a.id1=b.id --结果 id id1 id name ----------- ----------- ----------- ---- 1 2 1 a 1 2 2 b 2 9 2 b 0 3 3 c 4 10 4 d(5 行受影响) 你可以针对这种情况。设置优先级。如果A表中2列都能关联哪个先关联, 然后对最后结果分组。
或者用这个试试with cte as (select 1 as id,2 as id1 union all select 2 as id,9 as id1 union all select 0 as id,3 as id1 union all select 4 as id,10 as id1 union all select 10 as id,20 as id1 ),cte1 as (select 1 as id,'a' as name union all select 2 as id,'b' as name union all select 3 as id,'c' as name union all select 4 as id,'d' as name union all select 5 as id,'e' as name union all select 6 as id,'f' as name ) select * from cte as a join cte1 as b on a.id=b.id union all select * from cte as a join cte1 as b on a.id1=b.id where a.id not in(select id from cte1) --结果 id id1 id name ----------- ----------- ----------- ---- 1 2 1 a 2 9 2 b 4 10 4 d 0 3 3 c(4 行受影响)
一句话,没有真实的实例,无法按楼主的想法去分析。
用distinct 或者 group by
都可以
举个例子with cte as
(select 1 as id,2 as id1 union all
select 2 as id,9 as id1 union all
select 0 as id,3 as id1 union all
select 4 as id,10 as id1 union all
select 10 as id,20 as id1 ),cte1 as
(select 1 as id,'a' as name union all
select 2 as id,'b' as name union all
select 3 as id,'c' as name union all
select 4 as id,'d' as name union all
select 5 as id,'e' as name union all
select 6 as id,'f' as name )
select * from cte as a join cte1 as b
on a.id=b.id or a.id1=b.id
--结果
id id1 id name
----------- ----------- ----------- ----
1 2 1 a
1 2 2 b
2 9 2 b
0 3 3 c
4 10 4 d(5 行受影响)
你可以针对这种情况。设置优先级。如果A表中2列都能关联哪个先关联,
然后对最后结果分组。
(select 1 as id,2 as id1 union all
select 2 as id,9 as id1 union all
select 0 as id,3 as id1 union all
select 4 as id,10 as id1 union all
select 10 as id,20 as id1 ),cte1 as
(select 1 as id,'a' as name union all
select 2 as id,'b' as name union all
select 3 as id,'c' as name union all
select 4 as id,'d' as name union all
select 5 as id,'e' as name union all
select 6 as id,'f' as name ) select * from cte as a join cte1 as b
on a.id=b.id
union all
select * from cte as a join cte1 as b
on a.id1=b.id where a.id not in(select id from cte1)
--结果
id id1 id name
----------- ----------- ----------- ----
1 2 1 a
2 9 2 b
4 10 4 d
0 3 3 c(4 行受影响)