sql - 列属性的条件组

Question

我有一个包含这些列的表:pk、gender、attribute，我想为每个性别选择前 10 个属性。目前我使用两个查询来完成:

SELECT attribute, count(attribute)
FROM tab
WHERE gender=1
GROUP BY attribute
ORDER BY count(attribute) DESC
LIMIT 10

和另一个查询 WHERE gender=0。

我可以对一个查询进行此操作，并输出每个性别的前 10 个属性吗

男性属性，计数，女性属性，计数

Answer 1

可以。最简单的方法是使用变量:

SELECT gender, attribute, cnt
FROM (SELECT ag.*,
             (@rn := if(@g = gender, @rn + 1,
                        if(@g := gender, 1, 1)
                       )
             ) as seqnum
      FROM (SELECT gender, attribute, count(attribute) as cnt
            FROM tab
            GROUP BY gender, attribute
            ORDER BY gender, cnt DESC
           ) ag CROSS JOIN
           (SELECT @rn := 0, @g := -1) params
     ) ag
WHERE seqnum <= 10;

有了两个性别值，单独的查询可能会更简单。但是，如果您有更多的值(value)，那么这种方法也有它的优点。

编辑:

Postgres 中的解决方案要简单得多:

select gender, attribute, cnt
from (select gender, attribute, count(*) as cnt,
             row_number() over (partition by gender order by count(*) desc) as seqnum
      from tab t
      group by gender, attribute
     ) ga
where seqnum <= 10;