请教一个填充日历的SQL

yuekai 2015-01-14 11:28:52
各位大牛，请教一条SQL。被难在最后一步了。

问题：
原数据是按日期记录变化点的一张表。只有value字段发生变化才增加一条。如果在起止时间内，且没有数据的日期，按距离这个日期之前的最近一个日期数据赋值。

下面已经处理到的最后一步，riqi是日历列。typeid是每个类别的id，每个类别均有全部日期，示例是1号到10号，实际可能更长。sdate和edate是这个typeid的有效区间。value是需要求的列，输入数据中已有的value值是之前记录的变化点。如何才能通过sql得到输出数据呢？



--输入数据

SELECT '2014/1/1' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 0 value

UNION ALL

SELECT '2014/1/2' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 1000 value

UNION ALL

SELECT '2014/1/3' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 0 value

UNION ALL

SELECT '2014/1/4' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 0 value

UNION ALL

SELECT '2014/1/5' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 0 value

UNION ALL

SELECT '2014/1/6' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 2000 value

UNION ALL

SELECT '2014/1/7' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 0 value

UNION ALL

SELECT '2014/1/8' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 0 value

UNION ALL

SELECT '2014/1/9' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 0 value

UNION ALL

SELECT '2014/1/10' riqi, 'a1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value

UNION ALL

SELECT '2014/1/1' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value

UNION ALL

SELECT '2014/1/2' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value

UNION ALL

SELECT '2014/1/3' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value

UNION ALL

SELECT '2014/1/4' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value

UNION ALL

SELECT '2014/1/5' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 100 value

UNION ALL

SELECT '2014/1/6' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value

UNION ALL

SELECT '2014/1/7' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 200 value

UNION ALL

SELECT '2014/1/8' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value

UNION ALL

SELECT '2014/1/9' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 300 value

UNION ALL

SELECT '2014/1/10' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value



--输出结果



SELECT '2014/1/1' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 0 value

UNION ALL

SELECT '2014/1/2' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 1000 value

UNION ALL

SELECT '2014/1/3' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 1000 value

UNION ALL

SELECT '2014/1/4' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 1000 value

UNION ALL

SELECT '2014/1/5' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 1000 value

UNION ALL

SELECT '2014/1/6' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 2000 value

UNION ALL

SELECT '2014/1/7' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 2000 value

UNION ALL

SELECT '2014/1/8' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 2000 value

UNION ALL

SELECT '2014/1/9' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 2000 value

UNION ALL

SELECT '2014/1/10' riqi, 'a1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value

UNION ALL

SELECT '2014/1/1' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value

UNION ALL

SELECT '2014/1/2' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value

UNION ALL

SELECT '2014/1/3' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value

UNION ALL

SELECT '2014/1/4' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value

UNION ALL

SELECT '2014/1/5' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 100 value

UNION ALL

SELECT '2014/1/6' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 100 value

UNION ALL

SELECT '2014/1/7' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 200 value

UNION ALL

SELECT '2014/1/8' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 200 value

UNION ALL

SELECT '2014/1/9' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 300 value

UNION ALL

SELECT '2014/1/10' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 300 value


谢谢
...全文
160 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
还在加载中灬 2015-01-14
  • 打赏
  • 举报
 回复
没注意，你不需要聚合，已修改
;WITH CTE AS(
	SELECT *,ROW_NUMBER()OVER(PARTITION BY typeid ORDER BY riqi)RN
	FROM TB
)
SELECT DATEADD(DAY,T4.number,riqiStart)riqi,T3.typeid,T3.startdate,T3.enddate,T3.VALUE
FROM(
	SELECT 
		ISNULL(T1.typeid,T2.typeid)typeid 
		,ISNULL(T1.value,0)value
		,ISNULL(T1.startdate,T2.startdate)startdate
		,ISNULL(T1.enddate,T2.enddate)enddate
		,ISNULL(T1.riqi,DATEADD(DAY,1-DAY(T2.riqi),T2.riqi))riqiStart
		,ISNULL(T2.riqi,DATEADD(DAY,1,T1.enddate))riqiEnd
	FROM CTE T1
		FULL JOIN CTE T2 ON T1.typeid=T2.typeid AND T1.RN+1=T2.RN
	)T3
	JOIN master..spt_values T4 ON T4.type='P'AND T4.number<DATEDIFF(DAY,riqiStart,riqiEnd)
