Mysql统计外表中的记录实例数

Question

我在这些结构中有两个表:

Orders

-------------------------------------------------------
| id | description | created_by | price | created_at  | etc
-------------------------------------------------------

Sample data:
-------------------------------------------------------
| id | description | created_by | price | created_at  | 
-------------------------------------------------------
| 1  |   test 1    |   2        | 10.00 | 2015-10-10 00:00:00
| 2  |   test 2    |   1        | 20.00 | 2015-11-10 00:00:00
| 3  |   test 3    |   3        | 5.00  | 2015-12-10 00:00:00
| 4  |   test 4    |   10       | 100.00| 2015-01-10 00:00:00

还有另一张表

OrderStatus

------------------------------------
| orderid | status | comment      |
------------------------------------

------------------------------------
| orderid | status | comment      |
------------------------------------
| 1       | 1      | This is pending payment|
| 2       | 1      | This is pending payment|
| 1       | 2      |                        |
| 1       | 3      | Canceled due to shipment delay  |
| 2       | 2      | This is pending payment|
| 3       | 1      | This is pending payment|
| 4       | 1      | This is pending payment|
| 4       | 2      |                        |
| 4       | 4      | This is delivered      |

状态的可能值为:待处理:1、付款已完成:2、已取消:3、已交付:4 等

我的愿望是创建返回的查询:

Orders stats
--------------------------------------------------------------
| total | pending |   paid     | canceled  |    delivered    |
--------------------------------------------------------------
  4        1             1            1            1

到目前为止，我已经想出了这些:

#total
SELECT COUNT(distinct a.id) FROM tbl_order a inner join tbl_order_status b on a.id=b.orderid

#pending
SELECT COUNT(distinct a.id) FROM tbl_order a inner join tbl_order_status b on a.id=b.orderid where b.status=1

#paid
SELECT COUNT(distinct a.id) FROM tbl_order a inner join tbl_order_status b on a.id=b.orderid where b.status=2

#canceled
SELECT COUNT(distinct a.id) FROM tbl_order a inner join tbl_order_status b on a.id=b.orderid where b.status=3

#delivered
SELECT COUNT(distinct a.id) FROM tbl_order a inner join tbl_order_status b on a.id=b.orderid where b.status=4

第一个查询结果(总计)符合预期，但其余查询结果均不准确

Answer 1

DISTINCT 这不是一个好的性能实践...尝试使用 GROUP BY 代替。

SELECT status, COUNT(1) FROM tbl_order a inner join tbl_order_status b on a.id=b.orderid GROUP BY b.status

这将导致 n 个状态行，因此您可以 grep 每种类型的所有总和;)