如何在MySQL中统计多列按行分组？

Question

我有两个表，“关键字”和“统计”，想知道每个关键字每个商家有多少结果。所以每个关键字一行。

期望的结果，例如：

KWD    | RESULTS Amazon | RESULTS eBay
test     3                5
second   6                2

表：

create table keywords
(
    ID           mediumint unsigned auto_increment
        primary key,
    KEYWORD      varchar(255)                                                     null
);

create table stats
(
    MERCHANT_ID tinyint unsigned                   not null,
    TYPE_ID     mediumint unsigned                 not null comment 'the ID of the coresponding type. E.g. kw_id from keywords',
    RESULTS     smallint unsigned                  null,
    DATE        date                               not null,
    primary key (DATE, MERCHANT_ID, TYPE_ID)
)
    comment 'How many results does each merchant have per search?';

示例数据：

-- keywords
insert into test.keywords (ID, KEYWORD) values (1, 'testing');
insert into test.keywords (ID, KEYWORD) values (2, 'blablub');

-- stats
insert into test.stats (MERCHANT_ID, TYPE_ID, RESULTS, DATE) values (1, 1, 33, '2021-07-06');
insert into test.stats (MERCHANT_ID, TYPE_ID, RESULTS, DATE) values (1, 2, 3, '2021-07-06');
insert into test.stats (MERCHANT_ID, TYPE_ID, RESULTS, DATE) values (2, 1, 22, '2021-07-06');
insert into test.stats (MERCHANT_ID, TYPE_ID, RESULTS, DATE) values (2, 2, 6, '2021-07-06');

查询：

select
       kwd.KEYWORD,
       mss.MERCHANT_ID,
       mss.RESULTS
from keywords kwd
LEFT JOIN stats mss ON mss.TYPE_ID = kwd.ID
where
    date = 20210705
group by kwd.ID

大约有10个商家。是否有可能获得每个关键字一行，并在单独的列中有每个商家的结果数量？

Answer 1

尝试如下所示：

select
  kwd.KEYWORD,
  SUM(IF(mss.MERCHANT_ID = 'amazon', mss.RESULTS, 0)) as `amazon_sum`,
  SUM(IF(mss.MERCHANT_ID = 'eBay', mss.RESULTS, 0)) as `eBay_sum`
    
from keywords kwd
    LEFT JOIN stats mss ON mss.TYPE_ID = kwd.ID
    where
        date = 20210705
    group by kwd.ID