月光软件站 - 编程文档 - 数据库 - 跟日期有关的两条经典SQL语句

数据库

本类阅读TOP10

·SQL语句导入导出大全
·Power Designer杂记
·SQL Server日期计算
·常用的oracle函数使用说明(一)
·sqlserver2000数据库置疑的解决方法
·MS SQLServer OLEDB分布式事务无法启动的一般解决方案
·SQL to Excel 的应用
·SQL语句导入导出大全
·Error:ORA-01033:ORACLE initialization or shutdown in progress错误解决
·Oracle中password file的作用及说明

→ 分类导航

VC语言	Delphi
VB语言	ASP
Perl	Java
Script	数据库
其他语言	游戏开发
文件格式	网站制作
软件工程	.NET开发

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

作者：未知来源：月光软件站 加入时间：2005-2-28　月光软件站

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

相关文章：

相关软件：