我需要一个查询，它可以每列输出一行，同时对该列的内容求和并按另一列分组(为了简化，请在内部绘制图表)

Question

我有一个包含电话呼叫记录的数据库（我不是电话销售人员，这是紧急呼叫数据）。每一行都是一条通话记录。某些列包含计时信息。每个调用还有一个优先级列。它看起来像这样：

  PRIORITY    TIMING1     TIMING2     TIMING3
  -----0----------22----------3-----------43 
  -----1----------42----------10----------12
  -----0----------12----------13----------6
  -----2----------23----------12----------37
  -----1----------12----------16----------23

我需要的输出基本上是每个时间一行，所有时间的总和按优先级分组为列。我提前知道有固定数量的时间和优先事项。它看起来是这样的：

------------Priority 0-------Priority 1-------Priority 2
  Timing1----123--------------332--------------233
  Timing2----265--------------241--------------302
  Timing3----387--------------192--------------201

有可能在一个查询中执行这样的操作吗？查询还必须能够在资源相当有限的移动平台上运行，尽管运行时间并不重要。考虑到这个表的大小和内存问题，我宁愿避免使用临时表。

Answer 1

您可以使用UNPIVOT和PIVOT进行以下操作：

这里是查询的静态版本-静态版本意味着您对值进行硬编码。

select *
from
(
  select *
  from
  (
    select priority, timing1, timing2, timing3
    from yourtable
  ) x
  unpivot
  (
    value
    for field in (timing1, timing2, timing3)
  ) u
) x1
pivot
(
  sum(value)
  for priority in ([0], [1], [2])
) p

请参阅SQL Fiddle with Demo

你也可以动态地这样做：

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX),
    @colsPivotName as  NVARCHAR(MAX)
select @colsUnpivot = stuff((select ','+ quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('yourtable') and
               C.name LIKE 'timing%'
         for xml path('')), 1, 1, '')
select @colsPivot = 
    STUFF((SELECT distinct ',' + Quotename(priority)
             from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
select @colsPivotName = 
    STUFF((SELECT distinct ',' 
           + Quotename(priority) + ' as Priority' + cast(priority as varchar(1))
             from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
set @query 
  = 'select col, '+ @colsPivotName +'
      from
      (
        select priority, val, col
        from 
        (
          select priority, ' + @colsUnpivot + '
          from yourtable
        ) x
        unpivot
        (
          val
          for col in ('+ @colsUnpivot +')
        ) u
      ) x1
      pivot
      (
        sum(val)
        for priority in ( '+ @colspivot + ')
      ) p'
exec(@query)

请参阅SQL Fiddle with Demo

编辑#1，您在评论中指出您使用的是没有PIVOT函数的SQLite。但您仍然可以使用UNION ALL，然后使用CASE:的聚合函数来完成此操作

select col,
  sum(case when priority = 0 then value end) as Priority0,
  sum(case when priority = 1 then value end) as Priority1,
  sum(case when priority = 2 then value end) as Priority2
from
(
  select priority, timing1 as value, 'timing1' as col
  from yourtable
  union all
  select priority, timing2 as value, 'timing2' as col
  from yourtable
  union all
  select priority, timing3 as value, 'timing3' as col
  from yourtable
) x
group by col

请参阅SQL Fiddle with Demo

Answer 2

如果你不介意翻转/反转表格，你可以试试这个：

SELECT PRIORITY, SUM(TIMING1), SUM(TIMING2), SUM(TIMING3)
  FROM tbl
 GROUP BY PRIORITY
 ORDER BY PRIORITY

Answer 3

使用SQL Sum和Over函数执行聚合求和，请参阅此链接：http://www.sqlteam.com/article/sql-sever-2005-using-over-with-aggregate-functions

Answer 4

因为你知道有固定数量的行和列，所以这里有一个简单的实现：

SELECT 'Timing1' AS Timing, SUM(IIF(PRIORITY = 0, TIMING1, 0)) AS [Priority 0],
    SUM(IIF(PRIORITY = 1, TIMING1, 0)) AS [Priority 1], 
    SUM(IIF(PRIORITY = 2, TIMING1, 0)) AS [Priority 2]
FROM CallRecords
UNION ALL
SELECT 'Timing2' AS Timing, SUM(IIF(PRIORITY = 0, TIMING2, 0)) AS [Priority 0], 
    SUM(IIF(PRIORITY = 1, TIMING2, 0)) AS [Priority 1], 
    SUM(IIF(PRIORITY = 2, TIMING2, 0)) AS [Priority 2]
FROM CallRecords
UNION ALL
SELECT 'Timing3' AS Timing, SUM(IIF(PRIORITY = 0, TIMING3, 0)) AS [Priority 0], 
    SUM(IIF(PRIORITY = 1, TIMING3, 0)) AS [Priority 1], 
    SUM(IIF(PRIORITY = 2, TIMING3, 0)) AS [Priority 2]
FROM CallRecords

不过，按照@n8wrl的建议翻转表格可能会更快，除非你绝对需要这种格式。