代码之家 › 专栏 › 技术社区 › anbiun

如何按特定值计算列?

sql-server-2008 sql-server mysql

anbiun · 技术社区 · 7 年前

如何像这些图片一样查询结果。第一列选择是本月加上条件字段的下一列( 选择列 )

黄色背景是sum的select列

我的示例代码。

    declare @myDate date = getdate(),@qry varchar(max)

set @qry = 'select case v.SelectColumn
    when 0 then (SELECT '+DATENAME(month,@myDate)+')
    when 2 then (SELECT '+DATENAME(month,@myDate)+'+'+DATENAME(MONTH,DATEADD(MONTH,1,@myDate))+')
    when 1 then (SELECT '+DATENAME(month,@myDate)+'+'+DATENAME(MONTH,DATEADD(MONTH,1,@myDate))+'+'+DATENAME(MONTH,DATEADD(MONTH,2,@myDate))+')

    end
    as SumColumn

from vwQC12Month v'
exec (@qry)

1 回复 | 直到 7 年前

RnP 7 年前

此问题要求每月值可以作为行访问,以便可以应用选定的聚合。通常,可以使用UNPIVOT或值将列旋转为行。下面是一个使用UNPIVOT的工作示例。

在下面的查询中,您必须更改简短的月份名称,以匹配列名,才能使其工作。这是一个工作演示: http://sqlfiddle.com/#!18/094e4/2

此外,您可能需要考虑如何处理年终包装,并相应地调整聚合。

-- setup sample data 
create table Test (
  id int, Jan int, Feb int, Mar int, Apr int, May int, 
  Jun int, Jul int, Aug int, Sep int, Oct int, Nov int, Dec int,
  SelectColumn int)

insert Test values
(1, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 1),
(2, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 2),
(3, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 3),
(4, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 4)

-- query 
DECLARE @currentMonthNumber int = MONTH(getdate())

-- CTE that rotates the data into rows
; WITH RowsByMonth(id, SelectColumn, monthNumber, val) AS
(
  SELECT id, SelectColumn, CONVERT(int, month) AS monthNumber, val
  FROM
    (SELECT id, SelectColumn, 
       Jan AS [1], Feb AS [2], Mar AS [3], Apr AS [4], 
       May AS [5], Jun AS [6], Jul AS [7], Aug AS [8],
       Sep AS [9], Oct AS [10], Nov AS [11], Dec AS [12] 
     FROM Test) AS Source
     UNPIVOT
     (val FOR month IN 
      ([1], [2], [3], [4],
       [5], [6], [7], [8],
       [9], [10], [11], [12])
     ) AS asRows 
) -- aggregation below 
SELECT id, SUM(val) AS SumAcrossSelectedMonths
FROM RowsByMonth
WHERE 
  monthNumber >= @currentMonthNumber 
  AND monthNumber - @currentMonthNumber < SelectColumn
GROUP BY id

-- Results
| id | SumAcrossSelectedMonths |
|----|-------------------------|
|  1 |                      40 |
|  2 |                      90 |
|  3 |                     150 |
|  4 |                     220 |