select a.*,if(b.aid is not null,'B',if(c.aid is not null,'C','D')) as tableName from a left join b on a.id=b.aid left join c on a.id=c.aid left join d on a.id=d.aid order by a.id limit 10;
个人愚见 if(b.aid is not null,'B',if(c.aid is not null,'C','D')) 这句应该可以不要 但我也是小菜
select a.id,u.t from A left join ( select 'B' as t,id from B union all select 'C' as t,id from B union all select 'D' as t,id from B ) u on a.id=u.id
贴建表及插入记录的SQL,及要求结果出来看看 select a1.id,b1.bz from A a1 left join ( select 'B' as bm,id from b union all select 'C' as bm,id from c union all select 'D' as bm,id from d ) b1 on a1.id=b1.id order by a1.id desc limit 10orselect a1.id,b1.bz from A a1 left join ( select 'B' as bm,id from b union all select 'C' as bm,id from c union all select 'D' as bm,id from d ) b1 on a1.id=b1.id where b1.id is not null order by a1.id desc limit 10
select a.id,u.t from A left join ( select 'B' as t,id from B union all select 'C' as t,id from B union all select 'D' as t,id from B ) u on a.id=u.id order by a1.id desc limit 10
如果B C D 每张表都有10万级别的数据,我发现效率很差啊。有没有效率比较高的,感谢各位大虾。
select a.id, (select id from b) as bid, (select id from c) as cid, (select id from d) as did from A limit 10b,c,d 表中创建基于ID的索引。
和BCD没关系 因为你取的是A的记录
但是我要join B C D啊 难道也没关系吗?
谢谢,创建索引貌似也有点慢这得union多少数据啊。
贴建表及插入记录的SQL,及要求结果出来看看在4表的ID上建立索引SELECT a.*,CASE WHEN b.id IS NOT NULL THEN 'B' WHEN c.id IS NOT NULL THEN 'C' ELSE 'D' END AS bm FROM a LEFT JOIN b ON a.id=b.id LEFT JOIN c ON a.id=c.id LEFT JOIN d ON a.id=d.id ORDER BY a.id DESC LIMIT 10
from a
left join b on a.id=b.aid
left join c on a.id=c.aid
left join d on a.id=d.aid
order by a.id
limit 10;
个人愚见 if(b.aid is not null,'B',if(c.aid is not null,'C','D')) 这句应该可以不要 但我也是小菜
from A left join (
select 'B' as t,id from B
union all
select 'C' as t,id from B
union all
select 'D' as t,id from B
) u on a.id=u.id
select a1.id,b1.bz from A a1 left join (
select 'B' as bm,id from b
union all
select 'C' as bm,id from c
union all
select 'D' as bm,id from d
) b1 on a1.id=b1.id order by a1.id desc limit 10orselect a1.id,b1.bz from A a1 left join (
select 'B' as bm,id from b
union all
select 'C' as bm,id from c
union all
select 'D' as bm,id from d
) b1 on a1.id=b1.id where b1.id is not null order by a1.id desc limit 10
from A left join (
select 'B' as t,id from B
union all
select 'C' as t,id from B
union all
select 'D' as t,id from B
) u on a.id=u.id order by a1.id desc limit 10
(select id from c) as cid,
(select id from d) as did
from A
limit 10b,c,d 表中创建基于ID的索引。
谢谢,创建索引貌似也有点慢这得union多少数据啊。
WHEN c.id IS NOT NULL THEN 'C'
ELSE 'D' END AS bm
FROM a
LEFT JOIN b ON a.id=b.id
LEFT JOIN c ON a.id=c.id
LEFT JOIN d ON a.id=d.id
ORDER BY a.id DESC LIMIT 10