主页 / dba / 问题 / 253251
Accepted
Michael Cherevko
Asked: 2019-11-15 00:22:55 +0800 CST

使用 group by 和 window 函数减少表扫描

  • 772

我正在尝试改进的代码如下所示（简单示例）：

SELECT    DISTINCT a.col_a
         ,COALESCE(b1.col_c, b2.col_c, b3.col_c)
FROM      tab_a a
LEFT JOIN tab_b b1
          ON a.col_a = b1.col_a
             AND b1.col_b = 'blabla1'
LEFT JOIN tab_b b2
          ON a.col_a = b2.col_a
             AND b2.col_b = 'blabla2'
LEFT JOIN tab_b b3
          ON a.col_a = b3.col_a
             AND b3.col_b = 'blabla3';

您可以使用以下脚本重新创建这些表

CREATE TABLE tab_a(col_a int)
CREATE TABLE tab_b(col_a INT, col_b VARCHAR(10), col_c INT)

INSERT INTO dbo.tab_a ( col_a ) VALUES ( 1 ), ( 2 ), ( 3 );

INSERT INTO dbo.tab_b ( col_a
                       ,col_b
                       ,col_c )
VALUES ( 1, 'blabla1', 1 )
      ,( 1, 'blabla2', 3 )
      ,( 1, 'blabla2', 5 )
      ,( 2, 'blabla2', NULL )
      ,( 2, 'blabla3', 5 );

如何将其更改为 1 join + 也许是窗口函数以及如何重写合并部分。只是为了解释一下，当前计划显示 3 个 tab_b 扫描，我想将其减少到 1 个。

sql-server sql-server-2016

3 个回答

  1. Best Answer
    SELECT    DISTINCT a.col_a
         ,b.col_c
FROM      tab_a a
outer apply (select top 1 b.col_c 
             from tab_b b
             where ((a.col_a = b.col_a
                     AND b.col_b = 'blabla1' )
                  or (a.col_a = b.col_a
                     AND b.col_b = 'blabla2')
                  or (a.col_a = b.col_a
                     AND b.col_b = 'blabla3'))
                  and b.col_c is not null
            order by b.col_b)b;

    此解决方案有 1 个 tab_bscan但添加sort是因为您想b.col_c在COALESCE. 在上面的示例中，这order对应于order您的constantsinjoin condition对应于c列的值。如果顺序应该不同，事情会更复杂，因为你应该编写自定义order by子句。

    • 5

  2. 如果您希望或拥有这 3 个描述静态，您可以执行以下操作：

    1. 创建临时表或变量表

    2. 在临时表或变量表中插入值，但以列的形式 https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server

    3. 在您的答案中编写脚本，但删除那些 3 LEFT JOIN，然后对临时表或变量表执行 CROSS JOIN

    4. 更改 COALESCE 代码以从 CROSS JOIN 对象中获取值

    您还可以更改数据库模型以满足您最需要的内容。

    为了提供帮助，请分享当前的数据库模型，以便我们进一步帮助您。您可以使用以下选项创建它：

    在此处输入图像描述

    • 1 
  3. SELECT    DISTINCT a.col_a
         ,b1.col_c
FROM      tab_a a
LEFT JOIN tab_b b1
          ON a.col_a = b1.col_a
             AND b1.col_b IN ('blabla1','blabla2','blabla3');

    1 按要求加入 ^^ 使用 IN (..) 编辑为更优雅的风格

    • 0

相关问题