MySQL 如何在SQL表中根据条件选择列值作为列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22587600/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select Column value as Column name with conditions in SQL table
提问by Den
I'm new to SQL. I have a table 'Customers' and it looks like this.
我是 SQL 的新手。我有一张“客户”表，它看起来像这样。
I would like to select 'Gender' AS Gender Column and 'Age' as Age column which would be like this.
我想选择“性别”作为性别列和“年龄”作为年龄列，就像这样。
I've tried several ways but it still doesn't show what I need. Please help me.
我已经尝试了几种方法，但它仍然没有显示我需要什么。请帮我。
回答by peterm
One way to go about it is to use conditional aggregation
一种方法是使用条件聚合
SELECT name,
MAX(CASE WHEN field = 'Gender' THEN value END) gender,
MAX(CASE WHEN field = 'Age' THEN value END) age
FROM customers
GROUP BY name
The other way (if you're interested only in these two columns) would be
另一种方式（如果您只对这两列感兴趣）将是
SELECT c1.name, c1.value gender, c2.value age
FROM customers c1 JOIN customers c2
ON c1.name = c2.name
AND c1.field = 'Gender'
AND c2.field = 'Age';
Assumption is that both Gender and Age exist for each Name. It it's not the case then use an OUTER JOINinstead of an INNER JOINlike so
假设每个名称都存在性别和年龄。事实并非如此，然后使用一个OUTER JOIN而不是一个INNER JOIN像这样
SELECT n.name, c1.value gender, c2.value age
FROM
(
SELECT DISTINCT name
FROM customers
) n LEFT JOIN customers c1
ON n.name = c1.name AND c1.field = 'Gender'
LEFT JOIN customers c2
ON n.name = c2.name AND c2.field = 'Age';
Output:
输出：
| NAME | GENDER | AGE | |--------|--------|-----| | Angela | Female | 28 | | Davis | Male | 30 |
Here is SQLFiddledemo
这是SQLFiddle演示
回答by grantmcconnaughey
I have not tested this, but give something like this a try:
我还没有测试过这个，但试试这样的东西：
Select c.Name,
(Select c2.Value from customers c2 Where c2.Name = c.Name And c2.Field = 'Gender') as Gender,
(Select c2.Value from customers c2 Where c2.Name = c.Name And c2.Field = 'Age') as Age
From Customers c
Group By c.Name
PS I apologize for the awful formatting...had to type this from my phone.
PS 我为糟糕的格式道歉......不得不从我的手机上输入。
回答by Victor Zhang
You can use SHOW COLUMNS FROM Customersto get the name of the columns
您可以使用SHOW COLUMNS FROM Customers来获取列的名称
Then just use SELECT * FROM Customersto retrieve the data
然后只是SELECT * FROM Customers用来检索数据
