 |
|
|
 首页 |
名片设计 CorelDRAW Illustrator AuotoCAD Painter 其他软件 Photoshop Fireworks Flash |
|
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.日期 返回类别: 教程 上一教程: MYSQL数据进行并发锁定 下一教程: 如何将数据从文本导入到MYSQL 您可以阅读与"跟日期有关的两条经典SQL语句"相关的教程: · SQL SERVER日志清除的两种方式 · SQL Server各种日期计算方式 · SQL中与IP相关的常见问题 · MYSQL的日期和时间函数 · SQL中PRINT语句的烦恼 |
  |
| 快精灵印艺坊 版权所有 |
首页 会员中心在线印刷在线编辑付款方式索取样品设计指南连锁门店
|
||
