被2分组时计算场的出现 [英] Count Ocurrence of Field whilst being Group By 2

问题描述

我正在努力尝试在item_id行上获取订单ID的计数，非常感谢您的帮助！

I'm struggling to try to have the count of order id on an item_id row, any help is greatly appreciated!

Data
item_id | order_id
1 | Order_1
2 | Order_1
3 | Order_2
4 | Order_3
Desired Result

item_id | order_id | items_in_order
1 | Order_1 | 2
2 | Order_1 | 2
3 | Order_2 | 1
4 | Order_3 | 1


   SELECT S.item_id, S.`order_id`, S.order_total, C.cnt as items_in_order,

       `order_discount` / C.cnt as item_discount,
                `order_total` / C.cnt  as item_price
FROM `orders` S 
LEFT JOIN (SELECT `item_id`, `order_id`, count(`order_id`) as cnt  FROM `supplier_orders` GROUP BY `order_id`) 
C ON S.`order_id` = C.`order_id` AND S.id = C.item_id

This would produce this with null values
item_id | order_id | items_in_order | item_discount | item_price
3009117 | 3029511 | 2    | 0    | 25 
3009118 | 3029511 | null | null | null 

更新，现在似乎可以正常使用

UPDATE, this now seems to work as intended

SELECT S.`item_id`, S.`order_id`, S.order_total, C.cnt as items_in_order,

       `order_discount` / C.cnt as item_discount,
                `order_total` / C.cnt  as item_price
FROM `orders` S 
INNER JOIN (SELECT `item_id`, `order_id`, count(`order_id`) as cnt  FROM `orders` GROUP BY `order_id`) 
C ON S.`order_id` = C.`order_id` 
GROUP BY S.`item_id`

推荐答案

您的查询与您的样本数据无关；但是您似乎想要聚合和排名。在MySQL 8.0中，您可以这样做：

Your query does not relate to your sample data; however you seem to want aggregation and ranking. In MySQL 8.0, you would do:

select
    row_number() over(order by count(*) desc) rn,
    order_id,
    count(*) items_in_order
from data
group by order_id
order by rn

我将第一列命名为 rn （对于 rank ）：我发现 id 在这里令人困惑，因为您已经在表中有一列具有该名称的列。

I named the first column rn (for rank): I find id confusing here, since you already have a column with that name in the table.

在早期版本中，一个选项使用会话变量而不是 row_number（）：

In earlier versions, one option uses a session variable instead of row_number():

select @rn := @rn + 1 rn, order_id, items_in_order
from (
    select order_id, count(*) items_in_order
    from data
    group by order_id
    order by items_in_order desc
) t
cross join (select @rn := 0) r
order by items_in_order desc

这篇关于被2分组时计算场的出现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！