| 導購 | 订阅 | 在线投稿
分享
 
 
 

跟日期有關的兩條經典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- 王朝網路 版權所有