left join 改写标量子查询

数据库环境：SQL SERVER 2005

　　有一博彩的赔率是1:20，它有2张业务表：smuchs（投注表），lottery（开奖表）。

smuchs表有3个字段，分别是sno（投注号码）、smuch（投注金额），stime（投注时间），

lottery表有2个字段，分别是lno（开奖号码）、stime（开奖时间）。smuchs表和lottery表的数据如下：

     

　　要求：根据每天的投注情况和开奖号码，统计指定日期的投注金额、中奖应支付金额、盈亏金额。

　　1.建表，导入模拟数据

CREATE TABLE smuchs (sno INT,smuch INT,stime DATETIME)
INSERT into smuchs values(23,100,'2015-09-01 09:10:11');
INSERT into smuchs values(02,2,'2015-09-01 12:23:28');
INSERT into smuchs values(18,4,'2015-09-01 14:02:34');
INSERT into smuchs values(32,60,'2015-09-01 14:10:58');
INSERT into smuchs values(10,26,'2015-09-02 10:57:24');
INSERT into smuchs values(27,12,'2015-09-02 15:42:34');
INSERT into smuchs values(03,14,'2015-09-02 16:12:58');
INSERT into smuchs values(19,18,'2015-09-03 17:23:24');
INSERT into smuchs values(14,30,'2015-09-03 17:25:12');
INSERT into smuchs values(02,90,'2015-09-01 18:02:34');
CREATE TABLE lottery(lno INT,ltime DATETIME)
INSERT INTO lottery VALUES(18,'2015-09-01 21:00:00')
INSERT INTO lottery VALUES(09,'2015-09-02 21:00:00')
INSERT INTO lottery VALUES(14,'2015-09-03 21:00:00')

　　2.标量实现

SELECT  stime ,
        smuch ,
        ISNULL(lmuch, 0) AS lmuch ,
        smuch - ISNULL(lmuch, 0) slmuch
FROM    ( SELECT    CONVERT(VARCHAR(10), sh.stime, 121) AS stime ,
                    SUM(sh.smuch) AS smuch ,
                    20
                    * ( SELECT  SUM(smuch)
                        FROM    smuchs
                                INNER JOIN lottery ON lottery.lno = smuchs.sno
                                                      AND CONVERT(VARCHAR(10), lottery.ltime, 121) = CONVERT(VARCHAR(10), smuchs.stime, 121)
                                                      AND CONVERT(VARCHAR(10), sh.stime, 121) = CONVERT(VARCHAR(10), smuchs.stime, 121)
                      ) AS lmuch
          FROM      smuchs sh
          GROUP BY  CONVERT(VARCHAR(10), sh.stime, 121)
        ) t

　　

　　这是某网友的实现方法，我们可以看到，smuchs表被扫描了4次，lottery表被访问了3次。通过

查看执行计划，发现外部表和子查询部分走的是嵌套循环。如果数据很多，这个SQL的查询速度会比较慢。

　　3.left join实现

/*合计每个号码的投注金额*/
WITH    x0
          AS ( SELECT   sh.sno ,
                        SUM(sh.smuch) AS smuch ,
                        CONVERT(VARCHAR(10), sh.stime, 121) AS stime
               FROM     smuchs sh
               GROUP BY sno ,
                        CONVERT(VARCHAR(10), sh.stime, 121)
             ),
        x1
          AS ( SELECT   sh.sno ,
                        sh.smuch ,
                        sh.stime ,
                        ly.lno
               FROM     x0 sh
                        LEFT JOIN lottery ly ON ly.lno = sh.sno
                                                AND CONVERT(VARCHAR(10), ly.ltime, 121) = sh.stime
             ),
        x2
          AS ( SELECT   stime ,
                        SUM(smuch) AS smuch ,--统计所有投注金额
                        20 * SUM(CASE WHEN lno IS NOT NULL THEN smuch
                                 END) AS lmuch--统计中奖应付金额
               FROM     x1
               GROUP BY stime
             )
    SELECT  stime ,
            smuch ,
            ISNULL(lmuch, 0) AS lmuch ,
            smuch - ISNULL(lmuch, 0) slmuch
    FROM    x2

　　通过left join改写，lottery表和smuchs表均只被扫描1次。

　　统计的结果如图：

（本文完）