ITPub博客

首页 > 数据库 > 数据库开发技术 > 跟日期有关的两条经典SQL语句

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

原创 数据库开发技术 作者:qiuwuhen 时间:2005-05-14 19:28:06 0 删除 编辑

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

SELECT DAY(DATEADD(dd, -DAY('2004-02-13'), DATEADD(mm, 1, '2004-02-13'))) AS 'Day Number'

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

[@more@]

跟日期有关的两条经典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.日期

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/39424/viewspace-798435/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-09-23

  • 博文量
    52
  • 访问量
    221291