跟日期有关的两条经典SQL语句

 

1.用一条语句得出某日期所在月份的最大天数？

SELECT DAY(DATEADD(dd, -1, DATEADD(mm, 1, DATEADD(dd, 1-DAY('2004-8-31'), '2004-8-31')))) AS 'Day Number'

2.少记录变成多条记录问题

有表tbl
日期       收入    支出
2004-02-11 00:00:00 60 45
2004-03-01 00:00:00 60 45
2004-03-02 00:00:00 40 50
2004-03-05 00:00:00 50 40

/*
测试数据：
Create Table tbl([日期] smalldatetime,[收入] int ,[支出] int)

Insert Into tbl
SELECT '2004-02-11', 60, 45
union SELECT '2004-03-01',60, 45
union SELECT '2004-03-02',40, 50
union SELECT '2004-03-05',50, 40
*/

要得到的结果：
日期                                                           收入          支出          余额         
------------------------------------------------------ ----------- ----------- -----------
2004-02-01 00:00:00                                    NULL        NULL        NULL
2004-02-02 00:00:00                                    NULL        NULL        NULL
2004-02-03 00:00:00                                    NULL        NULL        NULL
2004-02-04 00:00:00                                    NULL        NULL        NULL
2004-02-05 00:00:00                                    NULL        NULL        NULL
2004-02-06 00:00:00                                    NULL        NULL        NULL
2004-02-07 00:00:00                                    NULL        NULL        NULL
2004-02-08 00:00:00                                    NULL        NULL        NULL
2004-02-09 00:00:00                                    NULL        NULL        NULL
2004-02-10 00:00:00                                    NULL        NULL        NULL
2004-02-11 00:00:00                                    60            45           15
2004-02-12 00:00:00                                    NULL        NULL        15
2004-02-13 00:00:00                                    NULL        NULL        15
2004-02-14 00:00:00                                    NULL        NULL        15
2004-02-15 00:00:00                                    NULL        NULL        15
2004-02-16 00:00:00                                    NULL        NULL        15
2004-02-17 00:00:00                                    NULL        NULL        15
2004-02-18 00:00:00                                    NULL        NULL        15
2004-02-19 00:00:00                                    NULL        NULL        15
2004-02-20 00:00:00                                    NULL        NULL        15
2004-02-21 00:00:00                                    NULL        NULL        15
2004-02-22 00:00:00                                    NULL        NULL        15
2004-02-23 00:00:00                                    NULL        NULL        15
2004-02-24 00:00:00                                    NULL        NULL        15
2004-02-25 00:00:00                                    NULL        NULL        15
2004-02-26 00:00:00                                    NULL        NULL        15
2004-02-27 00:00:00                                    NULL        NULL        15
2004-02-28 00:00:00                                    NULL        NULL        15
2004-02-29 00:00:00                                    NULL        NULL        15
2004-03-01 00:00:00                                    60            45           30
2004-03-02 00:00:00                                    40            50           20
2004-03-03 00:00:00                                    NULL        NULL        20
2004-03-04 00:00:00                                    NULL        NULL        20
2004-03-05 00:00:00                                    50            40           30
2004-03-06 00:00:00                                    NULL        NULL        30
2004-03-07 00:00:00                                    NULL        NULL        30
2004-03-08 00:00:00                                    NULL        NULL        30
2004-03-09 00:00:00                                    NULL        NULL        30
2004-03-10 00:00:00                                    NULL        NULL        30
2004-03-11 00:00:00                                    NULL        NULL        30
2004-03-12 00:00:00                                    NULL        NULL        30
2004-03-13 00:00:00                                    NULL        NULL        30
2004-03-14 00:00:00                                    NULL        NULL        30
2004-03-15 00:00:00                                    NULL        NULL        30
2004-03-16 00:00:00                                    NULL        NULL        30
2004-03-17 00:00:00                                    NULL        NULL        30
2004-03-18 00:00:00                                    NULL        NULL        30
2004-03-19 00:00:00                                    NULL        NULL        30
2004-03-20 00:00:00                                    NULL        NULL        30
2004-03-21 00:00:00                                    NULL        NULL        30
2004-03-22 00:00:00                                    NULL        NULL        30
2004-03-23 00:00:00                                    NULL        NULL        30
2004-03-24 00:00:00                                    NULL        NULL        30
2004-03-25 00:00:00                                    NULL        NULL        30
2004-03-26 00:00:00                                    NULL        NULL        30
2004-03-27 00:00:00                                    NULL        NULL        30
2004-03-28 00:00:00                                    NULL        NULL        30
2004-03-29 00:00:00                                    NULL        NULL        30
2004-03-30 00:00:00                                    NULL        NULL        30
2004-03-31 00:00:00                                    NULL        NULL        30

答案：

SELECT Y.[日期], tbl.[收入], tbl.[支出], (
    SELECT SUM(ISNULL(tbl.[收入], 0)-ISNULL(tbl.[支出], 0)) FROM tbl WHERE [日期]<=Y.[日期]) AS [余额]
        FROM tbl RIGHT JOIN (
            SELECT DATEADD(dd, N.i, DATEADD(dd, 1-DAY(m.MinDay), m.MinDay)) AS [日期]
            FROM (
                SELECT 0 AS i
                UNION ALL SELECT 1
                UNION ALL SELECT 2
                UNION ALL SELECT 3
                UNION ALL SELECT 4
                UNION ALL SELECT 5
                UNION ALL SELECT 6
                UNION ALL SELECT 7
                UNION ALL SELECT 8
                UNION ALL SELECT 9
                UNION ALL SELECT 10
                UNION ALL SELECT 11
                UNION ALL SELECT 12
                UNION ALL SELECT 13
                UNION ALL SELECT 14
                UNION ALL SELECT 15
                UNION ALL SELECT 16
                UNION ALL SELECT 17
                UNION ALL SELECT 18
                UNION ALL SELECT 19
                UNION ALL SELECT 20
                UNION ALL SELECT 21
                UNION ALL SELECT 22
                UNION ALL SELECT 23
                UNION ALL SELECT 24
                UNION ALL SELECT 25
                UNION ALL SELECT 26
                UNION ALL SELECT 27
                UNION ALL SELECT 28
                UNION ALL SELECT 29
                UNION ALL SELECT 30
                UNION ALL SELECT 31
            ) N,
            (
                SELECT MIN(日期) AS MinDay
                FROM tbl
                GROUP BY DATEDIFF(month, 0, 日期)
            ) M
            WHERE DATEDIFF(mm, DATEADD(dd, N.i, DATEADD(dd, 1-DAY(m.MinDay), m.MinDay)), M.MinDay)=0) AS Y
        ON tbl.[日期]=Y.日期