mysql - 那么 SQL 时如何使用 case 呢？

Question

我正在尝试从表中计算每月每天有多少笔交易。我有这样的查询:

SELECT namarss,
case(tanggal_daftar) when '2016-06-01' then count(namarss) else '' end as "01",
case(tanggal_daftar) when '2016-06-02' then count(namarss) else '' end as "02",
case(tanggal_daftar) when '2016-06-03' then count(namarss) else '' end as "03",
case(tanggal_daftar) when '2016-06-04' then count(namarss) else '' end as "04",
case(tanggal_daftar) when '2016-06-05' then count(namarss) else '' end as "05",
case(tanggal_daftar) when '2016-06-06' then count(namarss) else '' end as "06",
case(tanggal_daftar) when '2016-06-07' then count(namarss) else '' end as "07",
case(tanggal_daftar) when '2016-06-08' then count(namarss) else '' end as "08",
case(tanggal_daftar) when '2016-06-09' then count(namarss) else '' end as "09",
case(tanggal_daftar) when '2016-06-10' then count(namarss) else '' end as "10",
case(tanggal_daftar) when '2016-06-11' then count(namarss) else '' end as "11",
case(tanggal_daftar) when '2016-06-12' then count(namarss) else '' end as "12",
case(tanggal_daftar) when '2016-06-13' then count(namarss) else '' end as "13",
case(tanggal_daftar) when '2016-06-14' then count(namarss) else '' end as "14",
case(tanggal_daftar) when '2016-06-15' then count(namarss) else '' end as "15",
case(tanggal_daftar) when '2016-06-16' then count(namarss) else '' end as "16",
case(tanggal_daftar) when '2016-06-17' then count(namarss) else '' end as "17",
case(tanggal_daftar) when '2016-06-18' then count(namarss) else '' end as "18",
case(tanggal_daftar) when '2016-06-19' then count(namarss) else '' end as "19",
case(tanggal_daftar) when '2016-06-20' then count(namarss) else '' end as "20",
case(tanggal_daftar) when '2016-06-21' then count(namarss) else '' end as "21",
case(tanggal_daftar) when '2016-06-22' then count(namarss) else '' end as "22",
case(tanggal_daftar) when '2016-06-23' then count(namarss) else '' end as "23",
case(tanggal_daftar) when '2016-06-24' then count(namarss) else '' end as "24",
case(tanggal_daftar) when '2016-06-25' then count(namarss) else '' end as "25",
case(tanggal_daftar) when '2016-06-26' then count(namarss) else '' end as "26",
case(tanggal_daftar) when '2016-06-27' then count(namarss) else '' end as "27",
case(tanggal_daftar) when '2016-06-28' then count(namarss) else '' end as "28",
case(tanggal_daftar) when '2016-06-29' then count(namarss) else '' end as "29",
case(tanggal_daftar) when '2016-06-30' then count(namarss) else '' end as "30",
case(tanggal_daftar) when '2016-06-31' then count(namarss) else '' end as "31"
FROM mcutrpendaftaran
group by namarss, tanggal_daftar

结果是这样的:

但我希望结果显示如下:

我应该做什么来修复它？谢谢:)

Answer 1

只需从 GROUP BY 子句中删除字段 tanggal_daftar 即可:

SELECT namarss,
       case when tanggal_daftar = '2016-06-01' then 1 end as "01",
       case when tanggal_daftar = '2016-06-02' then 1 end as "02",
       case when tanggal_daftar = '2016-06-03' then 1 end as "03",
       case when tanggal_daftar = '2016-06-04' then 1 end as "04",
       case when tanggal_daftar = '2016-06-05' then 1 end as "05",
       ...

FROM mcutrpendaftaran
group by namarss

上面的查询应该为每个 namasss 值提供一条记录。