Mysql从表中获取所有数据并计算某些列的数量

Question

表表1结构如下:

`id, rma_id, order_id, product_id, order_nr, comment, admin_comment, add_date`

及其数据:

(40, 1, 110331, 4399, 1, 'comment1', '', '2016-09-30 08:46:54'),
(42, 2, 110331, 4399, 1, 'comment2', '', '2016-09-30 11:18:06'),
(43, 3, 110374, 4399, 1, 'comment3', '', '2016-10-03 05:55:25'),
(44, 4, 110374, 4399, 1, 'comment4', '', '2016-10-03 05:55:43'),
(45, 4, 110374, 4399, 2, 'comment5', '', '2016-10-03 05:55:43');

使用这样的查询来获取精确的 RMA 的所有信息:

SELECT * FROM `rma_products` WHERE `rma_id` = 4

得到这样的结果:

[0] => array(8) {
    ["id"] => string(2) "44"
    ["rma_id"] => string(1) "4"
    ["order_id"] => string(6) "110374"
    ["product_id"] => string(4) "4399"
    ["order_nr"] => string(1) "1"
    ["comment"] => string(16) "comment4"
    ["admin_comment"] => string(0) ""
    ["add_date"] => string(19) "2016-10-03 08:55:43"
}
[1] => array(8) {
    ["id"] => string(2) "45"
    ["rma_id"] => string(1) "4"
    ["order_id"] => string(6) "110374"
    ["product_id"] => string(4) "4399"
    ["order_nr"] => string(1) "2"
    ["comment"] => string(7) "comment5"
    ["admin_comment"] => string(0) ""
    ["add_date"] => string(19) "2016-10-03 08:55:43"
}

但我想再添加一个字段，例如计数，我的结果将如下所示:

[0] => array(8) {
    ["id"] => string(2) "44"
    ["rma_id"] => string(1) "4"
    ["order_id"] => string(6) "110374"
    ["product_id"] => string(4) "4399"
    ["order_nr"] => string(1) "1"
    ["comment"] => string(16) "comment4"
    ["admin_comment"] => string(0) ""
    ["add_date"] => string(19) "2016-10-03 08:55:43"
    ["count"] => string(1) "2"
}
[1] => array(8) {
    ["id"] => string(2) "45"
    ["rma_id"] => string(1) "4"
    ["order_id"] => string(6) "110374"
    ["product_id"] => string(4) "4399"
    ["order_nr"] => string(1) "2"
    ["comment"] => string(7) "comment5"
    ["admin_comment"] => string(0) ""
    ["add_date"] => string(19) "2016-10-03 08:55:43"
    ["count"] => string(1) "2"
}

按照同一个rma_id中有多少个相同的product_id来计算

Answer 1

select t.*,s.cnt from t
join
(select rma_id,product_id,count(*) cnt from t where rma_id = 4 group by rma_id,product_id) s 
on s.rma_id = t.rma_id and t.product_id = s.product_id