SQL年份语句

[hongchan5229]

现在我有一个这样的表，我想要的效果是select出未来2年的年份，如下表




想通过图1得出图2 的结果

或者通过语句获取本年，明年，后年的结果，不用union

谢谢解答

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(
	t1_Year INT	
)
GO
SET NOCOUNT ON
INSERT INTO t VALUES (2016)
INSERT INTO t VALUES (2016)
INSERT INTO t VALUES (2017)
INSERT INTO t VALUES (2018)
GO
---- 以上为测试数据 -----

SELECT t1_Year 
FROM t 
GROUP BY t1_Year
UNION 
SELECT YEAR(DATEADD(YEAR,1,GETDATE()))
UNION 
SELECT YEAR(DATEADD(YEAR,2,GETDATE()))
/*
t1_Year
2016
2017
2018
2019
2020
 */

[hongchan5229]

引用 1 楼 yenange 的回复:

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(
	t1_Year INT	
)
GO
SET NOCOUNT ON
INSERT INTO t VALUES (2016)
INSERT INTO t VALUES (2016)
INSERT INTO t VALUES (2017)
INSERT INTO t VALUES (2018)
GO
---- 以上为测试数据 -----

SELECT t1_Year 
FROM t 
GROUP BY t1_Year
UNION 
SELECT YEAR(DATEADD(YEAR,1,GETDATE()))
UNION 
SELECT YEAR(DATEADD(YEAR,2,GETDATE()))
/*
t1_Year
2016
2017
2018
2019
2020
 */

不用union啊，版主

[吉普赛的歌]

;WITH cte AS (
	SELECT CASE WHEN sv.number-2<=0 THEN sv.number ELSE -sv.number+3 END AS n
	FROM [master].dbo.spt_values AS sv 
	WHERE sv.[type]='P' AND sv.number>0 AND sv.number<100
)
SELECT Year(DATEADD(YEAR,n,GETDATE())) AS y
FROM cte 
WHERE Year(DATEADD(YEAR,n,GETDATE())) >= (SELECT MIN(t1_Year) FROM t)

[吉普赛的歌]

[二月十六]

利用sptvalues表。number的大小范围为0到最大年和最小年的差加2，然后再利用dateadd函数，把所有年份读取出来。手头没电脑，没法给你写啊

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([t_YEAR] Date)
Insert #T
select '2016' union all
select '2017' union all
select '2018'
Go
--测试数据结束
SELECT
    YEAR(DATEADD(   YEAR, number,
        (
            SELECT
                MIN(t_YEAR)
            FROM
                #T
        )
           )) t_YEAR
FROM
    master..spt_values
WHERE
    type = 'p'
    AND number
    BETWEEN 0 AND
            (
                SELECT
                    DATEDIFF(YEAR, MIN(t_YEAR), MAX(t_YEAR))
                FROM
                    #T
            ) + 2;

[hongchan5229]

两位都辛苦了，所以给两位分了