建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
FROM fxtx_order_info AS odr
INNER JOIN
(SELECT * FROM fxtx_order_goods WHERE `status` = 1) AS gds
ON odr.id = gds.order_id
GROUP BY odr.idSELECT COUNT(gds.suppliers_id) AS '配送完的供货商数量'
FROM fxtx_order_info AS odr
INNER JOIN
(SELECT * FROM fxtx_order_goods WHERE `status` = 1) AS gds
ON odr.id = gds.order_id
GROUP BY odr.id
, gds.`suppliers_id`
FROM fxtx_order_info AS odr
INNER JOIN
(SELECT * FROM fxtx_order_goods WHERE `status` = 1) AS gds
ON odr.id = gds.order_id
GROUP BY odr.id
SELECT COUNT(gds.suppliers_id) AS '配送完的供货商数量'
FROM fxtx_order_info AS odr
INNER JOIN
(SELECT * FROM fxtx_order_goods WHERE `status` = 1) AS gds
ON odr.id = gds.order_id
GROUP BY gds.suppliers_idSELECT COUNT(odr.id) AS '未配送完的订单数量'
FROM fxtx_order_info AS odr
INNER JOIN
(SELECT * FROM fxtx_order_goods WHERE `status` = 0) AS gds
ON odr.id = gds.order_id
GROUP BY odr.idSELECT COUNT(gds.suppliers_id) AS '未配送完的供货商数量'
FROM fxtx_order_info AS odr
INNER JOIN
(SELECT * FROM fxtx_order_goods WHERE `status` = 0) AS gds
ON odr.id = gds.order_id
GROUP BY gds.suppliers_id
SELECT
T1.odr_num_y AS '配送完的订单数量',
T2.gds_num_y AS '配送完的供货商数量',
T3.odr_num_n AS '未配送完的订单数量',
T4.gds_num_n AS '未配送完的供货商数量'
FROM
(
SELECT '1' AS id, COUNT(odr.id) AS odr_num_y
FROM fxtx_order_info AS odr
INNER JOIN
(SELECT * FROM fxtx_order_goods WHERE `status` = 1) AS gds
ON odr.id = gds.order_id
GROUP BY odr.id
) AS T1
JOIN
(
SELECT '1' AS id, COUNT(gds.suppliers_id) AS gds_num_y
FROM fxtx_order_info AS odr
INNER JOIN
(SELECT * FROM fxtx_order_goods WHERE `status` = 1) AS gds
ON odr.id = gds.order_id
GROUP BY gds.suppliers_id
) AS T2
ON T1.id =T2.id
JOIN
(
SELECT '1' AS id, COUNT(odr.id) AS odr_num_n
FROM fxtx_order_info AS odr
INNER JOIN
(SELECT * FROM fxtx_order_goods WHERE `status` = 0) AS gds
ON odr.id = gds.order_id
GROUP BY odr.id
) AS T3
ON T1.id =T3.id
JOIN
(
SELECT '1' AS id, COUNT(gds.suppliers_id) AS gds_num_n
FROM fxtx_order_info AS odr
INNER JOIN
(SELECT * FROM fxtx_order_goods WHERE `status` = 0) AS gds
ON odr.id = gds.order_id
GROUP BY gds.suppliers_id
) AS T4
ON T1.id =T4.id