一个查询的写法

Richard345265669 2011-11-09 06:02:41

最近写了类似于这样一个查询
select b.name1,c.name1,d.name1,e.name1 from A a
left join place b on a.bid on b.id
left join place c on a.cid on c.id
left join place d on a.did on d.id
left join place e on a.eid on e.id
就是同样一个表join（10+次）了很多次，有木有变通的方法可以减少join表的次数？

...全文

119 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

liujuaner 2011-11-14

打赏
举报

同意4楼7楼写法;不过还有一种写法略显麻烦
select
(case t.t when 1 then b.name else null end) as bname,
(case t.t when 2 then b.name else null end) as cname,
(case t.t when 3 then b.name else null end) as dname,
(case t.t when 4 then b.name else null end) as ename
from A a
join (select 1 union all select 2 union all select 3 union all select 4) t(t) on 1 = 1
join place b on (case t.t when 1 then a.bid when 2 then a.cid when 3 then a.did when 4 then a.eid end) = b.id

苦苦的潜行者 2011-11-14

打赏
举报

[Quote=引用 8 楼 liujuaner 的回复:]

同意4楼7楼写法;不过还有一种写法略显麻烦
select
(case t.t when 1 then b.name else null end) as bname,
(case t.t when 2 then b.name else null end) as cname,
(case t.t when 3 then b.name else null end) as dname,
……
[/Quote]

再说lz就崩溃了....

dawugui 2011-11-10

打赏
举报

select b.name1,c.name1,d.name1,e.name1 from A a
left join place b on a.bid = b.id
left join place c on a.cid = c.id
left join place d on a.did = d.id
left join place e on a.eid = e.id

这是你表设计的问题,目前想省略是做不到了.

kevinjay567 2011-11-10

打赏
举报

select * from tba a
left join tbb b on a.col=b.col
left join tbc c on a.col=c.col
left join tbd d on a.col=d.col
……

--小F-- 2011-11-09

打赏
举报

连接的关联字段没办法减少如果减少了就成了笛卡尔积了

oO寒枫Oo 2011-11-09

打赏
举报



select (select name1 from place where id=a.bid) as b_name1,

  (select name1 from place where id=a.cid) as c_name1,

  (select name1 from place where id=a.did) as d_name1,

  (select name1 from place where id=a.eid) as e_name1

  from A a