mysql - 两个没有子查询的简单 COUNT 列

Question

1.) 数据模型

我将数据模型大大简化为仅相关部分，如下:

DOCUMENT
  id

DOCUMENT2FILE
  document_id (references DOCUMENT)
  file_id     (references FILE)

FILE
  id
  filetype_id (references FILETYPE)

FILETYPE
  id
  name

2.) 演示数据

假设我们有以下数据(连接表并选择所有内容):

document.id   file.id   filetype.id   filetype.name

1             1         1             PDF
1             2         1             PDF
1             3         2             XML
1             4         2             XML

2             5         1             PDF
2             6         2             XML

3             7         1             PDF

4             8         2             XML

5             NULL      NULL          NULL

所以有 5 个文档，每个文档可以附加多个文件。每个文件都有一种文件类型(仅限 PDF 或 XML)。

3.) 期望的查询结果

现在，我想运行一个查询，该查询输出以下结果集:

document_id   pdf_count   xml_count
1             2           2
2             1           1
3             1           0
4             0           1
5             0           0

4.) 低效的解决方案

我提出了这个查询，它准确地生成了所需的结果:

SELECT
    id,

    (SELECT COUNT(*)
     FROM
         document,
         document2file,
         file,
         filetype
     WHERE document.id = document2file.document_id
     AND document2file.file_id = file.id
     AND file.filetype_id = filetype.id
     AND document.id = document_global.id
     AND filetype.name = "PDF") AS pdf_count,

     (SELECT COUNT(*)
     FROM
         document,
         document2file,
         file,
         filetype
     WHERE document.id = document2file.document_id
     AND document2file.file_id = file.id
     AND file.filetype_id = filetype.id
     AND document.id = document_global.id
     AND filetype.name = "XML") AS xml_count

FROM
    document document_global;

5.) 问题

这个查询工作正常，但是......

有没有一种方法可以在没有这两个子查询的情况下更有效地完成它？有没有办法只用 COUNT、GROUP BY 和 HAVING 来做到这一点？

如果有人能帮助我并教我如何提高效率，我会非常高兴。预先非常感谢您。

Answer 1

我认为你只是想要条件求和:

select d2f.id,
       sum(case when ft.name = 'PDF' then 1 else 0 end) as PDF_count,
       sum(case when ft.name = 'XML' then 1 else 0 end) as XML_count
from document2file d2f join
     file f
     on d2f.file_id = f.id join
     filetype ft
     on f.filetype_id = ft.id
group by d2f.id;