使用大小写将列值转换为mysql中的行值 [英] Using case to convert column values to row values in mysql

问题描述

我试图在mysql查询中使用case语句将行转换为列。我在这里看到过类似的其他问题，但由于某种原因我无法使用它。这里是示例原始数据。

I am trying to use case statements in a mysql query to convert rows to columns. I have seen other questions like this here, but I can't get it to work for some reason. Here is example raw data.

last_name  question_number  result
Jones        1               correct
Jones        2               correct
Jones        3               incorrect
Jones        4               correct
Smith        1               incorrect
Smith        2               correct
Smith        3               correct
Smith        4               incorrect

Here is what I want to end up with

last_name    Q1         Q2         Q3         Q4
Jones        correct    correct    incorrect  correct
Smith        incorrect  correct    correct    incorrect

以下是设置和我正在尝试的内容......

Here is the set-up, and what I was trying...

drop table if exists test_case;
create table test_case (
    last_name varchar(30),
    question_number int,
    result varchar(30)
);

insert into test_case 
values ('Jones',1,'correct'), ('Jones',2,'correct'), ('Jones',3,'incorrect'), ('Jones',4,'correct'),
('Smith',1,'incorrect'), ('Smith',2,'correct'), ('Smith',3,'correct'), ('Smith',4,'incorrect');

select * from test_case;

select 
    last_name,
    case
        when question_number = 1 then result
        else null
    end as Q1,
    case
        when question_number = 2 then result
        else ''
    end as Q2,
    case
        when question_number = 3 then result
        else ''
    end as Q3,
    case
        when question_number = 4 then result
        else ''
    end as Q4
from
    test_case
group by last_name;

然而我回来的是这个...

What I get back however is this...

 last_name    Q1         Q2         Q3         Q4
Jones        correct    
Smith        incorrect  

推荐答案

您需要在查询中使用聚合函数：

You need aggregation functions in your query:

select last_name,
       max(case when question_number = 1 then result end) as Q1,
       max(case when question_number = 2 then result end) as Q2,
       max(case when question_number = 3 then result end) as Q3,
       max(case when question_number = 4 then result end) as Q4
from test_case
group by last_name;

在大多数数据库中，您的查询版本无法编译。你会得到一个错误，其结果是 question_number 不在组中，由子句。 MySQL允许使用这种语法，但只是为单行返回任意值。 max（）确保处理所有行。

In most databases, your version of the query would not compile. You would get an error to the effect that question_number is not in the group by clause. MySQL allows the syntax, but then just returns arbitrary values for a single row. The max() makes sure that all the rows are processed.

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