-- 已使用
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'
多个表的连接使用join连接，有完全连接、左外/右外连接等等，根据自己的需求，选择合适的命令语句即可，，，相应的命令网上或者数据库书上都有，可以参考！加油！ 祝你成功！ 谢谢
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
)
多个表不相关，但都要显示的大致思路是：
用join即可。
嗯嗯，就是：
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
)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
)
复制别人的，赚个币。。。
LEFT JOIN 关联