ORDER BY T3.typeid,riqi
SQL2005+
frankl123 2015-01-14
  • 打赏
  • 举报
 回复 

;with tb1 as(
SELECT '2014/1/1' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 0 value
UNION ALL
SELECT '2014/1/2' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 1000 value
UNION ALL
SELECT '2014/1/3' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 0 value
UNION ALL
SELECT '2014/1/4' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 0 value
UNION ALL
SELECT '2014/1/5' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 0 value
UNION ALL
SELECT '2014/1/6' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 2000 value
UNION ALL
SELECT '2014/1/7' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 0 value
UNION ALL
SELECT '2014/1/8' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 0 value
UNION ALL
SELECT '2014/1/9' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 0 value
UNION ALL
SELECT '2014/1/10' riqi, 'a1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value
UNION ALL
SELECT '2014/1/1' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value
UNION ALL
SELECT '2014/1/2' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value
UNION ALL
SELECT '2014/1/3' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value
UNION ALL
SELECT '2014/1/4' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value
UNION ALL
SELECT '2014/1/5' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 100 value
UNION ALL
SELECT '2014/1/6' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value
UNION ALL
SELECT '2014/1/7' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 200 value
UNION ALL
SELECT '2014/1/8' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value
UNION ALL
SELECT '2014/1/9' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 300 value
UNION ALL
SELECT '2014/1/10' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 0 value
),
tb2 as(
select ROW_NUMBER() over (order by getdate()) id,* from tb1)
select *,(select MAX(value) from tb2 b where a.id>=b.id and a.typeid = b.typeid) from tb2 a
还在加载中灬 2015-01-14
  • 打赏
  • 举报
 回复 
;WITH CTE AS(
	SELECT T1.typeid,SUM(T2.value)value,T1.riqi riqiStart,T1.startdate,T1.enddate
		,COUNT(T2.typeid)RN
	FROM TB T1
		JOIN TB T2 ON T1.typeid=T2.typeid AND T1.riqi>=T2.riqi
	GROUP BY T1.typeid,T1.value,T1.riqi,T1.startdate,T1.enddate
)
SELECT DATEADD(DAY,T4.number,riqiStart)riqi,T3.typeid,T3.startdate,T3.enddate,T3.VALUE
FROM(
	SELECT 
		ISNULL(T1.typeid,T2.typeid)typeid 
		,ISNULL(T1.value,0)value
		,ISNULL(T1.startdate,T2.startdate)startdate
		,ISNULL(T1.enddate,T2.enddate)enddate
		,ISNULL(T1.riqiStart,DATEADD(DAY,1-DAY(T2.riqiStart),T2.riqiStart))riqiStart
		,ISNULL(T2.riqiStart,DATEADD(DAY,1,T1.enddate))riqiEnd
	FROM CTE T1
		FULL JOIN CTE T2 ON T1.typeid=T2.typeid AND T1.RN+1=T2.RN
	)T3
	JOIN master..spt_values T4 ON T4.type='P'AND T4.number<DATEDIFF(DAY,riqiStart,riqiEnd)
ORDER BY T3.typeid,riqi
yuekai 2015-01-14
  • 打赏
  • 举报
 回复
原表差不多就是把value是0的列都删掉。这里是为了后续处理方便。
还在加载中灬 2015-01-14
  • 打赏
  • 举报
 回复
你的原表长啥样 不是你的倒数第二步的数据，是原表的结构和数据
还在加载中灬 2015-01-14
  • 打赏
  • 举报
 回复 
