给点数据吧select a.aa from a left join ab c on a.aa=c.aa left join b on b.bb=c.cc
select a.aa from a left join ab on a.aa=ab.aa join b on ab.bb=b.bb
select A.aa,B.BB from AB join B on AB.BB=B.BB join A on AB.aa=A.aa
要A表所有数据,用左连接 select a.aa from a left join ab on a.aa=ab.aa join b on ab.bb=b.bb
select a.aa from a left join ab on a.aa=ab.aa join b on ab.bb=b.bb
答题都是靠猜。。 用两个LEFT JOIN 试试吧,5L小爱那个
SELECT A.* FROM A LEFT JOIN AB ON A.AA=AB.AA LEFT JOIN B ON AB.BB=B.BB
select a.aa, b.* from a left join ab on a.aa = ab.aa join b on ab.bb = b.bb
select a.aa from a,b,ab where a.aa=ab.aa and b.bb=ab.bb
两个left join SELECT A.* FROM A LEFT JOIN AB ON A.AA=AB.AA LEFT JOIN B ON AB.BB=B.BB
-- ========================================= -- -----------t_mac 小编------------------- --------------------希望有天成为大虾---- -- =========================================IF OBJECT_ID('A') IS NOT NULL DROP TABLE A GO CREATE TABLE A( AA int) go insert A SELECT 2 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 8 UNION ALL SELECT 9 GO IF OBJECT_ID('B') IS NOT NULL DROP TABLE B GO CREATE TABLE B( BB int) go insert B SELECT 2 UNION ALL SELECT 1 GO IF OBJECT_ID('AB') IS NOT NULL DROP TABLE AB GO CREATE TABLE AB( AA int,BB INT ) go insert AB SELECT 2,1 UNION ALL SELECT 1,3 UNION ALL SELECT 3,2 UNION ALL SELECT 4,6 UNION ALL SELECT 5,8 go select a.AA,K.BB from A LEFT JOIN (SELECT AB.* FROM B JOIN AB ON B.BB=AB.BB) K ON A.AA=K.AA /* AA BB ----------- ----------- 2 1 1 NULL 3 2 4 NULL 5 NULL 8 NULL 9 NULL */
昨天写了个也是3表联合的,select a.aa from a,b,ab where b.bb=ab.bb and ab.aa=a.aa
select a.AA,K.BB from A LEFT JOIN (SELECT AB.* FROM B JOIN AB ON B.BB=AB.BB) K ON A.AA=K.AA 肯定是LEFT JOIN的使用。
a.aa
from
A,B,AB
where
a.aa=ab.aa and b.bb=ab.bb
?
from a表 a
left join (
select a.aa,b.* --因为不知道实用列,故用*代替。实际操作中请勿用*
from AB表 a
right join b表 b on a.bb=b.bb )
c on a.aa=c.aa
则
子查询中的right join b表 可以改为 join b表
left join ab c on a.aa=c.aa
left join b on b.bb=c.cc
from a left join ab on a.aa=ab.aa join b on ab.bb=b.bb
select A.aa,B.BB from AB join B on AB.BB=B.BB join A on AB.aa=A.aa
要A表所有数据,用左连接
select a.aa
from a left join ab on a.aa=ab.aa join b on ab.bb=b.bb
from a left join ab on a.aa=ab.aa join b on ab.bb=b.bb
用两个LEFT JOIN 试试吧,5L小爱那个
a.aa
from
a,b,ab
where
a.aa=ab.aa
and
b.bb=ab.bb
两个left join
SELECT A.* FROM A LEFT JOIN AB ON A.AA=AB.AA LEFT JOIN B ON AB.BB=B.BB
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('A') IS NOT NULL
DROP TABLE A
GO
CREATE TABLE A( AA int)
go
insert A SELECT 2
UNION ALL SELECT 1
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 8
UNION ALL SELECT 9
GO
IF OBJECT_ID('B') IS NOT NULL
DROP TABLE B
GO
CREATE TABLE B( BB int)
go
insert B SELECT 2
UNION ALL SELECT 1
GO
IF OBJECT_ID('AB') IS NOT NULL
DROP TABLE AB
GO
CREATE TABLE AB( AA int,BB INT )
go
insert AB SELECT 2,1
UNION ALL SELECT 1,3
UNION ALL SELECT 3,2
UNION ALL SELECT 4,6
UNION ALL SELECT 5,8
go
select
a.AA,K.BB
from
A LEFT JOIN (SELECT AB.* FROM B JOIN AB ON B.BB=AB.BB) K
ON A.AA=K.AA
/*
AA BB
----------- -----------
2 1
1 NULL
3 2
4 NULL
5 NULL
8 NULL
9 NULL
*/
a.AA,K.BB
from
A LEFT JOIN (SELECT AB.* FROM B JOIN AB ON B.BB=AB.BB) K
ON A.AA=K.AA
肯定是LEFT JOIN的使用。
AB表数据
aa bb
9081600101 16000090108
9081600202 16000090208
9082200202 22000090108
9082200202 22000090208
9082002017 22000090308
9082203327 22000090408
9082205043 22000090508
9082203341 22000090608
9082304401 23000090708
9082304401 23000090808
9082304401 23000090908
9082304401 23000091008
9082304401 23000091108
9082304401 23000091208
9082304401 23000091308
9082304401 23000091408
9082304401 23000091508
9082304401 23000091608
9082304401 23000091708
9082304401 23000091808
9082304401 23000091908
9082304401 23000092008
9082304401 23000092108
9082304805 23000092208
9082304805 23000092308
9082304805 23000092408
9082304805 23000092508
9082304805 23000092608
9082304805 23000092708
9082304805 23000092808
9082304805 23000092908
9082304805 23000093008
9082304805 23000093108
9082304805 23000093208
9082304805 23000093308
9082304805 23000093408
9082304805 23000093508
9082304805 23000093608
9082304805 23000093708
9082304805 23000093808
9082304805 23000093908
9082304805 23000094008
9082304805 23000094108
9082304805 23000094208
9082304805 23000094308
9082304805 23000094408
9082304401 23000094508
9082304401 23000094608
9082304401 23000094708
9082304401 23000094808
9082304704 23000094908
9082304805 23000095008
9082305007 23000095108
9082305108 23000095208
9082401102 24000094708
9082401102 24000094808
9082401203 24000094908
9082401001 24000095008a表数据
aa
09081600101
09081600202
09081700301
09081700402
09081700503
09081900101
09081900202
09081900303
09081900404
09081900505
09081900606
09081900707
09081900808
09081900909
09081901010
09082001101
09082001202
09082001203
09082001505
09082001606
09082001707
09082001808
09082001909
09082002010
09082200101
09082200202
09082200303
09082200404
09082200505
09082200606
09082200607
09082200608
09082200609
09082200610
09082200611
09082200612
09082200613
09082201414
09082201415
09082201416
09082201417
09082002010
09082002011
09082002012
09082002013
09082002014
09082002015
09082002016
09082002017
09082101801
09082101802
09082101803
09082101804
09082101805
09082101806
09082101807
09082202518
09082202519
09082202520
09082202521
09082202522
09082202523
09082202524
09082202525
09082203326
09082203327
09082203328
09082203329
09082203330
09082203331
09082203332
09082203333
09082203334
09082203335
09082203336
09082203337
09082203338
09082203339
09082203340
09082203341
09082204942
09082205043
09082304401
09082304502
09082304603
09082304704
09082304805
09082304995
09082305007
09082305108
10082300101
09082305263
10082300202
09082401001
09082401102
09082401203 b表数据
bb
16000090108
16000090208
22000090108
22000090208
22000090308
22000090408
22000090508
22000090608
23000090708
23000090808
23000090908
23000091008
23000091108
23000091208
23000091308
23000091408
23000091508
23000091608
23000091708
23000091808
23000091908
23000092008
23000092108
23000092208
23000092308
23000092408
23000092508
23000092608
23000092708
23000092808
23000092908
23000093008
23000093108
23000093208
23000093308
23000093408
23000093508
23000093608
23000093708
23000093808
23000093908
23000094008
23000094108
23000094208
23000094308
23000094408
23000094508
23000094608
23000094708
23000094808
23000094908
23000095008
23000095108
23000095208
24000094708
24000094808
24000094908
24000095008