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

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

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

[@more@]

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

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

• 博文量
52
• 访问量
222596