sql server语言 - sql:如何在多次查询中选择一些(但不是全部)记录

Question

我们有一堆记录，我们按以下方式为每个值在1和记录总数之间的记录分配一个随机数:

SELECT personID, ROW_NUMBER()
OVER(ORDER BY NEWID()) as RowNumber
FROM folks

Easy like pie。让我们假设一个较低(编辑:不是更高，抱歉!)的数字更适合客户的目的，并且他们喜欢这里的"随机"元素的工作方式。问题是，顾客现在说:"有些人很特别，我们希望他们有三次机会，然后把最好的结果作为他们的号码。"'

由于我们不按顺序分发数字，而是一次性分发，因此这里的方法似乎是在此查询中选择特定人员三次，然后获取他们的最高行号。

这个类似于，但比这个问题(以及其他类似的问题)更复杂一步:

从表

中选择多次记录

我不想选择所有记录三次;但我确实想把所有的事情都一次性做完;也就是说，我不能给特殊的人分配号码，然后给其他人分配号码——它必须是一个查询。

1. 我如何构建一个JOIN(和/或CTE)来建模，假设我们可以依赖于每个记录上的isSpecial = 1这样的字段?

2. 然后我如何从我的SELECT声明的结果中获取"最低数字"(即该记录的第一个row_number出现)?

平台:Microsoft SQL 2012

SAMPLE DATA(为了演示，在输出查询中包括isSpecial)—此外，出于业务目的，我们希望这里的最小值，而不是最大值

personID    isSpecial
1           1
2           0
3           0
4           0
5           0
6           0
7           0
8           0
9           0
10          0

当前输出:

SELECT personID, isSpecial, row_number
OVER(ORDER BY NEWID()) as RowNumber
FROM folks
personID    RowNumber    isSpecial
8           1            0
2           2            0
10          3            0
1           4            1
9           5            0
3           6            0
4           7            0
6           8            0
5           9            0
7           10            0

所需输出:

personID    MinRowNumber isSpecial rowNumber1 rowNumber2 rowNumber3
8           1            0         1
2           2            0         2
1           3            1         4          7          3
9           5            0         5
3           6            0         6
6           8            0         8
5           9            0         9
7           10           0         10
4           11           0         11         
10          12           0         12

Answer 1

您可以使用一个统计表和一些聚合来做到这一点。

WITH
cteTally(N) AS (select n from (values (1),(2),(3))dt(n))
select personID
    , MAX(RowNumber)
from 
(
    SELECT personID
        , ROW_NUMBER() OVER(ORDER BY NEWID()) as RowNumber
    FROM folks f
    join cteTally t on t.N <= case when f.IsSpecial = 1 then 3 else 1 end
) x
group by x.personID

——编辑——

你说你可能想要所有的行，而不仅仅是最大的一个。你可以这样做:

WITH
cteTally(N) AS (select n from (values (1),(2),(3))dt(n))
SELECT personID
    , ROW_NUMBER() OVER(ORDER BY NEWID()) as RowNumber
FROM folks f
join cteTally t on t.N <= case when f.IsSpecial = 1 then 3 else 1 end

Answer 2

我认为您可以使用UNION方法，但只应用NEWID()一次:

create table folks (personID int, isSpecial int)
insert into folks values (1,1);
insert into folks values (2,0);
insert into folks values (3,0);
insert into folks values (4,0);
insert into folks values (5,0);
insert into folks values (6,0);
insert into folks values (7,0);
insert into folks values (8,0);
insert into folks values (9,0);
insert into folks values (10,0);
select * from folks;
select
   personID,
   min(rownumber) as min_rownumber
from
  (SELECT 
      personID, 
      ROW_NUMBER() OVER(ORDER BY NEWID()) as RowNumber
   FROM 
      (select personID from folks 
      union all
      select personID from folks where isSpecial = 1 
      union all
      select personID from folks where isSpecial = 1) u
   ) r
group by
   personID

SQLFiddle

Answer 3

解决这个任务的正确方法是:

让我们有O个普通人加上S个特殊的人。每个普通人有一次机会，每个特别的人有三次机会。我们应该生成O + S * 3随机数，均匀分布在[1 .. O+S*3]的范围内，然后根据得到的随机数对所有人排序。特殊人物会在这个有序的列表中出现3次，普通人只会出现一次。

下面是执行此操作的查询。在我的第一个变体中显示了用于创建带有示例数据的表的代码。CTE_Numbers只是一个有三个数字的表。如果您希望为特定的人提供不同数量的机会，请更改此查询。CTE列出所有普通人一次，所有特殊人物三次。CTE_rn为每一行分配一个随机数。每个特殊的人得到三个随机数字。由于每个特殊人物在CTE_rn中有三行，因此最后的查询按PersonID分组，为每个特殊人物留下最小数量的一行。为了更好地理解它是如何工作的，检查CTE_rn的中间结果。

