select中的SQL动态查询
select中的SQL动态查询
提问于 2018-05-31 19:13:18
如何编写SQL查询动态值？
pos_id start_date Enddate
1 20140131 20141201
2 20150331 20151201结果：
position_id startdate Enddate Month Year
1 20140131 20141201 1 2014
1 20140131 20141201 2 2014
1 20140131 20141201 3 2014
1 20140131 20141201 4 2014
1 20140131 20141201 5 2014
1 20140131 20141201 6 2014
1 20140131 20141201 7 2014
1 20140131 20141201 8 2014
1 20140131 20141201 9 2014
1 20140131 20141201 10 2014
1 20140131 20141201 11 2014
1 20140131 20141201 12 2014
2 20150331 20151201 3 2015
2 20150331 20151201 4 2015
2 20150331 20151201 5 2015
2 20150331 20151201 6 2015
2 20150331 20151201 7 2015
2 20150331 20151201 8 2015
2 20150331 20151201 9 2015
2 20150331 20151201 10 2015
2 20150331 20151201 11 2015
2 20150331 20151201 12 2015select start_date,
cast(SUBSTRING(start_date,5,2)as int),end_date,cast(SUBSTRING(end_date,5,2)as int),
case when
cast(SUBSTRING(start_date,5,2)as int) < cast(SUBSTRING(end_date,5,2)as int)
then start_date end
as date_range
from [dbo].[SRC_TEST_DATE]我可以比较这些值，但如何获得动态值。提前谢谢。
回答 2
Stack Overflow用户
发布于 2018-05-31 19:16:22
递归子查询是一种简单的方法：
with cte as (
select pos_id, startdate, enddate, startdate as yyyymm
from t
union all
select pos_id, startdate, enddate, dateadd(month, 1, startdate)
from t
where yyyymm < enddate
)
select pos_id, startdate, enddate, month(yyyymm), year(yyyymm)
from cte
order by pos_id, start_date, yyyymm;Stack Overflow用户
发布于 2018-06-05 20:05:09
SELECT Position_ID, MONTH(DATEADD(MONTH, X.NUMBER, BEGDA)) PERIOD,
YEAR(DATEADD(MONTH, X.NUMBER, BEGDA)) YEAR, BEGDA, ENdda
from SRC_IN_TEST
INNER JOIN
MASTER.DBO.SPT_VALUES X
ON 1 = 1
WHERE X.TYPE = 'P' AND
X.NUMBER <= DATEDIFF(MONTH, CONVERT(DATE,LEFT(BEGDA,8)),
CONVERT(DATE,LEFT(REPLACE(endda,9999,2024),8))页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50622997
复制相关文章
相似问题