我有一个查询
SELECT * FROM cms_contentindex i LEFT JOIN cms_content3 c USING(tid) WHERE i.ifpub=1 AND i.mid='3' AND cid IN(4) ORDER BY i.hits desc LIMIT 0,30然后我要在这个语句中加入另外两个表的查询结果SELECT s.tagname,s.tagid FROM cms_contenttag t LEFT JOIN cms_tags s USING(tagid) WHERE t.tid='cms_contentindex的tid'我想把这两条语句组合成一条,不知道有什么办法
SELECT * FROM cms_contentindex i LEFT JOIN cms_content3 c USING(tid) WHERE i.ifpub=1 AND i.mid='3' AND cid IN(4) ORDER BY i.hits desc LIMIT 0,30然后我要在这个语句中加入另外两个表的查询结果SELECT s.tagname,s.tagid FROM cms_contenttag t LEFT JOIN cms_tags s USING(tagid) WHERE t.tid='cms_contentindex的tid'我想把这两条语句组合成一条,不知道有什么办法
FROM cms_contenttag t LEFT JOIN cms_tags s USING(tagid)
WHERE t.tid in (
SELECT i.tid
FROM cms_contentindex i LEFT JOIN cms_content3 c USING(tid)
WHERE i.ifpub=1
AND i.mid='3'
AND cid IN(4)
ORDER BY i.hits desc
LIMIT 0,30
)
SELECT * FROM cms_contentindex i LEFT JOIN cms_content3 c USING(tid) WHERE i.ifpub=1 AND i.mid='3' AND cid IN(4) ORDER BY i.hits desc
LIMIT 0,30) a1
left join
(SELECT s.tagname,s.tagid FROM cms_contenttag t LEFT JOIN cms_tags s USING(tagid) WHERE t.tid='cms_contentindex的tid') b1
on a1.tid=b1.tagid
cms_contenttag、cms_tags通过tagidcms_contentindex、cms_content3这两个表的内容我也要查询出来
from (
SELECT *
FROM cms_contentindex i LEFT JOIN cms_content3 c USING(tid)
WHERE i.ifpub=1
AND i.mid='3'
AND cid IN(4)
ORDER BY i.hits desc
LIMIT 0,30) a left join (
SELECT s.tagname,s.tagid
FROM cms_contenttag t LEFT JOIN cms_tags s USING(tagid)
) b on a.tid=b.tid但不清楚你的limit 30 想在哪一步控制,上面SQL是先控制,这样结果就不一定是 30 条了。或者会多。
SELECT * FROM cms_contentindex i LEFT JOIN cms_content3 c USING(tid)
left join cms_contenttag t on t.tid=i.tid
LEFT JOIN cms_tags s on t.tagid=s.tagid
WHERE i.ifpub=1 AND i.mid='3' AND cid IN(4)
ORDER BY i.hits desc LIMIT 0,30