对 select 子句中的一长串后续列执行计算的最佳方法

Question

我有一个表格，每周为不同的员工支付工资，以及其他一些数据。

例如

Employee ID  | Upper Limit | ... | Week 1 | Week 2 | Week 3 | Week .. | ... | Week 52 | isActive |
8971239        70000               100      200      100      400             500       1
7823468        60000               200      300      100      200             400       1
8187119        20000               300      300      300      300             200       1

当我需要计算例如特定员工所有周的总和时， 为了避免编写以下代码：

SELECT [Week 1] + [Week 2] + [Week 3] + ... + [Week 52] FROM tblWeeklySalaries where EmployeeID = 8971239

我正在循环中创建一个字符串，该字符串将用作要执行的脚本。 像这样：

DECLARE @script nvarchar(max)
SET @script = 'SET @periodou = (SELECT '
WHILE @tempWeek <= @week
BEGIN
IF @tempWeek <= 52
BEGIN
SET @script = @script + 'ISNULL([Week ' + CONVERT(NVARCHAR, @tempWeek) + '], 0) +'  
END
SET @tempWeek = @tempWeek + 1
END
SET @script = LEFT(@script, LEN(@script) - 1)
SET @script = @script + ' AS [Salary] FROM tblWeeklySalaries 
WHERE [EmployeeID ] = ' + CONVERT(NVARCHAR, @EmployeeID)

但是我不喜欢这种方式，因为创建字符串不容易处理，而且很多时候在要求更高的脚本中它会变得非常复杂。

因此，我正在寻找一种在特定表中添加一系列列的方法。 类似的东西

SELECT (COLUMNS(4, 56) FROM INFORMATION.SCHEMA WHERE TABLE_NAME = 'tblWeeklySalaries') FROM tblWeeklySalaries where EmployeeID = 8971239

有什么建议吗？ 多谢

Answer 1

你通过假肢违反了第一正常形式......后果将是：

• 编写查询的困难
• 重要的性能损失
• 事务异常

您有多个列的事实称为"周...n"表示您希望相同信息有多个值，该值因一个条件而异。

事实上，这可以替换为限制从 1 到 52 的周数组。 但是第一种范式说你应该总是在一列中只有原子值。 ARRAYS 包含多个值，因此它们不是原子的。 通过使用表的这种结构，您有一个隐藏数组。 这完全违反了假肢的第一个正常形式......

在正确设计数据库时：

CREATE TABLE T_EMPLOYEE_PAYS 
(EMP_ID,    YEAR,    WEEK_NUMBER,    SALARY)

查询将非常快速地写入：

SELECT EMP_ID,  YEAR,  SUM(SALARY)
FROM   T_EMPLOYEE_PAYS 
GROUP  BY EMP_ID,  YEAR

您可以毫不费力地为性能编制索引，也不会丢失事务中的数据。

请记住，关系数据库不是电子表格

Answer 2

你的数据结构很糟糕，应该修复它。 SQL 表几乎不应该有重复的列。 将值放在单独的行中要简单得多。

在任何情况下，您都可以编写一个视图来有效地执行此操作apply：

create view v_WeeklySalaries as
select ws.employee_id, . . . ,  -- other columns you want from tblWeeklySalaries
v.*      
from tblWeeklySalaries ws cross apply
(values ('Week 1', ws.[Week 1]),
('Week 2', ws.[Week 2]),
. . . 
) v(week, salary);

然后，您可以使用此视图。 您可能需要每周重新创建视图 - 数据模型中不清楚如何添加新周。 至少，在修复数据模型之前，您需要这样做。