如何根据标准选择包含记录的TOP X

Question

我有一个表，有多列，包括一个名为"PolicyNumber"的列

这是一个示例：

PolicyNumber
NYH1111
NYD2222
SCH3333
SCS4444
LUH5555
LUS6666
ALH7777
ALW8888
VAH9999
AKH0000
...
NYH1010
NYD2318

此表中有1000多条记录，每条记录都包含几种策略编号类型。例如，以"NYH"开头的多个策略或以"VAH"开头多个策略。

可能的策略类型如下：

NYH
NYD
SCH
SCS
LUH
LUS
ALH
ALW
VAH
AKH

如何进行SELECT TOP 300，其中至少包含每种策略类型中的一种？请记住，策略类型是策略编号的前3个字母。

这可能吗？这样做的目的是，我必须从生产中获取300条记录以转储到测试环境中，并且每个策略至少需要包含1条。在我每种至少有一种之后，它可以完全随机。

Answer 1

你可以试试这个：

在这个解决方案中，首先有newid()，您可以通过每次运行生成随机顺序

为了实现"每个策略至少有一个"的目标，我制作了AtLeastOne专栏。这将从随机化的CTE表中为开头的每个唯一的三个字母选择第一个。如果当前的Policy等于第一个选定的值，则它得到1，否则为0。因此，使用此逻辑，您可以从每个唯一的三个字母中选择一个随机化的第一个字母

注意：如果只需要Policy字段，也可以将此逻辑直接放入Order By部分。（我用这种方式做了一个例子，使其背后的逻辑可见）

在最后一步中，您只需要按AtLeastOne Des c排序，然后按随机ID排序。

WITH CTE_Policy
AS
(
  SELECT newid() as  ID, Policy
  FROM Code
)
SELECT TOP 300
Policy,
CASE WHEN Policy = (SELECT TOP 1 Policy FROM cte_Policy c
                    WHERE SUBSTRING(c.Policy,1,3) = 
                      SUBSTRING(CTE_Policy.Policy,1,3))
THEN 1 ELSE 0 END  AS AtLeastOne
FROM CTE_Policy
ORDER BY AtLeastOne DESC, ID

这是一个SQLFiddle演示。

Answer 2

在我的脑海中，你可以做：

SELECT TOP 30 Column1, Column2, Column3, PolicyNumber
FROM YourTable
WHERE PolicyNumber LIKE 'NYH%'
UNION 
SELECT TOP 30 Column1, Column2, Column3, PolicyNumber
FROM YourTable
WHERE PolicyNumber LIKE 'NYD%'
UNION
/* ... remaining eight policy types go here */ 
ORDER BY PolicyNumber /* Or whatever sort order you want */

它每次都会给你30种类型，而不是一种类型的X和另一种类型。

Answer 3

我想到的一个快速方法。。以下查询将只获取每个策略类型的1条记录

 SELECT TOP 300 *
 FROM   ( SELECT *,rank1= ROW_NUMBER () OVER (PARTITION BY LEFT (PolicyNo,3) ORDER BY GETDATE ()) FROM MyTable
        ) AS t1
 WHERE  t1.rank1 = 1

Answer 4

在SQL Server 2005+中尝试此操作：

;WITH CTE AS
(
    SELECT  LEFT(PolicyNumber) PolicyType, PolicyNumber, 
            ROW_NUMBER() OVER(PARTITION BY LEFT(PolicyNumber) ORDER BY NEWID()) RN
    FROM YourTable
)
SELECT TOP 300 PolicyNumber
FROM CTE
ORDER BY RN, NEWID()

Answer 5

从ClearLogic+1借用请检查ClearLogic是否有效

WHERE t1.rank1=1的问题是，如果少于300个唯一，它将停止在300以下

 SELECT TOP 300 t1.PolicyNo
 FROM   ( SELECT PolicyNo, rank1= ROW_NUMBER () 
          OVER (PARTITION BY LEFT (PolicyNo,3) ORDER BY NEWID()) 
          FROM MyTable
        ) AS t1
 order by t1.rank, t1.PolicyNo