mysql查询动态将行数据转换为列
我正在研究数据透视表查询。 架构如下
Sno、名称、地区
相同的名称可能会出现在许多地区，例如以样本数据为例
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
但是，地区可能会增加，在这种情况下，我将不得不再次手动编辑查询并添加新区就到了。
我想知道是否有一个查询可以动态获取不同地区的名称并运行上述查询。我知道我可以通过一个过程并动态生成脚本来做到这一点，还有其他方法吗？
我这么问是因为查询的输出“从district_details中选择不同的(地区)”强>”将返回一个单列，每行都有地区名称，我希望将其转置到该列。
I am working on a pivot table query.
The schema is as follows
Sno, Name, District
The same name may appear in many districts eg take the sample data for example
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
As you see i have a set of 4 distinct districts (CA, JB, MN, LP). Now i wanted to get the pivot table generated for it by mapping the name against districts
Name CA JB MN LP
Mike 3 0 0 1
Proctor 0 2 2 0
Luke 0 0 3 0
i wrote the following query for this
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
However there is a possibility that the districts may increase, in that case i will have to manually edit the query again and add the new district to it.
I want to know if there is a query which can dynamically take the names of distinct districts and run the above query. I know i can do it with a procedure and generating the script on the fly, is there any other method too?
I ask so because the output of the query "select distinct(districts) from district_details" will return me a single column having district name on each row, which i will like to be transposed to the column.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您根本无法让静态 SQL 语句返回可变数量的列。每次不同地区的数量发生变化时，您都需要构建这样的语句。为此，您首先执行 a
这将为您提供包含详细信息的地区列表。然后，您构建一个 SQL 语句，迭代先前的结果（伪代码）
并执行该查询。然后，您需要在代码中处理可变列数的处理
You simply cannot have a static SQL statement returning a variable number of columns. You need to build such statement each time the number of different districts changes. To do that, you execute first a
This will give you the list of districts where there are details. You then build a SQL statement iterating over the previous result (pseudocode)
And execute that query. You'll then need have to handle in your code the processing of the variable number of columns
a) MySQL 存储过程不支持“For every”。
b) 存储过程不能使用所谓的动态 SQL 语句从串联字符串执行准备好的语句，也不能返回具有多个不同行的结果。
c) 存储函数根本无法执行动态SQL。
一旦你有了一个好主意，跟踪它就是一场噩梦，而每个人似乎都在思考“为什么有人想要……”之前就揭穿了它，
我希望你找到你的解决方案，我仍在寻找我的解决方案。
我得到的关闭是
（请原谅伪代码）
->对于存储过程，构建函数...
1) 创建临时表
2）使用 if 语句将数据从列加载到临时表
3）将临时表加载到存储过程中的 INOUT 或 OUT 参数，就像表调用一样...如果您可以让它返回多行
还有另一个提示...
将您的地区存储为表格常规样式，加载它并通过循环标记为活动的地区来进行迭代，以动态连接一个查询字符串，该查询字符串可以是所有系统关心的纯文本
然后使用；
从 @yourqyerstring 准备 stmName；
执行stmName；
释放准备stmName；
（也可以在 mysql 论坛的存储过程部分找到更多信息）
每次运行一组不同的分区，而不必重新设计原始过程
也许以数字形式更容易。
我处理表格中的纯文本内容，没有任何内容需要求和、计数或相加
a) "For each " is not supported in MySQL stored procedures.
b) Stored procedures cannot execute prepared statements from concatenated strings using so called dynamic SQL statements, nor can it return results with more than One distinct row.
c) Stored functions cannot execute dynamic SQL at all.
It is a nightmare to keep track of once you got a good idea and everyone seems to debunk it before they think "Why would anyone wanna..."
I hope you find your solution, I am still searching for mine.
The closes I got was
(excuse the pseudo code)
-> to stored procedure, build function that...
1) create temp table
2) load data to temp table from columns using your if statements
3) load the temp table out to INOUT or OUT parameters in a stored procedure as you would a table call... IF you can get it to return more than one row
Also another tip...
Store your districts as a table conventional style, load this and iterate by looping through the districts marked active to dynamically concatenate out a querystring that could be plain text for all the system cares
Then use;
prepare stmName from @yourqyerstring;
execute stmName;
deallocate prepare stmName;
(find much more on the stored procedures part of the mysql forum too)
to run a different set of districts every time, without having to re-design your original proc
Maybe it's easier in numerical form.
I work on plain text content in my tables and have nothing to sum, count or add up
以下假设您需要不同（名称/地区）对的匹配。即 Luke/CA 和 Duke/CA 将产生两个结果：
如果不是这种情况，只需从 GROUP BY 子句中删除名称即可。
最后，请注意，当您尝试对所有分组行进行计数而不是获取值的总和时，我将 sum() 切换为 count()。
The following assumes you want matches of distinct (name/district) pairs. I.e. Luke/CA and Duke/CA would yield two results:
If this is not the case simply remove name from the GROUP BY clause.
Lastly, notice that I switched sum() for count() as you are trying to count all of the grouped rows rather than getting a summation of values.
通过上面@cballou的评论，我能够执行这种功能，这并不完全是OP所要求的，但适合我的类似情况，因此将其添加到此处以帮助后来者。
正常选择语句：
垂直结果：
使用 group_concat 选择语句：
然后我得到“字段”和“配额”的逗号分隔字段，然后我可以轻松地以编程方式处理它们。
横向结果：
神奇！
Via comment by @cballou above, I was able to perform this sort of function which is not exactly what OP asked for but suited my similar situation, so adding it here to help those who come after.
Normal select statement:
Vertical results:
Select statement using group_concat:
Then I get comma-separated fields of "fields" and "quotas" which I can then easily process programmatically later.
Horizontal results:
Magic!