sql - 如何在 SQL 中进行列式和行式操作

Question

我对 SQL 查询中的行式和列式操作有疑问。我将使用以下示例数据表来演示我的问题:

Person           Income      Hours worked   Day
A                100         10             1
A                200         9              2
B                300         8              1 
B                400         7              2
C                500         6              1
C                600         5              2

我想编写一个查询，从中获取 (A + B) 和 C 的总收入、工作小时数和每小时收入。即:

Person           Income      Hours worked   Income / Hour
A + B            1000        34             1000 / 34
C                1100        11             1100 / 11

我如何在 SQL 中执行此操作？

非常感谢!

Answer 1

您可以在子查询中使用 UNION ALL

SELECT Person, Income, [Hours worked], [Income / Hour]    
FROM (
  SELECT 'A + B' AS Person,
          SUM(Income) AS Income,
          SUM([Hours worked]) AS [Hours worked],
          SUM([Day]) AS [Hours worked]
  FROM dbo.TableName
  WHERE Person IN('A', 'B')           

  UNION ALL

  SELECT 'C' AS Person,
          SUM(Income) AS Income,
          SUM([Hours worked]) AS [Hours worked],
          SUM([Day]) AS [Hours worked]
  FROM dbo.TableName
  WHERE Person = 'C'
) x

如果你想排序，你可以在外部查询上应用 ORDER BY。