mysql - 连接 2 个表和嵌套选择以对同一字段的不同列进行计数

Question

我尝试了几个小时但没有成功......

我的问题如下:我需要连接 2 个表，比如说 Persons 和 Documents。在 Documents 表中，我有一个 document_type 列，其中包含不同类型的文档(即护照、驾驶执照)。每个人可以拥有多次相同的文件(即护照的 2 倍)。

我想通过一个复杂的查询来实现这个结果:每个 Person 仅显示 1 行，每个 document_type 显示一列，其中显示每个人的 count()文档类型。

PERSON|PASSPORT|DRIVING_LICENCE|SCHOOL_BADGE
Mark  |2       |1              |0
Lisa  |1       |0              |1

但我就是做不到。我实现的唯一一件事就是获得类似一个人的东西以及该文档类型的所有文档的计数，例如:

PERSON|PASSPORT

Mark  |4

这是简单的架构:

CREATE TABLE `Persons` 
(
    `id` int(11) NOT NULL,
    `person` varchar(255) COLLATE utf8_unicode_ci NOT NULL
) 

CREATE TABLE `Documents` 
(
    `id` int(11) NOT NULL,
    `project_id` int(11) NOT NULL,
    `document_type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `document_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL
) 

SELECT 
    p.person,
    COUNT(d.document_type) AS passport
FROM
    Persons p 
LEFT JOIN 
    Documents d ON p.id = d.person_id
WHERE
    d.document_type = 'passport'

预期结果是:

PERSON|PASSPORT|DRIVING_LICENCE|SCHOOL_BADGE
Mark  |2       |1              |0
Lisa  |1       |0              |1

但我不知道如何为所有文档类型复制上述查询并将所有内容合并到一个大查询中。

我吓坏了!请你帮助我好吗？非常感谢

Answer 1

使用条件聚合:

select p.person,
       sum(case when d.document_type = 'passport' then 1 else 0 end) as num_passport,    
       sum(case when d.document_type = 'driving license' then 1 else 0 end) as num_driving_license,
       sum(case when d.document_type = 'school badge' then 1 else 0 end) as num_school_badge
from Persons p left join
     Documents d 
     on p.id = d.person_id
group by p.person;

在 MySQL 中，您可以将其缩短为:

select p.person,
       sum(d.document_type = 'passport') as num_passport,    
       sum(d.document_type = 'driving license') as num_driving_license,
       sum(d.document_type = 'school badge') as num_school_badge
from Persons p left join
     Documents d 
     on p.id = d.person_id
group by p.person;