如果没有master数据库有可以替换的么？
很多数据库设计的时候，也都会加一个存数字的表 你也可以考虑建一个，master...spt_values在这边也只有提供这个作用
Tiger_Zhao 2015-01-14
  • 打赏
  • 举报
 回复
选相关范围中的最后一条，不是用MAX()，而是用OUTER APPLY最合适。
WITH /* 测试数据

table1 AS (

    SELECT CAST(riqi AS datetime) riqi,

           typeid,

           CAST(startdate AS datetime) startdate,

           CAST(enddate AS datetime) enddate,

           value

      FROM (

            --提问中的输入数据

          ) t

), */

t1 AS (

    SELECT riqi,typeid,startdate,enddate,value,

           ROW_NUMBER() OVER(PARTITION BY typeid ORDER BY riqi) AS rn

      FROM table1

)

        SELECT m.riqi,

               m.typeid,

               m.startdate,

               m.enddate,

               CASE WHEN m.value <> 0 THEN

                    m.value

               ELSE

                    ISNULL(c.value,0)

               END value

          FROM t1 m

   OUTER APPLY (

                    SELECT TOP 1 *

                      FROM t1

                     WHERE typeid = m.typeid

                       AND rn < m.rn

                       AND m.riqi BETWEEN startdate AND enddate

                       AND value <> 0

                  ORDER BY rn DESC

               ) c

      ORDER BY m.typeid, m.rn

riqi                    typeid startdate               enddate                       value

----------------------- ------ ----------------------- ----------------------- -----------

2014-01-01 00:00:00.000 a1     2014-01-02 00:00:00.000 2014-01-09 00:00:00.000           0

2014-01-02 00:00:00.000 a1     2014-01-02 00:00:00.000 2014-01-09 00:00:00.000        1000

2014-01-03 00:00:00.000 a1     2014-01-02 00:00:00.000 2014-01-09 00:00:00.000        1000

2014-01-04 00:00:00.000 a1     2014-01-02 00:00:00.000 2014-01-09 00:00:00.000        1000

2014-01-05 00:00:00.000 a1     2014-01-02 00:00:00.000 2014-01-09 00:00:00.000        1000

2014-01-06 00:00:00.000 a1     2014-01-02 00:00:00.000 2014-01-09 00:00:00.000        2000

2014-01-07 00:00:00.000 a1     2014-01-02 00:00:00.000 2014-01-09 00:00:00.000        2000

2014-01-08 00:00:00.000 a1     2014-01-02 00:00:00.000 2014-01-09 00:00:00.000        2000

2014-01-09 00:00:00.000 a1     2014-01-02 00:00:00.000 2014-01-09 00:00:00.000        2000

2014-01-10 00:00:00.000 a1     2014-01-05 00:00:00.000 2014-01-10 00:00:00.000           0

2014-01-01 00:00:00.000 b1     2014-01-05 00:00:00.000 2014-01-10 00:00:00.000           0

2014-01-02 00:00:00.000 b1     2014-01-05 00:00:00.000 2014-01-10 00:00:00.000           0

2014-01-03 00:00:00.000 b1     2014-01-05 00:00:00.000 2014-01-10 00:00:00.000           0

2014-01-04 00:00:00.000 b1     2014-01-05 00:00:00.000 2014-01-10 00:00:00.000           0

2014-01-05 00:00:00.000 b1     2014-01-05 00:00:00.000 2014-01-10 00:00:00.000         100

2014-01-06 00:00:00.000 b1     2014-01-05 00:00:00.000 2014-01-10 00:00:00.000         100

2014-01-07 00:00:00.000 b1     2014-01-05 00:00:00.000 2014-01-10 00:00:00.000         200

2014-01-08 00:00:00.000 b1     2014-01-05 00:00:00.000 2014-01-10 00:00:00.000         200

2014-01-09 00:00:00.000 b1     2014-01-05 00:00:00.000 2014-01-10 00:00:00.000         300

2014-01-10 00:00:00.000 b1     2014-01-05 00:00:00.000 2014-01-10 00:00:00.000         300
yuekai 2015-01-14
  • 打赏
  • 举报
 回复
