代码之家 › 专栏 › 技术社区 › Sören Kuklau Keith Boynton

平衡受前100名限制的不同类型的结果记录

sql-server-2008 tsql sql

Sören Kuklau Keith Boynton · 技术社区 · 15 年前

给一张桌子 Records 用柱子 id int , Type int 和 Name varchar(50)

SELECT id, Type, Name
FROM Records
WHERE Name LIKE '%Foo%'

为了稍微调整性能,我只想给出有限的结果;目前仅通过添加 TOP 100 我同意这项声明。但是,这可能会导致某些类型的记录表示不足或根本不表示,如以下查询所示:

SELECT Type, COUNT(Type) FROM
    (SELECT id, Type, Name
    FROM Records
    WHERE Name LIKE '%Foo%') x
GROUP BY Type
ORDER BY Type

没有 前100名 ,我可能会得到:

有了它:

这可能导致用户得出结论,没有类型为的记录 90 存在。

我更喜欢 TOP 表现不同:至少给我一个结果任何键入有一些的,然后继续添加到其中,直到达到计数。例如。, 42 76 和 87 结果会更少,但是我会出现的。

理想情况下,我还希望为用户提供一个“x更多此类结果”的UI元素。

我必须放弃吗 顶部 为了达到这个目的?

3 回复 | 直到 15 年前

Shannon Severance 15 年前

WITH RecordsWithRn AS (
SELECT id, Type, Name,
   ROW_NUMBER() OVER (PARTITION BY Type ORDER BY ... intra-type ordering ...) as rn
        FROM Records
        WHERE Name LIKE '%Foo%') 
SELECT TOP 100 id, Type, Name
   FROM RecordsWithRn
   ORDER BY RN, ... inter-type ordering ...

这会给你一百张唱片。假设少于100种类型,每种类型至少有一种。使用ROW_NUMBER()的ORDER BY来控制类型中记录的ROW_编号顺序。最后的ORDER BY,ORDER BY先前指定的行号,然后添加其他条件(如果愿意),以控制每个行号处类型之间的记录顺序。

要获取未显示类型的记录数,请执行以下操作:

WITH RecordsWithRn AS (
   SELECT id, Type, Name,
      ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Type) as rn,
      COUNT(*) OVER (PARTITION BY Type) as CountType
   FROM Records
   WHERE Name LIKE '%Foo%')
, Top100Records as (
   SELECT TOP 100 id, Type, Name, CountType
   FROM RecordsWithRn
   ORDER BY RN)
select Id, Type, Name, 
    CountType - (COUNT(*) over (PARTITION BY Type)) as CountTypeNotIncluded
from Top100Records

Sören Kuklau Keith Boynton 15 年前

WITH cte AS (
SELECT id, Type, Name,
   ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Type) as rn
        FROM Records
        WHERE Name LIKE '%Foo%') 
SELECT *
   FROM cte
   WHERE rn <= 100;

每种类型最多可获得100条记录。请注意,这并不意味着查询速度会更快,事实上它可能会更慢。它取决于可用的索引、表中的数据以及客户端处理结果的速度。

WITH totals AS (
    SELECT Type, COUNT(*) AS count
            FROM Records
            WHERE Name LIKE '%Foo%'
     GROUP BY Type) 
SELECT * FROM totals;

然后将两个结果合并:

WITH cte AS (
    SELECT id, Type, Name,
       ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Type) as rn
            FROM Records
            WHERE Name LIKE '%Foo%')
, totals AS (
        SELECT Type, COUNT(*) AS count
                FROM Records
                WHERE Name LIKE '%Foo%'
         GROUP BY Type) 
    SELECT *
       FROM cte c 
       JOIN totals t on c.Type = t.Type
       WHERE c.rn <= 100;

Michael Todd 15 年前

你可以打两个电话。第一个是[type]和count([type])。这将提供一个独特的 全部的 可用类型以及它们的计数,以向用户提供更多信息。之后,根据用户的请求按类型进行调用。