mysql - 如何返回以字段值作为列标题的结果集？

Question

这是我所拥有的粗略概念:

+---------------+   +-----------------+    +---------------+
| customers     |   | transactions    |    | branches      |
+---------------+   +-----------------+    +---------------+
| customerid    |   | orderid         |    | branchid      |
|               |   | customerid (FK) |    |               | 
|               |   | branchid (FK)   |    |               | 
+---------------+   | datetime        |    +---------------+
                    +-----------------+

我将如何创建一个查询来提取每天/每周/等等的交易数量。但对于每个 branchid separately？

我试过这样的工会(只计算所有星期六)。这给出了结果，但不是所需的格式。

SELECT COUNT(orderid) as count1 FROM transactions WHERE WEEKDAY(datetime) = 5 
AND branchid = 'branch1' 
UNION 
SELECT COUNT(orderid) as count2 FROM transactions WHERE WEEKDAY(datetime) = 5 
AND branchid = 'branch2'

返回:

+------------+
|   count1   |
+------------+
|        152 |
|         48 |
+------------+

而我希望数据的格式如下:

+------------+------------+
|   count1   |   count2   |
+------------+------------+
|        152 |         48 |
|            |            |
+------------+------------+

有没有人对如何做到这一点有任何提示？提前致谢!

Answer 1

有几种方法可以实现这一点。使用已有的 UNION，您可以围绕子查询构建数据透视查询。此版本将静态 name 添加到原始 UNION 中的每个列，并使用它们来区分外部查询中的 CASE 语句。

在您的情况下，它可以通过 SELECT 列表中的子选择来完成，但是此方法更可扩展到其他类型的数据透视查询，并且是实现它们的更通用的约定。

SELECT 
  SUM(CASE WHEN name = 'count1' THEN counts ELSE 0 END) AS count1,
  SUM(CASE WHEN name = 'count2' THEN counts ELSE 0 END) AS count2
FROM (
   SELECT 'count1' as name, COUNT(orderid) as counts FROM transactions WHERE WEEKDAY(datetime) = 5 
     AND branchid = 'branch1' 
   UNION 
   SELECT 'count2' as name, COUNT(orderid) as counts FROM transactions WHERE WEEKDAY(datetime) = 5 
     AND branchid = 'branch2'
) subq