mysql - 如何按特定值计算列？

Question

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

黄色背景是求和的选择列

我的示例代码。

    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)

Answer 1

此问题要求可以以行形式访问每月值，以便可以应用选定的聚合。通常，您可以使用 UNPIVOT 或 VALUES 将列旋转为行。下面是一个使用 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 |