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
ROM 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.日期
恩,按照你的方法成功解决乱码
@echo off都没有解释为什要有这个 还有后面的那个*。bat
怎么没有一个回首页的连接···[face09]
你是转载高手吗?
这是叠罗汉么? 随便就往上面放。。。。。 一点规律都没有就上面写了四类···· 哎 年轻人哪
里面的东西不能自动换行。看起来真郁闷。。[face12]
从别的地方转载的,你爱看就看,不爱看也没了[face62]
[face78] 太多了前面要先有个目录···· 累、
[face78]无聊! 注册了有什么用啊 应该还没有人注册····