联合两个按newid排序的查询

Question

我有一个存储员工(id、姓名和性别)的表。我需要随机抽取两男两女。

CREATE TABLE employees 
(
id INT, 
name VARCHAR (10), 
gender VARCHAR (1), 
);
INSERT INTO employees VALUES (1, 'Mary', 'F');
INSERT INTO employees VALUES (2, 'Jake', 'M');
INSERT INTO employees VALUES (3, 'Ryan', 'M');
INSERT INTO employees VALUES (4, 'Lola', 'F');
INSERT INTO employees VALUES (5, 'Dina', 'F');
INSERT INTO employees VALUES (6, 'Paul', 'M');
INSERT INTO employees VALUES (7, 'Tina', 'F');
INSERT INTO employees VALUES (8, 'John', 'M');

我的尝试如下:

SELECT TOP 2 * 
FROM employees
WHERE gender = 'F'
ORDER BY NEWID()
UNION
SELECT TOP 2 * 
FROM employees
WHERE gender = 'M'
ORDER BY NEWID()

但是它不起作用，因为我不能在同一个查询中放置两个order by。

Answer 1

为什么不用row_number()呢?一个没有子查询的方法是:

SELECT TOP (4) WITH TIES e.* 
FROM employees
WHERE gender IN ('M', 'F')
ORDER BY ROW_NUMBER() OVER (PARTITION BY gender ORDER BY newid());

这比在子查询中使用ROW_NUMBER()稍微低一些。

或者，一个有趣的方法是使用APPLY:

select e.*
from (values ('M'), ('F')) v(gender) cross apply
(select top (2) e.*
from employees e
where e.gender = v.gender
order by newid()
) e;

Answer 2

不能在UNION的可组合查询(第一个)中放置ORDER BY。但是，如果您将每个表表达式转换为表表达式，则可以使用ORDER BY。

例如:

select *
from (
SELECT TOP 2 * 
FROM employees
WHERE gender = 'F'
ORDER BY newid()
) x
UNION ALL
select *
from (
SELECT TOP 2 * 
FROM employees
WHERE gender = 'M'
ORDER BY newid()
) y

结果:

id  name  gender 
--- ----- ------ 
5   Dina  F      
4   Lola  F      
2   Jake  M      
3   Ryan  M      

参见SQL Fiddle的运行示例。