递归SQL的写法
. Problem
Have table of sales per working day.
Need table of sales per calendar day.

CREATE TABLE Sales(day VARCHAR(10), date DATE, amount INTEGER);
INSERT INTO Sales VALUES('Friday','2006-05-12',30);
INSERT INTO Sales VALUES('Monday','2006-05-15',20);
INSERT INTO Sales VALUES('Tuesday','2006-05-16',15);
INSERT INTO Sales VALUES('Wednesday','2006-05-17',25);
INSERT INTO Sales VALUES('Thursday','2006-05-18',31);
INSERT INTO Sales VALUES('Friday','2006-05-19',33);
INSERT INTO Sales VALUES('Monday','2006-05-22',11);
INSERT INTO Sales VALUES('Tuesday','2006-05-23',18);

Select * from Sales;
 DAY       DATE       AMOUNT
 --------- ---------- ------
 Friday    2006-05-12     30
 Monday    2006-05-15     20
 Tuesday   2006-05-16     15
 Wednesday 2006-05-17     25
 Thursday  2006-05-18     31
 Friday    2006-05-19     33
 Monday    2006-05-22     11
 Tuesday   2006-05-23     18

 
WITH rec (dt)
AS (
    VALUES (DATE ('2006-05-12'))
    UNION ALL
    SELECT dt + 1 DAY
    FROM rec
    WHERE dt < DATE ('2006-05-23')
    )
SELECT DAYNAME(rec.dt) AS day
    ,rec.dt AS DATE
    ,COALESCE(amount, 0) AS sales
FROM rec
LEFT OUTER JOIN sales ON rec.dt = sales.DATE
ORDER BY dt

把周六，周日的数据给补上了

 DAY       DATE       SALES
 --------- ---------- -----
 Friday    2006-05-12    30
 Saturday  2006-05-13     0
 Sunday    2006-05-14     0
 Monday    2006-05-15    20
 Tuesday   2006-05-16    15
 Wednesday 2006-05-17    25
 Thursday  2006-05-18    31
 Friday    2006-05-19    33
 Saturday  2006-05-20     0
 Sunday    2006-05-21     0
 Monday    2006-05-22    11
 Tuesday   2006-05-23    18