主页 / dba / 问题 / 81065
Accepted
ajeh
Asked: 2014-10-25 10:46:56 +0800 CST

将子选择重写为使用“over (partition by ...)”生成的列的条件

  • 772

我想知道是否可以使用over (partition by...)子句以避免在下面的示例中使用子选择：

declare @t table (
     id         int
    ,code       char(2)
    ,descriptor int
)

insert into @t
      select 1, 'a1', 10
union select 1, 'a1', 20
union select 1, 'a1', 30
union select 2, 'b1', 10
union select 2, 'b1', 20
union select 2, 'b1', 30
union select 2, 'b2', 10
union select 2, 'b2', 20
union select 2, 'b2', 30
union select 3, 'c4', 10
union select 3, 'c4', 20
union select 3, 'c4', 30
union select 3, 'c7', 10
union select 3, 'c7', 20
union select 3, 'c7', 30


select *
from @t as t
where code = (select min(code) from @t where id = t.id)

我只想查看code等于a1和b1的记录c4。可以over (partition by...)将 1 分配给所有这些，将 2 分配给代码b2等c7，这样最后我可以说where row_number = 1而不是使用子查询？

sql-server sql-server-2008-r2

4 个回答

  1. 是的，但不是row_number()。您可以使用窗口聚合min()：

    select id, code, descriptor
from 
  ( select id, code, descriptor, 
           min_code = min(code) over (partition by id)
    from @t
  ) as t
where code = min_code ;

    或rank()窗口函数（或其中dense_rank()一个，它们在rnk = 1检查中的工作方式相同）：

    select id, code, descriptor
from 
  ( select id, code, descriptor, 
           rnk = rank() over (partition by id order by code)
    from @t
  ) as t
where rnk = 1 ;

    我正要评论说，如果没有任何类型的子查询（相关的、派生的表、ctes），就没有办法做到这一点，但唉，有。不是在 2008R2 中，而是在 2012 版本中使用了更多的窗口函数。

    注意：不要使用这个。效率可能会更差。以下查询可能适用于混淆竞赛，但不适用于生产。

    相反，当有合适的索引时，检查上述第二个查询的好执行计划。

    只是为了好玩 - 以及对代码未来维护者的恐惧：

    select distinct
    id, 
    code = min(code) over (partition by id),
    descriptor = 
        case when code = min(code) over (partition by id)
            then descriptor
            else first_value(descriptor) over 
                    (partition by id order by code, descriptor)
        end
from @t ;

    在SQLFiddle测试

    • 9
  2. Best Answer

    另一种方式（满足要求并与 2005+ 兼容，但可能永远不会在实践中使用）是

    SELECT TOP 1 WITH ties id,
                       code,
                       descriptor
FROM   t
WHERE  id IS NOT NULL
       AND code IS NOT NULL
ORDER  BY RANK()
            OVER (PARTITION BY id
                      ORDER BY code);

    虽然以这种方式使用有趣的答案（ab）TOP不如简单地使用某种形式的派生表并在其中进行过滤WHERE。

    无论如何，如果不将其包装在表表达式中，您将无法对该查询的结果应用任何额外的排序。因为电流ORDER BY必须保持不变以避免改变结果。

    SQL小提琴

    • 7

  3. 当然，您也可以使用 CTE 编写此代码：

    ;WITH t AS
(
  SELECT id, code, descriptor, 
    min_code = MIN(code) OVER (PARTITION BY id)
  FROM @t
)
SELECT id, code, descriptor 
FROM t WHERE code = min_code;

    你不能说：

    SELECT id, code, descriptor
FROM @t 
WHERE code = MIN(code) OVER (PARTITION BY id);

    因为：

    消息 4108，级别 15，状态 1，第 33 行
    窗口函数只能出现在 SELECT 或 ORDER BY 子句中。

    完全不清楚这一点：

    这个想法是不使用子查询。

    但是，也许谁盲目地拒绝子查询的想法，就不会明智地认识到 CTE 只是不同服装的子查询。（事实上​​很多人都没有认出 CTE，这有时既是福也是祸。）

    否则，请重新开始，并解释为什么需要完全删除子查询。对于某些问题（如应用窗口函数），除了在不同的范围内过滤它们之外别无选择——这意味着子查询、派生表、CTE 等......所有子查询的风格。

    • 6 
  4. select m.id, min(m.code) as code, m.descriptor
from @t as m
group by m.id, m.descriptor
order by m.id, code

    order by仅用于以与您的示例相同的顺序生成输出表，但这完全没有必要。

    修订以包括我在下面添加的评论 - 处理重复项：

    select a.id, a.code, b.descriptor
from
(
select id, min(code) as code
from
(
select id, code
from @t
group by id,code
) as a
group by id
) as a
inner join
@t as b
on b.id = a.id and a.code = b.code

    没有子选择，没有 SQL 2012 的特殊功能。只是 SQL。可以办到。

    • -8

相关问题