有A,B,C,D四张表
其中A,B内连接,和C,D内连接的结果做左外连接我自己写了个
如下感觉不是很爽,有没其他解决办法呢?=======================================
Select A.*
,B.*
,C.*
,D.*
from A as A
inner join B AS B
on a.id = b.id
left outer join
(select C.*
from C AS C
inner join D as D
on c.id = d.id) AS C
left outer join D AS D
on C.id = D.id
其中A,B内连接,和C,D内连接的结果做左外连接我自己写了个
如下感觉不是很爽,有没其他解决办法呢?=======================================
Select A.*
,B.*
,C.*
,D.*
from A as A
inner join B AS B
on a.id = b.id
left outer join
(select C.*
from C AS C
inner join D as D
on c.id = d.id) AS C
left outer join D AS D
on C.id = D.id
select *
from a join b on a.id = b.id
join c on a.cid = c.id
join d on c.id = d.id
,B.*
,C.*
,D.*
from A as A
inner join B AS B
on a.id = b.id
left outer join
(select C.*
from C AS C
inner join D as D
on c.id = d.id) AS C
on A.id = C.id
left outer join D AS D
on C.id = D.id
不好意思漏了个条件
,B.*
,C.*
,D.*
from A as A
inner join B AS B on a.id = b.id
left join c as c on c.id=a.id
inner join d as d on c.id=d.id
你这样写就是多次一举,直接 A B C D 都连接查询就是了
select *
from a join b on a.id = b.id
left join c on a.id = c.id
join d on c.id = d.id
比如
Aid,Aval
1,1A
2,2A
3,3A
4,4A
Bid,Bval
1,1B
2,2B
3,3B
5,5B
Cid,Cval
1,1C
2,2C
Did,Dval
1,1D
3,3D要查出的
结果是
1,1A,1,1B,1,1C,1,1D
2,2A,2,2B,NULL,NULL,NULL,NULL
3,3A,3,3B,NULL,NULL,NULL,NULL
mssql 中到没测试.难到这两个东西对表连接的处理方式不同?
Select A.*
,B.*
,C.*
,D.*
from A as A
inner join B AS B on a.id = b.id
left join c as c on c.id=a.id
left join d as d on c.id=d.id
我希望的是c表和d表的数据同时出现,要么都有要么都没有
这样写有可能 出现 A,B,C,NULL这样的组合结果也不是我想要的
,B.*
,C.*
from A as A
inner join B AS B
on a.id = b.id
left outer join
(select C.*,D.*
from C AS C
inner join D as D
on c.id = d.id) AS C
on A.id = C.id这样应该就可以了,但是实际使用时得显示指定列名,不然会报错