SQL难题-没有UNION的UNION ALL

Question

下面是要挑战您的SQL难题：

编写一个查询，该查询将选择三个不同类别中的前5条记录。

就像这样：

select top 5 name, age from table1 where category = 22 order by age desc
union all
select top 5 name, age from table1 where category = 27 order by age desc
union all
select top 5 name, age from table1 where category = 53 order by age desc

但是使用UNION或UNION进行而不使用

如果您正在使用特定于供应商的SQL扩展，请指定所使用的数据库。

Answer 1

经典的top-n-per-group，不是吗？

使用Server语法。ROW_NUMBER()应该在2015年出现在所有合适的数据库中。

WITH
CTE
AS
(
    select 
        name
        ,age
        ,ROW_NUMBER() OVER (PARTITION BY category ORDER BY age desc) AS rn
    from table1
    where category IN (22, 27, 53)
)
SELECT
    name
    ,age
FROM CTE
WHERE rn <= 5
;

从某种意义上说，UNION和OR是一样的。

如果您的表具有主键ID，则可以如下所示重写查询：

SELECT name, age
FROM table1
WHERE
    ID IN (select top 5 ID from table1 where category = 22 order by age desc)
    OR
    ID IN (select top 5 ID from table1 where category = 27 order by age desc)
    OR
    ID IN (select top 5 ID from table1 where category = 53 order by age desc)

不过，通常情况下，UNION ALL会比这更有效。

Answer 2

如果你真的想要前五，那么你可能需要一个平局。name每age唯一吗？

select t.name, t.age from T t
where t.category in (22, 27, 53) and 5 >= (
    select count(*) from T t2
    where t2.category = t.category
        and (t2.age >= t.age or t2.age = t.age and t2.name >= t.name)
)

Answer 3

如果您正在使用特定于供应商的SQL扩展，请指定所使用的数据库。

在 Server中，您可以使用排序函数ROW_NUMBER()和PARTITION BY category为每一组类别生成一个排名编号，并对正在寻找的三个类别进行筛选。然后，您可以筛选出只有排名小于5的行，这将为每个类别获得最老的5个名称：

SELECT name, age
FROM
(
    SELECT 
      name, age, 
      ROW_NUMBER() OVER(PARTITION BY category ORDER BY age DESC)AS RN
    FRO< table1 
    WHERE category IN(22,27, 53)
) AS t
WHERE RN <= 5;

• SQL Fiddle Demo