当前位置:七道奇文章资讯数据防范MSSQL防范
日期:2011-05-02 15:21:00  来源:本站整理

跟日期有关的两条经典SQL语句[MSSQL防范]

赞助商链接



  本文“跟日期有关的两条经典SQL语句[MSSQL防范]”是由七道奇为您精心收集,来源于网络转载,文章版权归文章作者所有,本站不对其观点以及内容做任何评价,请读者自行判断,以下是其具体内容:
1.用一条语句得出某日期所在月份的最大天数?www.come on babyiTbulo.com92r725v


  SELECT DAY(DATEADD(dd, -DAY(''2004-02-13''), DATEADD(mm, 1, ''2004-02-13''))) AS ''Day Number''www.come on babyiTbulo.com92r725v


  2.少记录变成多条记录问题www.come on babyiTbulo.com92r725v


  有表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 40www.come on babyiTbulo.com92r725v


www.come on babyiTbulo.com92r725v



  /*
  测试数据:
  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
  */
www.come on babyiTbulo.com92r725v



  要得到的后果:
  日期 收入 支出 余额
  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
 ?004-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 30www.come on babyiTbulo.com92r725v


www.come on babyiTbulo.com92r725v




www.come on babyiTbulo.com92r725v



  答案:www.come on babyiTbulo.com92r725v


  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 www.come on babyiTbulo.com92r725v


  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语句[MSSQL防范]”的内容,如果你对以上该文章内容感兴趣,你可以看看七道奇为您推荐以下文章:

  • 跟日期有关的两条经典SQL语句
  • 本文地址: 与您的QQ/BBS好友分享!
    • 好的评价 如果您觉得此文章好,就请您
        0%(0)
    • 差的评价 如果您觉得此文章差,就请您
        0%(0)

    文章评论评论内容只代表网友观点,与本站立场无关!

       评论摘要(共 0 条,得分 0 分,平均 0 分) 查看完整评论
    Copyright © 2020-2022 www.xiamiku.com. All Rights Reserved .