这样吧具体点说 表A 字段 pid bid cid did eid 表B 字段 tid 表1 字段 tid bid 表2 字段 tid cid 表3 字段 tid did 表4 字段 tid eid现在我只能做到用2句语句来完成 select bid,cid,did,eid from 表B left join 表1 on(表B.tid=表1.tid) left join 表2 on(表B.tid=表2.tid) left join 表3 on(表B.tid=表3.tid) left join 表4 on(表B.tid=表4.tid)select 表A.* from 表A where A.bid=bid and A.cid=cid and A.did=did and A.eid=eid这样有个问题就是万一通过表B,1,2,3,4找到的BID CID..有多条记录 那第2句语句就要多跑N趟 想找个能高效点的解决方法
select a.* from 表A a,( select bid,cid,did,eid from 表B left join 表1 on(表B.tid=表1.tid) left join 表2 on(表B.tid=表2.tid) left join 表3 on(表B.tid=表3.tid) left join 表4 on(表B.tid=表4.tid) ) b where A.bid=B.bid and A.cid=B.cid and A.did=B.did and A.eid=B.eid
--这个意思? select B.* , t.* from B, ( select A.pid , T1.* from A,T1 where A.bid = T1.bid union all select A.pid , T2.* from A,T2 where A.cid = T2.cid union all select A.pid , T3.* from A,T3 where A.did = T3.did union all select A.pid , T4.* from A,T4 where A.eid = T4.eid ) T where B.tid = T.tid
可以执行 谢谢先 我是用MYSQL的 据说MYSQL 子查询效率不高 能否用表连接来做呢?
SELECT p.* FROM products as p LEFT JOIN ticket_use_brand as brand ON(p.brand_id=brand.brand_id) LEFT JOIN ticket_use_star as star ON(p.star_id=star.star_id) RIGHT JOIN tickets as t ON(t.ticket_id=brand.ticket_id AND t.ticket_id=star.ticket_id) WHERE t.ticket_id = 2 倒SQL还能这样写啊。
表A 字段 pid bid cid did eid
表B 字段 tid
表1 字段 tid bid
表2 字段 tid cid
表3 字段 tid did
表4 字段 tid eid现在我只能做到用2句语句来完成
select bid,cid,did,eid from 表B
left join 表1 on(表B.tid=表1.tid)
left join 表2 on(表B.tid=表2.tid)
left join 表3 on(表B.tid=表3.tid)
left join 表4 on(表B.tid=表4.tid)select 表A.* from 表A
where A.bid=bid and A.cid=cid and A.did=did and A.eid=eid这样有个问题就是万一通过表B,1,2,3,4找到的BID CID..有多条记录 那第2句语句就要多跑N趟
想找个能高效点的解决方法
select bid,cid,did,eid from 表B
left join 表1 on(表B.tid=表1.tid)
left join 表2 on(表B.tid=表2.tid)
left join 表3 on(表B.tid=表3.tid)
left join 表4 on(表B.tid=表4.tid)
) b
where A.bid=B.bid and A.cid=B.cid
and A.did=B.did and A.eid=B.eid
select B.* , t.* from B,
(
select A.pid , T1.* from A,T1 where A.bid = T1.bid
union all
select A.pid , T2.* from A,T2 where A.cid = T2.cid
union all
select A.pid , T3.* from A,T3 where A.did = T3.did
union all
select A.pid , T4.* from A,T4 where A.eid = T4.eid
) T
where B.tid = T.tid
我是用MYSQL的 据说MYSQL 子查询效率不高
能否用表连接来做呢?
SELECT p.* FROM products as p LEFT JOIN ticket_use_brand as brand ON(p.brand_id=brand.brand_id) LEFT JOIN ticket_use_star as star ON(p.star_id=star.star_id) RIGHT JOIN tickets as t ON(t.ticket_id=brand.ticket_id AND t.ticket_id=star.ticket_id) WHERE t.ticket_id = 2
倒SQL还能这样写啊。