您的位置：首页 → 技术开发 → 数据库技巧 → 跟日期有关的两条经典SQL语句

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

时间：2004/12/2 1:55:00来源：本站整理作者：蓝点我要评论(1)

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.日期


相关阅读 Windows错误代码大全 Windows错误代码查询激活windows有什么用Mac QQ和Windows QQ聊天记录怎么合并 Mac QQ和Windows QQ聊天记录Windows 10自动更新怎么关闭 如何关闭Windows 10自动更新windows 10 rs4快速预览版17017下载错误问题Win10秋季创意者更新16291更新了什么 win10 16291更新内容windows10秋季创意者更新时间 windows10秋季创意者更新内容kb3150513补丁更新了什么 Windows 10补丁kb3150513是什么

文章评论
发表评论

热门文章 没有查询到任何记录。

最新文章 mssql企业管理器不能打 Oracle PRKC-1002错误原因和解决方案SQL SERVER数据库日志清空图文教程win2003计算机改名后sql server 2005 本地复DB2错误信息码大全

人气排行 彻底解决mysql中文乱码的办法mysql数据库root密码忘记的修改方法SQL SERVER数据库日志清空图文教程.bak备份文件如何恢复Oracle PRKC-1002错误原因和解决方案Oracle错误代码大全如何将txt的文本数据导入SQL server 2005呢DB2错误信息码大全