WITH
CTE_Numbers
AS
(
    SELECT Number
    FROM (VALUES (1),(2),(3)) AS N(Number)
)
,CTE
AS
(
    -- list ordinary people only once
    SELECT PersonID,IsSpecial
    FROM @T
    WHERE IsSpecial = 0
    UNION ALL
    -- list each special person three times
    SELECT PersonID,IsSpecial
    FROM @T CROSS JOIN CTE_Numbers
    WHERE IsSpecial = 1
)
,CTE_rn
AS
(
    SELECT
        PersonID,IsSpecial
        ,ROW_NUMBER() OVER(ORDER BY CRYPT_GEN_RANDOM(4)) AS rn
    FROM CTE
)
SELECT
    PersonID,IsSpecial
    ,MIN(rn) AS FinalRank
FROM CTE_rn
GROUP BY PersonID,IsSpecial
ORDER BY FinalRank;

结果

PersonID    IsSpecial    FinalRank
9           0            1
2           0            2
1           1            3
10          0            4
8           0            5
5           0            6
3           0            7
7           0            9
4           0            10
6           0            12

注意，FinalRank的值从1到12(不是10)，值8和11没有显示。那个特别的人拥有它们。特殊的人得到随机数字3、8、11，最后的结果只包含这三个数字中的最小值。

---

第一个变体。它是有效的，但结果是扭曲的。

非常简单。生成三次随机行数，将它们连接在一起，对于普通人选择第一个随机数的结果，对于特殊人群选择三次中的最小值。

没有人承诺NEWID有任何特定的随机数分布，所以你最好不要在这种情况下使用它。在这个例子中，我使用CRYPT_GEN_RANDOM。

我将相同的查询放在三个单独的CTE中以获取随机数，而不是在连接中使用相同的CTE，以确保计算三次。如果使用单个CTE，服务器可能足够聪明，只计算一次随机数，而不是三次，这不是我们这里需要的。我们需要30次呼叫CRYPT_GEN_RANDOM。

DECLARE @T TABLE (PersonID int, IsSpecial bit);
INSERT INTO @T(PersonID, IsSpecial) VALUES
(1 , 1),
(2 , 0),
(3 , 0),
(4 , 0),
(5 , 0),
(6 , 0),
(7 , 0),
(8 , 0),
(9 , 0),
(10, 0);
WITH
CTE1
AS
(
    SELECT PersonID, IsSpecial,
        ROW_NUMBER() OVER(ORDER BY CRYPT_GEN_RANDOM(4)) AS rn
    FROM @T
)
,CTE2
AS
(
    SELECT PersonID, IsSpecial,
        ROW_NUMBER() OVER(ORDER BY CRYPT_GEN_RANDOM(4)) AS rn
    FROM @T
)
,CTE3
AS
(
    SELECT PersonID, IsSpecial,
        ROW_NUMBER() OVER(ORDER BY CRYPT_GEN_RANDOM(4)) AS rn
    FROM @T
)
,CTE_All
AS
(
SELECT
    CTE1.PersonID
    ,CTE1.IsSpecial
    ,CTE1.rn AS rn1
    ,CTE2.rn AS rn2
    ,CTE3.rn AS rn3
    ,CA.MinRN
FROM
    CTE1
    INNER JOIN CTE2 ON CTE2.PersonID = CTE1.PersonID
    INNER JOIN CTE3 ON CTE3.PersonID = CTE1.PersonID
    CROSS APPLY
    (
        SELECT MIN(A.rn) AS MinRN
        FROM (VALUES (CTE1.rn), (CTE2.rn), (CTE3.rn)) AS A(rn)
    ) AS CA
)
SELECT
    PersonID
    ,IsSpecial
    ,CASE WHEN IsSpecial = 0 
    THEN rn1 -- a person is not special, he gets random rank from the first run only
    ELSE MinRN -- a special person, he gets a rank that is minimum of three runs
    END AS FinalRank
    ,rn1
    ,rn2
    ,rn3
    ,MinRN
FROM CTE_All
ORDER BY FinalRank;

结果集

PersonID    IsSpecial    FinalRank    rn1    rn2    rn3    MinRN
8           0            1            1      1      1      1
6           0            2            2      7      2      2
5           0            3            3      5      6      3
1           1            3            9      3      4      3
4           0            4            4      6      3      3
7           0            5            5      9      10     5
3           0            6            6      8      9      6
2           0            7            7      2      8      2
10          0            8            8      10     5      5
9           0            10           10     4      7      4

你可以看到，特殊的人可以(偶然地)得到和普通人一样的地位。在这种情况下，你可以进一步偏袒特殊人士，确保他们出现在普通人面前。将ORDER BY改为ORDER BY FinalRank, IsSpecial DESC

Answer 4

如何使用UNION?

SELECT personID, ROW_NUMBER()
    OVER(ORDER BY NEWID()) as RowNumber
FROM folks
WHERE isSpecial = 0
UNION ALL
SELECT personID, MAX(RN)
FROM (    
    SELECT personID, ROW_NUMBER() AS 'RN'
        OVER(ORDER BY NEWID()) as RowNumber
    FROM folks
    WHERE isSpecial = 1
    UNION ALL
    SELECT personID, ROW_NUMBER()
        OVER(ORDER BY NEWID()) as RowNumber
    FROM folks
    WHERE isSpecial = 1
    UNION ALL
    SELECT personID, ROW_NUMBER()
        OVER(ORDER BY NEWID()) as RowNumber
    FROM folks
    WHERE isSpecial = 1    
    )
GROUP BY personID