有一个表A和一个表B
表A:
A1 A2 A3 A4
1 01 001 abc
2 null null cde
3 03 003 def表B:
B1 B2 B3 B4
1 01 001 xxx
2 02 002 yyy
null 03 003 zzz
4 04 004 ttt现在要连接A、B,其中A1=B1,A2=B2,A3=B3,如果在A表中A1能在B中找到,则用A1=B1,否则用A2=B2 and A3=B3
(B会比A行数多,所以用左链接,SQL Server 2008)select A1,A4,B4 from A left join B on 这里怎么写。不要用这种写法
select A1,A4,B4 from A left join B on A.A1=B.B1 where B.B1 in (select A1 from A)
union all
select A1,A4,B4 from A left join B on A.A2=B.B2 and A.A3=B.B3 where B.B1 not in (select A1 from A)SQL Server 2008数据库连接表连接,
表A:
A1 A2 A3 A4
1 01 001 abc
2 null null cde
3 03 003 def表B:
B1 B2 B3 B4
1 01 001 xxx
2 02 002 yyy
null 03 003 zzz
4 04 004 ttt现在要连接A、B,其中A1=B1,A2=B2,A3=B3,如果在A表中A1能在B中找到,则用A1=B1,否则用A2=B2 and A3=B3
(B会比A行数多,所以用左链接,SQL Server 2008)select A1,A4,B4 from A left join B on 这里怎么写。不要用这种写法
select A1,A4,B4 from A left join B on A.A1=B.B1 where B.B1 in (select A1 from A)
union all
select A1,A4,B4 from A left join B on A.A2=B.B2 and A.A3=B.B3 where B.B1 not in (select A1 from A)SQL Server 2008数据库连接表连接,
Declare @tableB table (B1 int ,B2 varchar(10),B3 varchar(10),B4 varchar(10))insert @tableA
select 1, '01', '001', 'abc' union all
select 2, null, null, 'cde' union all
select 3, '03', '003', 'def'insert @tableB
select 1, '01', '001', 'xxx' union all
select 2, '02', '002', 'yyy' union all
select null, '03', '003', 'zzz' union all
select 4, '04', '004', 'ttt'select a.A1,a.A4,b.B4
from @tableA A
left join @tableB B on (a.A1= b.B1)or (a.A2=b.B2 and a.A3=b.B3)/*
A1 A4 B4
1 abc xxx
2 cde yyy
3 def zzz*/
INSERT #tbA
SELECT 1, '01', '001', 'abc' UNION ALL
SELECT 2, NULL, NULL, 'cde' UNION ALL
SELECT 3, '03', '003', 'def'CREATE TABLE #tbB(B1 INT, B2 VARCHAR(10), B3 VARCHAR(10), B4 VARCHAR(10))
INSERT #tbB
SELECT 1, '01', '001', 'xxx' UNION ALL
SELECT 2, '02', '002', 'yyy' UNION ALL
SELECT null, '03', '003', 'zzz' UNION ALL
SELECT 4, '04', '004', 'ttt'--呵呵,又是你。参考一下吧:
SELECT a.A1,a.A4,b.B4
FROM #tbA a
LEFT JOIN #tbB b
ON (a.A1 = b.B1)
OR (a.A2=b.B2 and a.A3=b.B3)
/*
A1 A4 B4
1 abc xxx
2 cde yyy
3 def zzz
*/--楼主注意:
select A1,A4,B4 from A left join B on A.A1=B.B1 where B.B1 in (select A1 from A)
union ALL
--楼主这句明显有问题,用LEFT JOIN,再加NOT EXISTS,明显一条记录也查不出来
select A1,A4,B4 from A left join B on A.A2=B.B2 and A.A3=B.B3 where B.B1 not in (select A1 from A)
假设B表中还有一列5,01,001,mmm,我的做法是一直有A1=1的与B1=1的连,一共3行。而你还增加了一个A2='01'和B2='01' and A3='001'和B3='001'的连,你这样连,应该是4行吧,你可以试试。
不好意思,在数据库上试过的,这台电脑没有数据库。你的问题跟我上面一个回答存在问题一样的吧
select A.A1, A.A4, case when t1.B1 is null then t2.B4 else t1.B4
from A
left join B t1
on A.A1 = t1.B1
left join B t2
on A.A2 = t2.B2
and A.A3 = t2.B3楼主觉得这样对不?sql文没跑过,思路大概是这样。
不好意思,在数据库上试过的,这台电脑没有数据库。你的问题跟我上面一个回答存在问题一样的吧
应该就是正解啊(估计少个end),上次就是这个问题,郁闷。早点睡,谢谢,晚安
select a.A1,a.A4,b.B4
from @tableA A
left join @tableB B on ((a.A1= b.B1) and not (a.A2=b.B2 and a.A3=b.B3)) and (not (a.A1= b.B1) and (a.A2=b.B2 and a.A3=b.B3))
楼主,这样能达到你要的异或效果了
select a.A1,a.A4,b.B4
from @tableA A
left join @tableB B on ((a.A1= b.B1) and not (a.A2=b.B2 and a.A3=b.B3)) and (not (a.A1= b.B1) and (a.A2=b.B2 and a.A3=b.B3))
楼主,这样能达到你要的异或效果了
虽然没用过异或,感觉你这样中间也是Or,而且与我上面的问题一样的。我的假设。