请教个查询语句：按天递增统计

Question

表：d_sale
------------------------
sal_amt    sal_time
100          2015-07-03 12:15:18.153
200          2015-07-03 22:54:09.527
300          2015-07-04 09:15:01.527
400          2015-07-05 09:15:01.527
500          2015-07-06 12:15:01.527
600          2015-07-06 15:33:21.527
700          2015-07-09 15:33:21.527
查询条件：按时间 2015-07-01  到  2015-07-10 查询
查询结果：按天数递增结果如下：
------------------------
sal_amt    sal_time
0             2015-07-01
0             2015-07-02
300          2015-07-03
600          2015-07-04
1000        2015-07-05
2100        2015-07-06
2100        2015-07-07 
2100        2015-07-08
2800        2015-07-09

Answer 1

create table #d_sale
(
sal_amt int,
sal_time datetime
)
insert into #d_sale
select 100,'2015-07-03 12:15:18.153' union all
select 200,'2015-07-03 22:54:09.527' union all
select 300,'2015-07-04 09:15:01.527' union all
select 400,'2015-07-05 09:15:01.527' union all
select 500,'2015-07-06 12:15:01.527' union all
select 600,'2015-07-06 15:33:21.527' union all
select 700,'2015-07-09 15:33:21.527'select  isnull(sal_amt,0) sal_amt,
da sal_time
from 
(select
sal_amt,
convert(date,sal_time) as sal_time
from #d_sale) a
right join(
select 
convert(date,DATEADD(day,number,'2015-07-01'))  as da
FROM MASTER..SPT_VALUES 
WHERE TYPE='P' and number<10) b on a.sal_time=b.da
sal_amt     sal_time
----------- ----------
0           2015-07-01
0           2015-07-02
100         2015-07-03
200         2015-07-03
300         2015-07-04
400         2015-07-05
500         2015-07-06
600         2015-07-06
0           2015-07-07
0           2015-07-08
700         2015-07-09
0           2015-07-10(12 行受影响)

Answer 2

WITH /* 测试数据
d_sale(sal_amt,sal_time) AS (
    SELECT 100,'2015-07-03 12:15:18.153' UNION ALL
    SELECT 200,'2015-07-03 22:54:09.527' UNION ALL
    SELECT 300,'2015-07-04 09:15:01.527' UNION ALL
    SELECT 400,'2015-07-05 09:15:01.527' UNION ALL
    SELECT 500,'2015-07-06 12:15:01.527' UNION ALL
    SELECT 600,'2015-07-06 15:33:21.527' UNION ALL
    SELECT 700,'2015-07-09 15:33:21.527'
), */
c AS (--日历
    SELECT DATEADD(day,number,'2015-07-01') sal_time,
           number+1 rn
      FROM master..spt_values
     WHERE type = 'p'
       AND number < 10
)
,s AS ( --按日统计
    SELECT SUM(d.sal_amt) sal_amt,
           c.sal_time,
           c.rn
      FROM c
 LEFT JOIN d_sale d
        ON DATEDIFF(day, c.sal_time, d.sal_time) = 0
  GROUP BY c.sal_time, c.rn
)
,r AS (--逐日累加
    SELECT ISNULL(sal_amt,0) sal_amt,
           sal_time,
           rn
      FROM s
     WHERE rn = 1
    UNION ALL
    SELECT r.sal_amt+ISNULL(s.sal_amt,0) sal_amt,
           s.sal_time,
           s.rn
      FROM r
      JOIN s
        ON r.rn + 1 = s.rn
)
SELECT sal_amt, sal_time
  FROM r
    sal_amt sal_time
----------- ----------
          0 2015-07-01
          0 2015-07-02
        300 2015-07-03
        600 2015-07-04
       1000 2015-07-05
       2100 2015-07-06
       2100 2015-07-07
       2100 2015-07-08
       2800 2015-07-09
       2800 2015-07-10

Answer 3

谢谢Tiger_Zhao，我的是SQL2000，没办法用with，还有其它方法吗，谢谢

Answer 4

    SELECT ISNULL(SUM(d.sal_amt),0) sal_amt,
           c.sal_time
      FROM (
            SELECT DATEADD(day,number,'2015-07-01') sal_time
              FROM master..spt_values
             WHERE type = 'p'
               AND number < 10
           ) c
 LEFT JOIN d_sale d
        ON DATEDIFF(day, c.sal_time, d.sal_time) <= 0
  GROUP BY c.sal_time
数据量大了这个会比较慢，d_sale 越是前面的数据重复读取的次数越多。