按2分组时计算字段的出现次数

pgpifvop 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(402)

我正在努力尝试将订单id计数到一个项目\u id行，非常感谢您的帮助！

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

更新后，现在似乎可以正常工作了

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`

sql mysql Join window-functions count

来源：https://stackoverflow.com/questions/62354177/count-ocurrence-of-field-whilst-being-group-by-2

1条答案

按热度按时间

d4so4syb1#

您的查询与您的样本数据无关；然而，你似乎想要聚合和排名。在mysql 8.0中，您将执行以下操作：

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 （代表等级）：我发现 id 这里很混乱，因为表中已经有一个同名的列。
在早期版本中，一个选项使用会话变量而不是 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

赞(0）回复(0）举报 2021-08-09

我来回答

按2分组时计算字段的出现次数

1条答案

相关问题

热门标签

最新问答