将行转换为mysql中的列 [英] convert rows to columns in mysql

问题描述

我在数据库中有一个表，看起来像:

I have a table in database that looks like:

我正在尝试创建查询，结果如下:

I am trying to create query which gives me result like:

当我在论坛中搜索时，我确实找到了有关使用聚合函数和/或使用预定义语句将行转换为列的信息.从示例中我发现我确实尝试了以下查询，但是它们不起作用我不明白它们是如何工作的，所以我只是复制查询.我确实尝试在其中使用我的数据:

And when I was searching trough the forum I did found information about converting rows to columns using aggregate function and/or using predefined statement. From example what I did found I did try following queries however they doesn't work I don't understand how they work so I simply copy the query. I did try to use my data in it:

select fk_playerID as name, roundID as roundNo, score
from(
roundID,
CASE WHEN roundID = 1 THEN score END AS 1,
CASE WHEN roundID = 2 THEN score END AS 2,
CASE WHEN roundID = 3 THEN score END AS 3,
from cup
) cup group by fk_playerID

和第二个:

SELECT fk_playerID as name, roundID as roundNo, score
MAX(CASE WHEN'roundID' = 1, THEN score end) roundNo1,
MAX(CASE WHEN'roundID' = 2, THEN score end) roundNo2,
MAX(CASE WHEN'roundID' = 3, THEN score end) roundNo3
FROM cup
ORDER BY fk_playerID

最后我的问题是我的查询必须是什么样子，我还需要解释一下它是如何工作的.

And finally my question is how my query must look like and also I need a little explanation how it works.

推荐答案

第二个就差不多了:

SELECT c.fk_playerID,
       p.Name
       MAX(CASE WHEN c.roundID = 1 THEN c.score END) AS roundNo1,
       MAX(CASE WHEN c.roundID = 2 THEN c.score END) AS roundNo2,
       MAX(CASE WHEN c.roundID = 3 THEN c.score END) AS roundNo3
FROM cup c
JOIN Player p on c.fk_playerID = p.ID
GROUP BY c.fk_playerID, p.Name

您正在按 fk_playerID 分组.让我们考虑 player = 1.这个

You are grouping by fk_playerID. Lets consider player = 1. This

CASE WHEN roundID = 1 THEN score END 

将为 score 生成以下集合:{1, null, null}.Max 将返回 1.

will produce the following set for score: {1, null, null}. Max will return 1.

CASE WHEN roundID = 2 THEN score END 

将为 score 生成以下集合:{null, 1, null}.Max 将返回 1.

will produce the following set for score: {null, 1, null}. Max will return 1.

CASE WHEN roundID = 3 THEN score END 

将为 score 生成以下集合:{null, null, 1}.Max 将返回 1.

will produce the following set for score: {null, null, 1}. Max will return 1.

player = 19 也一样.

这篇关于将行转换为mysql中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！