如何获得与第一

  • Z时代
  • 2024-01-10
  • 分类：问答

关系如何获得与第一

Supplier大部分订单的供应商列表 - >一对多的关系与orders

| suppliers        | 
| ----------       | 
| id | name | archived (bool) | user_id | 
| orders      | 
| ----------------    | 
| id | supplier_id | creator_id |

期望

现在，我想名单对于给定的supplier.user_id和orders.creator_id以及那个supplier不应该是archived（请在问题最后找到预期结果）。

这里是我想：

  • 供应商ORDER BY最大（计数（orders.supplier_id））

  • 其中supplier.archived =假

  • 其中supplier.user_id = 2

  • 哪里orders.creator_id = 1

不成功Attampt

这是我的失败attampt，我不知道如何在此查询中添加供应商条件。

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    |

SOLUTION

所以最后参照Vamsi的答案，在这里你可以找到包括原始版本的SQL和ActiveRecord的（滑轨）此问题的解决方案版本：

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

回答：

可以join表和计数。

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

回答：

select top 1 supplier_id, COUNT(*) as count_suppliers 
from purchase_orders 
where orders.creator_id = 1 
And supplier.archived = false 
And supplier.user_id = 2 
group by purchase_orders.supplier_id 
order by COUNT(*) desc

以上是 如何获得与第一 的全部内容， 来源链接： utcz.com/qa/265541.html

回到顶部