| 订阅 | 在线投稿
分享
 
 
 

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

来源:互联网网民  宽屏版  评论
2006-12-17 07:41:10

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

跟日期有关的两条经典SQL语句 1.用一条语句得出某日期所在月份的最大天数?SELECT DAY(DATEADD(dd, -DAY('2004-02-13'), DATEADD(mm, 1, '2004-02-13'))) 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.日期

 
特别声明:以上内容(如有图片或视频亦包括在内)为网络用户发布,本站仅提供信息存储服务。
 
跟日期有关的两条经典SQL语句 跟日期有关的两条经典SQL语句 1.用一条语句得出某日期所在月份的最大天数?  SELECT DAY(DATEADD(dd, -DAY('2004-02-13'), DATEADD(mm, 1, '2004-02-13'))) 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.日期
󰈣󰈤
 
 
 
>>返回首页<<
 
 热帖排行
 
 
 
静静地坐在废墟上,四周的荒凉一望无际,忽然觉得,凄凉也很美
©2005- 王朝网络 版权所有