如何在Microsoft SSMS / T-SQL中引用列名
如何在Microsoft SSMS / T-SQL中引用列名
提问于 2017-10-02 04:36:28
我使用基本的t-sql作为工作的一部分已经有一段时间了，但我从来没有真正投入太多的时间来发展我的技能集，并将自己归类为初学者-中等生。我有一个任务，我有一个特定体育比赛的数据，以及每个月的数字数据。
Competition, StartDate, EndDate, Aug_2017_Total, September_2017_Total,October_2017_Total
English Premier Leage, 2017-08-01,2018-07-01, 20, 30, 45我需要基本上创建一个总计列，如果数值数据的列标题(例如Aug_2017_Total )在开始日期和结束日期之间，则对内容求和(例如Aug_2017_Total + September_2017_Total等)。
伪代码的智慧，比如
sum(if columnanme like Total and columname between start date and end date) 然而，在我的一生中，我找不到一种以这种方式引用列标题的方法。我简要地研究了col_name和sysobjects，但它们似乎没有给我提供我想要的功能……
我想我遗漏了一些明显的东西。
回答 2
Stack Overflow用户
发布于 2017-10-02 12:33:09
我希望下面的内容能帮助您演示为什么需要改变存储数据的方式。下面我使用了3个查询，所有这些查询都结合在一起，以产生更规范化的结构。使用这种方法，您可以在不需要添加列的情况下添加下个月的数据，而且对数据求和也变得更容易。
你在下面看到的数据中有很多关于你的数据的假设，它只是一个指南。
SQL Fiddle
MS SQL Server 2014架构安装程序
CREATE TABLE Table1
([Competition] varchar(21), [StartDate] date, [EndDate] date, [Aug_2017_Total] int, [September_2017_Total] int, [October_2017_Total] int)
;
INSERT INTO Table1
([Competition], [StartDate], [EndDate], [Aug_2017_Total], [September_2017_Total], [October_2017_Total])
VALUES
('English Premier Leage', '2017-08-01', '2017-07-01', 20, 30, 45),
('English Premier Leage', '2017-09-01', '2017-09-01', 20, 30, 45),
('English Premier Leage', '2017-10-01', '2017-10-01', 20, 30, 45)
;查询1
select
Competition
, startdate
, concat(datename(month,startdate) + '_', year(startdate) ) MonthLabel
, MonthValue
from (
select Competition, StartDate, Aug_2017_Total as MonthValue
from table1
where StartDate = '20170801'
UNION ALL
select Competition, StartDate, September_2017_Total as MonthValue
from table1
where StartDate = '20170901'
UNION ALL
select Competition, StartDate, October_2017_Total as MonthValue
from table1
where StartDate = '20171001'
) unpiv| Competition | startdate | MonthLabel | MonthValue |
|-----------------------|------------|----------------|------------|
| English Premier Leage | 2017-08-01 | August_2017 | 20 |
| English Premier Leage | 2017-09-01 | September_2017 | 30 |
| English Premier Leage | 2017-10-01 | October_2017 | 45 |查询2
select
Competition
, SUM(MonthValue)
from (
select Competition, StartDate, Aug_2017_Total as MonthValue
from table1
where StartDate = '20170801'
UNION ALL
select Competition, StartDate, September_2017_Total as MonthValue
from table1
where StartDate = '20170901'
UNION ALL
select Competition, StartDate, October_2017_Total as MonthValue
from table1
where StartDate = '20171001'
) unpiv
where startdate >= '20170801'
and startdate < '20180801'
group by
Competition| Competition | |
|-----------------------|----|
| English Premier Leage | 95 |Stack Overflow用户
发布于 2017-10-04 01:19:53
您可能还想考虑UNPIVOT。它可以为您提供一个添加新列的位置，而不是在每次发生更改时编写完整的select语句。
_Months CTE用于匹配合计列应匹配的月份。如果你坚持使用命名约定{MMMM}_{yyyy}_Total，那么这个方法应该会继续适用于你。
_Results CTE可供您进一步查询以获得您感兴趣的输出。
SQL Fiddle
设置测试数据：
IF (OBJECT_ID('dbo.Scores') IS NOT NULL) DROP TABLE dbo.Scores;
GO
CREATE TABLE dbo.Scores (
Competition varchar(50),
StartDate date,
EndDate date,
Aug_2017_Total int,
September_2017_Total int,
October_2017_Total int
);
INSERT Scores
(Competition, StartDate, EndDate, Aug_2017_Total, September_2017_Total, October_2017_Total)
VALUES
('English Premier League', '2017-08-01', '2017-08-31', 20, 30, 45),
('English Premier League', '2017-09-01', '2017-09-30', 20, 30, 45),
('English Premier League', '2017-10-01', '2017-10-30', 20, 30, 45),
('Serie A', '2017-08-01', '2017-08-31', 20, 30, 45),
('Serie A', '2017-09-01', '2017-09-30', 20, 30, 45),
('Serie A', '2017-10-01', '2017-10-30', 20, 30, 45),
('La Liga', '2017-08-01', '2017-08-31', 20, 30, 45),
('La Liga', '2017-09-01', '2017-09-30', 20, 30, 45),
('La Liga', '2017-10-01', '2017-10-30', 20, 30, 45),
('Bundesliga', '2017-08-01', '2017-08-31', 20, 30, 45),
('Bundesliga', '2017-09-01', '2017-09-30', 20, 30, 45),
('Bundesliga', '2017-10-01', '2017-10-30', 20, 30, 45),
('Major League Soccer', '2017-08-01', '2017-08-31', 20, 30, 45),
('Major League Soccer', '2017-09-01', '2017-09-30', 20, 30, 45),
('Major League Soccer', '2017-10-01', '2017-10-30', 20, 30, 45);
GO 查询
WITH _Months AS (
SELECT 1 [MonthNumber], 'January' [MonthFullName]
UNION ALL SELECT 2 [MonthNumber], 'February' [MonthFullName]
UNION ALL SELECT 3 [MonthNumber], 'March' [MonthFullName]
UNION ALL SELECT 4 [MonthNumber], 'April' [MonthFullName]
UNION ALL SELECT 5 [MonthNumber], 'May' [MonthFullName]
UNION ALL SELECT 6 [MonthNumber], 'June' [MonthFullName]
UNION ALL SELECT 7 [MonthNumber], 'July' [MonthFullName]
UNION ALL SELECT 8 [MonthNumber], 'August' [MonthFullName]
UNION ALL SELECT 9 [MonthNumber], 'September' [MonthFullName]
UNION ALL SELECT 10 [MonthNumber], 'October' [MonthFullName]
UNION ALL SELECT 11 [MonthNumber], 'November' [MonthFullName]
UNION ALL SELECT 12 [MonthNumber], 'December' [MonthFullName]
),
_Results AS (
SELECT
Competition,
StartDate,
EndDate,
X.Points -- ... Points FOR ...
-- X.ColumnName, -- ... FOR ColumnName IN ...
FROM
Scores S
UNPIVOT(
Points FOR ColumnName IN (
Aug_2017_Total,
September_2017_Total,
October_2017_Total
-- add new columns here as the table structure changes
)
) X
INNER JOIN _Months M ON M.MonthFullName LIKE LEFT(X.ColumnName, CHARINDEX('_', X.ColumnName) - 1) + '%'
WHERE
M.MonthNumber = MONTH(StartDate)
AND CONVERT(int, SUBSTRING(X.ColumnName, CHARINDEX('_', X.ColumnName) + 1, 4)) = YEAR(StartDate)
)
SELECT
*
FROM
_Results R 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/46516665
复制相关文章
相似问题
腾讯云开发者
