代码之家 › 专栏 › 技术社区 › eugeneK

如何使用params从中选择top x?

sql-server sql

eugeneK · 技术社区 · 15 年前

我正在使用SQLServer2005

我有一个带有userID和gender的Users表。我想按userID desc选择前1000名男性(0)和前1000名女性(1)。

如果我创建union,那么只有一个结果集是按userID desc排序的。还有什么方法可以这样做呢?

SELECT top 1000 *
FROM Users
where gender=0
union
SELECT top 1000 *
FROM Users
where gender=1
order by userID desc

4 回复 | 直到 15 年前

Martin Smith 15 年前

另一种方法

WITH TopUsers AS
(
SELECT UserId, 
       Gender,
       ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY UserId DESC) AS RN
  FROM Users
  WHERE Gender IN (0,1) /*I guess this line might well not be needed*/
) 

SELECT UserId, Gender 
FROM TopUsers  
WHERE RN <= 1000
ORDER BY UserId DESC

Chris Diver 15 年前

马丁·史密斯的解决方案比下面的好。

SELECT UserID, Gender
FROM 
  (SELECT TOP 1000 UserId, Gender 
   FROM Users 
   WHERE gender = 0
   ORDER BY UserId DESC) m
UNION ALL
SELECT UserID, Gender
FROM 
 (SELECT TOP 1000 UserId, Gender
  FROM Users
  WHERE gender = 1
  ORDER BY UserId DESC) f
ORDER BY Gender, UserID DESC

Andomar 15 年前

做了一些测试,结果很奇怪。如果指定 order by 在联合体的两个部分中,SQL Server都会出现语法错误:

select top 2 * from @users where gender = 0 order by id
union all
select top 2 * from @users where gender = 1 order by id

这是有道理的,因为order by应该只在联盟的末尾。但是如果在子查询中使用相同的构造,它会编译!并按预期工作:

select * from (
    select top 2 * from @users where gender = 0 order by id
    union all
    select top 2 * from @users where gender = 1 order by id
) sub

最奇怪的事情发生在你只指定了一个 订货人 对于子查询联合:

select * from (
    select top 2 * from @users where gender = 0
    union all
    select top 2 * from @users where gender = 1 order by id
) sub

现在它随意点了工会的前半部分,但下半部分是按id点的。这真是出乎意料。同样的事情也发生在 订货人

select * from (
    select top 2 * from @users where gender = 0 order by id desc
    union all
    select top 2 * from @users where gender = 1
) sub

我希望这会给出一个语法错误,但是相反,它命令联合的前半部分。所以看起来 union 订货人 是子查询的一部分。

就像Chris Diver最初发布的那样,摆脱困惑的一个好方法就是不要依赖在联合中,并显式指定所有内容:

select  *
from    (
        select  *
        from    (
                select  top 2 *
                from    @users
                where   gender = 0
                order by 
                        id desc
                ) males
        union all
        select  *
        from    (
                select  top 2 *
                from    @users
                where   gender = 1
                order by 
                        id desc
                ) females
        ) males_and_females
order by 
        id

示例数据:

declare @users table (id int identity, name varchar(50), gender bit)

insert into @users (name, gender)
          select 'Joe', 0
union all select 'Alex', 0
union all select 'Fred', 0
union all select 'Catherine', 1
union all select 'Diana', 1
union all select 'Esther', 1

Tom 15 年前

这样的方法应该有用:

SELECT u.*
  FROM (SELECT u1a.* FROM (SELECT TOP 1000 u1.*
                             FROM USERS u1
                            WHERE u1.gender = 0
                            ORDER BY u1.userid DESC) u1a
        UNION ALL
        SELECT u2a.* FROM (SELECT TOP 1000 u2.*
                             FROM USERS u2
                            WHERE u2.gender = 1
                            ORDER BY u2.userid DESC) u2a
       ) u
ORDER BY u.userid DESC

另外,使用UNION ALL将提供更好的性能,因为db不会费心检查结果中的重复项(在这个查询中不会有重复项)。