数据库环境：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) 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
这是某网友的实现方法，我们可以看到，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次。
统计的结果如图：
（本文完）