你的位置:首页 > 数据库

[数据库]left join 改写标量子查询


数据库环境:SQL SERVER 2005

  有一博彩的赔率是1:20,它有2张业务表:smuchs(投注表),lottery(开奖表)。

smuchs表有3个字段,分别是sno(投注号码)、smuch(投注金额),stime(投注时间),

lottery表有2个字段,分别是lno(开奖号码)、stime(开奖时间)。smuchs表和lottery表的数据如下:

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')

View Code

  2.标量实现

SELECT stime ,    smuch ,    ISNULL(lmuch, 0) AS lmuch ,    smuch - ISNULL(lmuch, 0) slmuchFROM  ( 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

View Code

  

  这是某网友的实现方法,我们可以看到,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

View Code

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

  统计的结果如图:

(本文完)