好用，非常感谢。还有个问题，如果没有master数据库有可以替换的么？
引用 8 楼 ky_min 的回复:
还在加载中灬 2015-01-14
  • 打赏
  • 举报
 回复
是的，你执行以下
;WITH TB AS(
	SELECT CAST('2014/1/2'AS DATETIME) riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 1000 value
	UNION ALL
	SELECT '2014/1/6' riqi, 'a1' typeid, '2014/1/2' startdate, '2014/1/9' enddate, 2000 value
	UNION ALL
	SELECT '2014/1/5' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 100 value
	UNION ALL
	SELECT '2014/1/7' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 200 value
	UNION ALL
	SELECT '2014/1/9' riqi, 'b1' typeid, '2014/1/5' startdate, '2014/1/10' enddate, 300 value
)
,CTE AS(
	SELECT *,ROW_NUMBER()OVER(PARTITION BY typeid ORDER BY riqi)RN
	FROM TB
)
SELECT DATEADD(DAY,T4.number,riqiStart)riqi,T3.typeid,T3.startdate,T3.enddate,T3.VALUE
FROM(
	SELECT 
		ISNULL(T1.typeid,T2.typeid)typeid 
		,ISNULL(T1.value,0)value
		,ISNULL(T1.startdate,T2.startdate)startdate
		,ISNULL(T1.enddate,T2.enddate)enddate
		,ISNULL(T1.riqi,DATEADD(DAY,1-DAY(T2.riqi),T2.riqi))riqiStart
		,ISNULL(T2.riqi,DATEADD(DAY,1,T1.enddate))riqiEnd
	FROM CTE T1
		FULL JOIN CTE T2 ON T1.typeid=T2.typeid AND T1.RN+1=T2.RN
	)T3
	JOIN master..spt_values T4 ON T4.type='P'AND T4.number<DATEDIFF(DAY,riqiStart,riqiEnd)
ORDER BY T3.typeid,riqi
yuekai 2015-01-14
  • 打赏
  • 举报
 回复
不好意思，这里的TB是我的输入table么？我没有f5有错误
引用 5 楼 ky_min 的回复:
yuekai 2015-01-14
  • 打赏
  • 举报
 回复
谢谢，value可能为负数，不少顺序增加。MAX(value)应该有问题。我研究一下 [quote=引用 4 楼 frankl123 的回复:]
asp.net知识库
从SQL中的一个表中导出HTML文件表格 获取數据库表的前N条记录 几段SQL Server语句和存储过程 生成表中的数据的脚本 最详细的SQL注入相关的命令整理 Oracle Oracle中PL/SQL单行函数和组函数详解 mssql+oracle Oracle...
我在CSDN参与的3000个帖子
2：100分急求，随机输出十个小写字母，但是，要求这十个字母不相同 3：求Sn=a+aa+aaa+…+aaa…a（n个a）之值 4：数组题 望高手帮忙！ 5：呵呵，来推荐一下我的网站，本站提供大量当今流行的免费的音乐和免费电影，...
MySQL入门到精通
数据库是实际开发中必然要使用的软件，掌握数据库的基本操作是对每一个后端程序的最基本要求。MySQL作为互联网企业中使用最广泛的数据库，作为Java程序员是不可不学的一门技术。通过本课程，您可以学习到如下知识点...
java面试题
1)抽象：抽象就是忽略一个主题中与当前目标无关的那些方面，以便更充分地 注意与当前目标有关的方面。抽象并不打算了解全部问题，而只是选择其中的一 部分，暂时不用部分细节。抽象包括两个方面，一是过程抽象,二是...
MySQL整体文档
MySQL是一个典型的C/S服务架构 mysql自带的客户端程序 mysql mysqladmin mysqldump mysqld服务端程序 二进制的程序，一个后台的守护进程 单进程多线程的服务结构 MySQL环境变量 环境变量 描述 AUTHENTICATION_LDAP_...
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章