MySQL查询动态将行数据转换为列的方式

Question

我正在处理一个数据透视表查询。

数据结构如下：

学号，姓名，地区

同一个人的姓名可能会出现在多个不同的地区。例如，以样本数据为例。

1 Mike CA
2 Mike CA
3 Proctor JB
4 Luke MN
5 Luke MN
6 Mike CA
7 Mike LP
8 Proctor MN
9 Proctor JB
10 Proctor MN
11 Luke MN

您看到我有一组4个不同的地区（CA，JB，MN，LP）。现在我想通过将名称映射到地区来生成透视表。

Name CA JB MN LP
Mike 3 0 0 1
Proctor 0 2 2 0
Luke 0 0 3 0

我为此编写了以下查询。

select name,sum(if(District="CA",1,0)) as "CA",sum(if(District="JB",1,0)) as "JB",sum(if(District="MN",1,0)) as "MN",sum(if(District="LP",1,0)) as "LP" from district_details group by name

然而，可能会出现区域增加的情况，那么我将不得不手动编辑查询并将新区域添加到其中。

我想知道是否有一种查询可以动态地获取不同地区的名称并运行上述查询。我知道可以使用过程并即时生成脚本来完成，是否还有其他方法？

我之所以这样问是因为查询“select distinct(districts) from district_details” 的输出将返回一个单列，每行都有一个区名，我希望将其转置到列中。

- Anirudh Goel

---

看一下MySQL的GROUP_CONCAT()函数。 - Corey Ballou

Answer 1

你不能使用静态SQL语句返回可变数量的列，需要在每次不同地区数量发生变化时构建该语句。为此，您首先执行一个

SELECT DISTINCT District FROM district_details;

这将为您提供已经有详细信息的区域列表。然后，您可以通过循环遍历之前的结果来构建SQL语句（伪代码）。

statement = "SELECT name "

For each row returned in d = SELECT DISTINCT District FROM district_details 
    statement = statement & ", SUM(IF(District=""" & d.District & """,1 ,0)) AS """ & d.District & """" 

statement = statement & " FROM district_details GROUP BY name;"

执行该查询。然后，在您的代码中，您需要处理可变数量的列的处理。

- Consultuning

Answer 2

a) MySQL存储过程不支持"For each"语句。 b) 存储过程不能使用所谓的动态SQL语句执行连接字符串中的预处理语句，也不能返回多个不同行的结果。 c) 存储函数根本无法执行动态SQL。

一旦你有了一个好主意，每个人似乎都会在他们想到“为什么有人想要…”之前驳斥它，这是一场噩梦。

我希望你能找到解决方案，我还在寻找我的解决方案。 我最接近的是

(请原谅伪代码)

-> 到存储过程，构建函数...

1）创建临时表 2）使用if语句从列中加载数据到临时表中 3）将临时表加载到存储过程的INOUT或OUT参数中，就像调用表一样...如果您可以让它返回多行

还有另一个提示... 将您的地区作为表格传统样式存储，加载此表并通过循环遍历标记为活动的地区来动态连接出查询字符串，该查询字符串对于系统来说可以是纯文本

然后使用;

prepare stmName from @yourqyerstring; execute stmName; deallocate prepare stmName;

(在mysql论坛的存储过程部分可以找到更多信息)

每次运行不同的地区集合，而无需重新设计原始程序。

也许用数字形式更容易理解。 我处理表格中的纯文本内容，没有任何需要求和、计数或累加的内容。

- Techie

---

b) 存储过程不能使用所谓的动态SQL语句从连接字符串中执行准备好的语句，也不能返回具有多个不同行的结果。c) 存储函数根本无法执行动态SQL。这是错误的。MySQL存储过程可以执行动态SQL，也可以进行连接和准备，但不能嵌套（即不能使用EXECUTE执行另一个PREPARE/EXECUTE语句）。请参见：http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html - Hendy Irawan

a) "对于MySQL存储过程来说，不支持“for each”语句。"虽然如此，在MySQL SP内部仍然支持使用REPEAT-UNTIL/LOOP进行循环操作。请参考：http://dev.mysql.com/doc/refman/5.0/en/repeat-statement.html。 - Hendy Irawan

我不得不说，尽管我上面的评论有所偏见：我不相信存储过程，并谴责它的使用。除非严格需要并且所有其他替代方案（如代码+缓存）被证明更糟，否则存储过程就是邪恶的。 - Hendy Irawan

Answer 3

通过上面@cballou的评论，我能够执行这种函数，虽然不完全是OP所要求的，但适合我的类似情况，因此在此添加以帮助那些之后来的人。

普通选择语句：

SELECT d.id ID,
       q.field field,
       q.quota quota
  FROM defaults d
  JOIN quotas q ON d.id=q.default_id

垂直结果：

ID field  quota
1  male   25
1  female 25
2  male   50

使用 group_concat 的 select 语句：

SELECT d.id ID,
       GROUP_CONCAT(q.fields SEPARATOR ",") fields,
       GROUP_CONCAT(q.quotas SEPARATOR ",") quotas
  FROM defaults d
  JOIN quotas q ON d.id=q.default_id

然后我得到了逗号分隔的“字段”和“定额”，稍后我可以很容易地以编程方式处理。

水平结果：

ID fields      quotas
1  male,female 25,25
2  male        50

神奇！

- Tamzin Blake

Answer 4

以下假设您想要不同（名称/地区）对的匹配。即Luke/CA和Duke/CA将产生两个结果：

SELECT name, District, count(District) AS count
FROM district_details
GROUP BY District, name

如果不是这种情况，只需从GROUP BY子句中删除名称即可。

最后，请注意我将 sum()切换为 count() ，因为您正在尝试计算所有分组行而不是获取值的总和。

- Corey Ballou

---

感谢您的输入，但这不是我想要的。我想显示所有地区所有名称的区域计数。因此，如果对于Luke或Duke，我想获取它们在每个地区的计数，如果在该地区不存在，则为0。此外，将1 n次求和（我在查询中执行）将完成相同的操作，我相信。 - Anirudh Goel