是否可以像这样进行查询？

I have a table like this on mySQL db:

我在mySQL db上有这样的表:

Olimpiade     Sport        Disciplina   Categoria
---------------------------------------------------
London 2012   Athletics    100m         men
London 2012   Athletics    100m         woman
Beijing 2008  Athletics    200m         men
Beijing 2008  Athletics    200m         women
Athens 2004   Athletics    800m         men
Athens 2004   Athletics    800m         women

and so on. I don't know if I could set up a query like this, but what I would like to get is a result as follow:

等等。我不知道我是否可以设置这样的查询,但我想得到的结果如下:

Disciplina   Categoria   London 2012    Beijing 2008   Athens 2004
------------------------------------------------------------------
100m         men         yes            no             yes
100m         women       yes            yes            no
200m         men         yes            no             yes
200m         women       yes            yes            no
800m         men         yes            yes            yes
800m         women       yes            yes            yes

that is if the trial is present or not for that olympics edition.

那就是奥林匹克版的试验是否存在。

2 个解决方案

#1

Yes you can, use CASE like this,

是的你可以,像这样使用CASE,

Select Disciplina, Categoria, 
CASE when Olimpiade = 'London 2012' then 'yes' else 'no' end as 'London 2012', 
CASE when Olimpiade = 'Beijing 2008' then 'yes' else 'no' end as 'Biejing 2008',
CASE when Olimpiade = 'Athens 2004' then 'yes' else 'no' end as 'Athens 2004'    from tableName group by Disciplina, Categoria order by Disciplina, Categoria

#2

You can build a query with an inner select. The inner select prepares data by adding the columns. The outer select groups the data:

您可以使用内部选择构建查询。内部选择通过添加列来准备数据。外部选择对数据进行分组:

SELECT
    Disciplina,
    Categoria,
    IF (MAX(`London 2012`) > 0, 'yes', 'no') AS 'London 2012',
    IF (MAX(`Beijing 2008`) > 0, 'yes', 'no') AS 'Beijing 2008',
    IF (MAX(`Athens 2004`) > 0, 'yes', 'no') AS 'Athens 2004'
FROM
    (
        SELECT 
            Disciplina, 
            Categoria, 
            IF (Olimpiade = 'London 2012', 1, 0) AS 'London 2012',
            IF (Olimpiade = 'Beijing 2008', 1, 0) AS 'Beijing 2008',
            IF (Olimpiade = 'Athens 2004', 1, 0) AS 'Athens 2004'
        FROM YourTableName
    ) AS Games
GROUP BY Disciplina, Categoria
ORDER BY Disciplina, Categoria

You have to replace YourTableName with the name of your table.

您必须使用表的名称替换YourTableName。

#1