gpt4 book ai didi

mysql - 如何首先获取订单最多的供应商列表

转载 作者:行者123 更新时间:2023-11-29 10:27:39 27 4
gpt4 key购买 nike

关系

Supplier -> 与 orders 的一对多关系

| suppliers                             |
| ---------- |
| id | name | archived (bool) | user_id |

| orders |
| ---------------- |
| id | supplier_id | creator_id |

期望

现在,我想获取热门列表suppliers对于给定的supplier.user_idorders.creator_idsupplier不应该是archived (请在问题末尾找到预期结果)。

这是我想要的:

  • 供应商 ORDER BY 最大值(count(orders.supplier_id))
  • 其中,supplier.archived = false
  • 其中供应商.user_id = 2
  • 其中orders.creator_id = 1

尝试失败

这是我不成功的尝试,我不知道如何在此查询中添加供应商条件。

  select supplier_id, COUNT(*) as count_suppliers 
from orders
where creator_id = 2
group by orders.supplier_id
order by count_suppliers desc

这就是我想要的

suppliers

| id | user_id | archived |
| --- | --------- | ------- |
| 1 | 2 | false |
| 2 | 2 | false |
| 3 | 2 | false |
| 4 | 2 | false |
| 5 | 2 | true |

orders

| id | creator_id | supplier_id |
| -- | --------- | ------------ |
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 1 | 1 |
| 4 | 1 | 1 |
| 5 | 1 | 2 |
| 6 | 1 | 2 |
| 7 | 1 | 3 |
| 8 | 1 | 4 |
| 9 | 1 | 4 |
| 10 | 1 | 4 |
| 11 | 1 | 5 |

expected output

| supplier_id | supplier_count |
| 1 | 4 |
| 4 | 3 |
| 2 | 2 |
| 3 | 1 |

解决方案

所以最后通过引用Vamsi的回答,在这里你可以找到解决这个问题的RAW SQL版本和ActiveRecord(Rails)版本:

RAW SQL VERSION

SELECT o.supplier_id, COUNT(*) AS count_suppliers 
FROM suppliers s
JOIN orders o ON s.id=o.supplier_id
WHERE s.user_id=2
AND s.archived=FALSE
AND o.creator_id=2
GROUP BY o.supplier_id
ORDER BY count_suppliers DESC
LIMIT 5

ActiveRecord (Rails) Version

Supplier
.joins(:orders)
.where(user_id: 2, archived: false, orders: { creator_id: 2 })
.group("orders.supplier_id")
.order("count_all DESC")
.limit(limit)
.count

最佳答案

您可以加入表格并进行计数。

select p.supplier_id, COUNT(*) as count_suppliers 
from purchase_orders p
join suppliers s on s.id=p.supplier_id
where s.user_id=2 and p.creator_id=1 and s.archived='False'
group by p.supplier_id
order by count_suppliers desc

关于mysql - 如何首先获取订单最多的供应商列表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/47993500/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com