简化对月份 12 列的 SQL 表的查询

Question

我有一个正在生产的表，每个月有 12 列。我需要创建一个 SP，在其中传递产品 ID、客户和月份参数并检索该月的总和。

目前我的逻辑是

if month = 1 then
  select sum(JAN) from table where productID = @id and customer = @cust
if month = 2 then
   select SUM(FEB) from table where productID = @id and customer = @cust

....

查询有点复杂，但这是它的核心。有什么办法可以绕过这些“IF”语句吗？

编辑 - 这是一个 SQL Server 2000 数据库，但将迁移到 SQL Server 2005，当我迁移到 SQL Server 2005 时，PIVOT 和 UNPIVOT 将派上用场。

Answer 1

我认为查询的困难源于您的数据库未正确规范化这一事实。

您似乎有一个包含列的表：

productId
customer
JAN
FEB
...

要么是这样，要么是在存储过程中您创建了一个看起来像这样的临时表。这不是在 SQL 中执行此操作的最佳方法。您的目标应该是这样的表结构：

productId
customer
date
amount

然后您可以使用简单的 WHERE 子句编写查询：

SELECT SUM(amount)
FROM table
WHERE MONTH(date) = month AND productID = @id and customer = @cust

您可能还想检查查询中的年份，否则您可能会汇总 2009 年 1 月和 2010 年 1 月的数据。或者...也许您每年也有一张桌子？ （我希望不会！）

---

注意：当然，此时更改表结构实际上可能并不可行。我仍然认为，如果您不知道这个问题，这一点值得一提。至少了解这一点可以防止将来再次犯同样的错误。

Answer 2

看看 UNPIVOT - 您将取消透视以使列变为行，然后使用 WHERE 子句仅获取您感兴趣的月份的行。

内部：

SELECT *
FROM tbl UNPIVOT FOR Month IN ([JAN], [FEB], etc...)

然后连接到表以将月份转换为数字：
1月1日
2月2日
等。

然后WHERE MonthNum = @param

Answer 3

select sum(case when @month = 1 then Jan
        when @month = 2 then Feb
        when @month = 3 then Mar
        when @month = 4 then Apr
        when @month = 5 then May
        when @month = 6 then Jun
        when @month = 7 then Jul
        when @month = 8 then Aug
        when @month = 9 then Sep
        when @month = 10 then Oct
        when @month = 11 then Nov
        when @month = 12 then [Dec] end) 
        from mytable
        where productID = @id and customer = @cust 

假设您声明并初始化了变量，这应该可以工作。当然，如果您想要执行多个月的操作，情况会更复杂，这就是该设计不是最适合查询的原因之一。

Answer 4

怎么样：

declare @month int
set @month = 2
select sum(case @month
                when 1 then JAN
                when 2 then FEB
                when 3 then MAR
                when 4 then APR
                when 5 then MAY
                when 6 then JUN
                when 7 then JUL
                when 8 then AUG
                when 9 then SEP
                when 10 then OCT
                when 11 then NOV
                when 12 then DEC
                else 0
            end) 
from table 
where productID = @id 
and customer = @cust

Answer 5

首先想到的是 - 您可以使用动态 sql 生成查询并使用简单的 EXEC 执行。

在我看来，这听起来像是一个没有麻烦的解决方案，对于如此简单的查询，我认为没有理由不选择最简单的选项。

Answer 6

您可以查看 SQL Server 中的 UNPIVOT 函数 - UnPivot

Answer 7

select sum(quantity) from table unpivot (quantity for month in (jan,feb,mar/*etc*/)) as t where where productID = @id and customer = @cust and month='jan'