如何将第一次select的结果作为第二次select的列名?

nothk 2018-10-24 10:51:06
有一张表 tableA, 列名是 Jan , Feb , Mar .....Dec
现在希望每天取对应列的结果,
比如今天是1月10日,则取Jan列,如果是2月10日则取Feb列,


第一个select返回当前月的缩写,
select SubString('JanFebMarAprMayJunJulAugSepOctNovDec',Month(GETDATE())*3-2,3)
(比如今天10月24日,返回Oct)

我想在第二个select 中 返回表tableA的Oct列 ,

要怎么写?

多谢大神,
...全文
447 3 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
Dear SQL(燊) 2018-12-07
  • 打赏
  • 举报
回复
SQL SERVER 2016+
--方法二:
SELECT * FROM (
    SELECT 'Jan' AS m,Jan AS v FROM tableA
    UNION ALL SELECT 'Feb',Feb FROM tableA
    UNION ALL SELECT 'Mar',Mar FROM tableA
    UNION ALL SELECT 'Apr',Apr FROM tableA
    UNION ALL SELECT 'May',May FROM tableA
    UNION ALL SELECT 'Jun',Jun FROM tableA
    UNION ALL SELECT 'Jul',Jul FROM tableA
    UNION ALL SELECT 'Aug',Aug FROM tableA
    UNION ALL SELECT 'Sep',Sep FROM tableA
    UNION ALL SELECT 'Oct',Oct FROM tableA
    UNION ALL SELECT 'Nov',Nov FROM tableA
    UNION ALL SELECT 'Dec',Dec FROM tableA
) AS t 
WHERE t.m=SubString('JanFebMarAprMayJunJulAugSepOctNovDec',Month(GETDATE())*3-2,3)



USE tempdb
GO
IF OBJECT_ID('tableA') IS NOT NULL DROP TABLE tableA
GO
CREATE TABLE tableA(
    Jan INT,
    Feb INT,
    Mar INT,
    Apr int,
    May int,
    Jun int,
    Jul int,
    Aug int,
    Sep int,
    Oct INT,
    Nov INT,
    [Dec] int
)
GO
INSERT INTO tableA
(
    Jan,
    Feb,
    Mar,
    Apr,
    May,
    Jun,
    Jul,
    Aug,
    Sep,
    Oct,
    Nov,
    [Dec]
)
VALUES
(
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9,
    10,
    11,
    12
)
---- 以上为测试表及测试数据

declare @str nvarchar(4000)= (select * from tableA for JSON path)


select [key],[value]
from openjson(SUBSTRING(@str,2,len(@str)-2))
where [key]=SubString('JanFebMarAprMayJunJulAugSepOctNovDec',Month(GETDATE())*3-2,3)
吉普赛的歌 2018-10-24
  • 打赏
  • 举报
回复

--方法二:
SELECT * FROM (
SELECT 'Jan' AS m,Jan AS v FROM tableA
UNION ALL SELECT 'Feb',Feb FROM tableA
UNION ALL SELECT 'Mar',Mar FROM tableA
UNION ALL SELECT 'Apr',Apr FROM tableA
UNION ALL SELECT 'May',May FROM tableA
UNION ALL SELECT 'Jun',Jun FROM tableA
UNION ALL SELECT 'Jul',Jul FROM tableA
UNION ALL SELECT 'Aug',Aug FROM tableA
UNION ALL SELECT 'Sep',Sep FROM tableA
UNION ALL SELECT 'Oct',Oct FROM tableA
UNION ALL SELECT 'Nov',Nov FROM tableA
UNION ALL SELECT 'Dec',Dec FROM tableA
) AS t
WHERE t.m=SubString('JanFebMarAprMayJunJulAugSepOctNovDec',Month(GETDATE())*3-2,3)
吉普赛的歌 2018-10-24
  • 打赏
  • 举报
回复
USE tempdb
GO
IF OBJECT_ID('tableA') IS NOT NULL DROP TABLE tableA
GO
CREATE TABLE tableA(
	Jan INT,
	Feb INT,
	Mar INT,
	Apr int,
	May int,
	Jun int,
	Jul int,
	Aug int,
	Sep int,
	Oct INT,
	Nov INT,
	[Dec] int
)
GO
INSERT INTO tableA
(
	Jan,
	Feb,
	Mar,
	Apr,
	May,
	Jun,
	Jul,
	Aug,
	Sep,
	Oct,
	Nov,
	[Dec]
)
VALUES
(
	1,
	2,
	3,
	4,
	5,
	6,
	7,
	8,
	9,
	10,
	11,
	12
)
---- 以上为测试表及测试数据

DECLARE @sql NVARCHAR(MAX)
DECLARE @m VARCHAR(10)
SELECT @m=SubString('JanFebMarAprMayJunJulAugSepOctNovDec',Month(GETDATE())*3-2,3)

SET @sql='select ['+@m+'] from tableA'
EXEC (@sql);
/*
Oct
------
10
 */

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

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