SQL:按多个限制分组

Question

我有一个简单的表，包含id、key、level、table和data，如下所示：

id key level
1   a   1
2   a   2
3   a   3
4   a   3
5   b   1
6   b   3
7   c   1
8   c   2
9   c   2

问题是:我传入一个id数组(数据库支持在操作中查找)，我希望只计算给定id列表中的最高级别，并按键分组。例如:我传入2,3，返回的数据应该是：

key toplevel count
a     3        1

我传入2,3,8,9，返回的数据应该是：

key toplevel count
a     3        1
c     2        2

我不知道我的问题是否描述得很清楚(英语不好)，如果你不明白请问我。

Answer 1

这将在PostgreSQL中工作。

Select * from(
Select 
    keyss, 
    levels, 
    counts, 
    ROW_NUMBER() OVER ( partition by keyss
            order by levels desc) as rownum 
from
(Select keyss, levels, count(*) as counts from test where id in (2,3,4,5) group by keyss, levels) as X
) as Y
Where Y.rownum = 1;

您可以在MySQL中模拟row_number，或者我认为在最新版本的MySql中应该会使用类似的函数。

Answer 2

我创建了与您类似的数据并进行了测试。这似乎对我很有效。请试试这个。

select a.`key`, a.level, count(*) as cnt 
from (select `key`, max(level) as toplevel from mytable group by `key`) b
     left join mytable a on a.`key` = b.`key` and a.level = b.toplevel and a.id in (2,3,8,9)
where a.`key` is not null
group by a.`key`, a.level

Answer 3

您将替换密钥中的keyValue。我的表名是dbo.Table_2，请测试一下。

    select a.keyValue,a.level,MAX(rownum) as count from(
       Select 
       keyValue, 
       level, 
       ROW_NUMBER() OVER ( partition by keyValue
        order by level desc  ) as rownum 
       from
       [dbo].[Table_2]) as a
       inner join 
       (select MAX(level) as level,keyValue from [dbo].[Table_2]
         group by   keyValue)  b 
         on (a.level=b.level)and(a.keyValue=b.keyValue)
     group by a.keyValue ,a.keyValue,a.level