sql连表这三个单独查出来的数据怎样连在一起

-- 已使用
SELECT count(cacwo.id)
FROM carlife_order co
LEFT JOIN carlife_activity_commodity_write_off cacwo ON cacwo.carlife_order_id = co.id
WHERE cacwo.id != '' AND cacwo.type = '1' AND cacwo.carlife_commodity_id = '1'
--已购买
SELECT count(co.id)
FROM carlife_order co
LEFT JOIN carlife_commodity cc ON cc.id = co.carlife_commodity_id
WHERE cc.id = '1'

SELECT cc.name ,cc.id
FROM carlife_commodity cc
LEFT JOIN carlife_activity_commodity cac ON cac.carlife_commodity_id = cc.id
LEFT JOIN carlife_activity ca ON ca.id = cac.carlife_activity_id
WHERE ca.id = '1'

12个回答

多个表的连接使用join连接,有完全连接、左外/右外连接等等,根据自己的需求,选择合适的命令语句即可,,,相应的命令网上或者数据库书上都有,可以参考!加油! 祝你成功! 谢谢

多个表不相关,但都要显示的大致思路是:
用join即可。

en_joker
孤芳不自賞 把查询出来的列当成单独一个表。
2 年多之前 回复

select *
from a left join b on a.id=b.id
left join c on b.id=c.id

SELECT count(co.id) as count1,(SELECT count(cacwo.id)
FROM carlife_order co
LEFT JOIN carlife_activity_commodity_write_off cacwo ON cacwo.carlife_order_id = co.id
WHERE cacwo.id != '' AND cacwo.type = '1' AND cacwo.carlife_commodity_id = '1') as count2
FROM carlife_order co
LEFT JOIN carlife_commodity cc ON cc.id = co.carlife_commodity_id
WHERE cc.id = '1'
SELECT cc.name ,cc.id
FROM carlife_commodity cc
LEFT JOIN carlife_activity_commodity cac ON cac.carlife_commodity_id = cc.id
LEFT JOIN carlife_activity ca ON ca.id = cac.carlife_activity_id
WHERE ca.id = '1'

把第一个查询结果当作一个值放到第二个sql的列里面。

select * from (SELECT count(cacwo.id)
FROM carlife_order co
LEFT JOIN carlife_activity_commodity_write_off cacwo ON cacwo.carlife_order_id = co.id
WHERE cacwo.id != '' AND cacwo.type = '1' AND cacwo.carlife_commodity_id = '1' ) as a,(SELECT count(co.id)
FROM carlife_order co
LEFT JOIN carlife_commodity cc ON cc.id = co.carlife_commodity_id
WHERE cc.id = '1') as b,(SELECT cc.name ,cc.id
FROM carlife_commodity cc
LEFT JOIN carlife_activity_commodity cac ON cac.carlife_commodity_id = cc.id
LEFT JOIN carlife_activity ca ON ca.id = cac.carlife_activity_id
WHERE ca.id = '1') as c 把三个查询出来的结果分别当做一个虚拟表来查= =

SELECT X FROM(
(SELECT count(cacwo.id)
FROM carlife_order co
LEFT JOIN carlife_activity_commodity_write_off cacwo ON cacwo.carlife_order_id = co.id
WHERE cacwo.id != '' AND cacwo.type = '1' AND cacwo.carlife_commodity_id = '1') a
LEFT JOIN
(SELECT count(co.id)
FROM carlife_order co
LEFT JOIN carlife_commodity cc ON cc.id = co.carlife_commodity_id
WHERE cc.id = '1'
SELECT cc.name ,cc.id
FROM carlife_commodity cc
LEFT JOIN carlife_activity_commodity cac ON cac.carlife_commodity_id = cc.id
LEFT JOIN carlife_activity ca ON ca.id = cac.carlife_activity_id
WHERE ca.id = '1')b on a.x1 = b.x1
)
X 两个查询出来的名(对相同项进行处理),x1 和 x2 两个表相关联的字段

使用uinion all连接

select * from (SELECT count(cacwo.id)
FROM carlife_order co
LEFT JOIN carlife_activity_commodity_write_off cacwo ON cacwo.carlife_order_id = co.id
WHERE cacwo.id != '' AND cacwo.type = '1' AND cacwo.carlife_commodity_id = '1' ) as a,(SELECT count(co.id)
FROM carlife_order co
LEFT JOIN carlife_commodity cc ON cc.id = co.carlife_commodity_id
WHERE cc.id = '1') as b,(SELECT cc.name ,cc.id
FROM carlife_commodity cc
LEFT JOIN carlife_activity_commodity cac ON cac.carlife_commodity_id = cc.id
LEFT JOIN carlife_activity ca ON ca.id = cac.carlife_activity_id
WHERE ca.id = '1') as c 把三个查询出来的结果分别当做一个虚拟表来查= =

-- 已使用
SELECT count(cacwo.id)
FROM carlife_order co
LEFT JOIN carlife_activity_commodity_write_off cacwo ON cacwo.carlife_order_id = co.id
WHERE cacwo.id != '' AND cacwo.type = '1' AND cacwo.carlife_commodity_id = '1'
union all
--已购买
SELECT count(co.id)
FROM carlife_order co
LEFT JOIN carlife_commodity cc ON cc.id = co.carlife_commodity_id
WHERE cc.id = '1'
union all
SELECT cc.name ,cc.id
FROM carlife_commodity cc
LEFT JOIN carlife_activity_commodity cac ON cac.carlife_commodity_id = cc.id
LEFT JOIN carlife_activity ca ON ca.id = cac.carlife_activity_id
WHERE ca.id = '1'

SELECT X FROM(
(SELECT count(cacwo.id)
FROM carlife_order co
LEFT JOIN carlife_activity_commodity_write_off cacwo ON cacwo.carlife_order_id = co.id
WHERE cacwo.id != '' AND cacwo.type = '1' AND cacwo.carlife_commodity_id = '1') a
LEFT JOIN
(SELECT count(co.id)
FROM carlife_order co
LEFT JOIN carlife_commodity cc ON cc.id = co.carlife_commodity_id
WHERE cc.id = '1'
SELECT cc.name ,cc.id
FROM carlife_commodity cc
LEFT JOIN carlife_activity_commodity cac ON cac.carlife_commodity_id = cc.id
LEFT JOIN carlife_activity ca ON ca.id = cac.carlife_activity_id
WHERE ca.id = '1')b on a.x1 = b.x1
)

共12条数据 1 尾页
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