主页 / coding / 问题 / 79004221
Accepted
David542
Asked: 2024-09-20 03:51:23 +0800 CST

按发生次数计数

  • 772

我有一个类似的查询：

select * 
from combined 
where valid_rating is null
   order by FIND_IN_SET(provider, 
    (select provider 
     from combined 
     where valid_rating is null group by provider order by count(1) desc)
   )

基本上，我试图先按“最重要的”排序provider（项目数最多的）。我该如何使用order_by子句中的表达式正确地做到这一点，甚至可能使用窗口函数？

sql

2 个回答

  1. Best Answer

    使用count() 窗口函数，按提供者进行分区：

    select c.*, count(*) over (partition by provider) cnt
from combined c
where valid_rating is null
order by cnt desc
    • 2

  2. 与返回计数的子查询连接，然后按计数排序。

    SELECT c.*, ct.count
FROM combined AS c
JOIN (
    SELECT provider, COUNT(*) AS count
    FROM provider
    GROUP BY provider
) AS ct ON c.provider = ct.provider
ORDER BY count DESC
    • 0

相关问题