MySQL 单个 SQL 查询中的多个计数

Multiple counts within a single SQL query

提问by Das123

I'm trying to get the count of documents within 4 specific sections using the following code:

我正在尝试使用以下代码获取 4 个特定部分内的文档数：

SELECT
    category.id
    , category.title
    , count(ts1.section_id) AS doc1
    , count(ts2.section_id) AS doc2
    , count(ts3.section_id) AS doc3
    , count(ts4.section_id) AS doc4
FROM
    category 
    LEFT JOIN category_link_section AS ts1
        ON (category.id = ts1.category_id AND ts1.section_id = 1)
    LEFT JOIN category_link_section AS ts2
        ON (category.id = ts2.category_id AND ts2.section_id = 2)
    LEFT JOIN category_link_section AS ts3
        ON (category.id = ts3.category_id AND ts3.section_id = 3)
    LEFT JOIN category_link_section AS ts4
        ON (category.id = ts4.category_id AND ts4.section_id = 4)
GROUP BY category.id, ts1.section_id, ts2.section_id, ts3.section_id, ts4.section_id

The table 'category' had an id, title etc. The table 'category_link_section' contains id linkages between category_id, section_id, and doc_id.

表“category”有一个id、标题等。表“category_link_section”包含category_id、section_id和doc_id之间的id链接。

If the count is 0 for any column, it displays 0 in that column. But if the result is not 0 it shows the multiplication result of all the section results. So if my 4 count columns were supposed to return: 1, 2, 0, 3; it would actually show 6, 6, 0, 6;

如果任何列的计数为 0，则在该列中显示 0。但如果结果不为 0，则显示所有部分结果的乘法结果。所以如果我的 4 个计数列应该返回：1, 2, 0, 3; 它实际上会显示 6, 6, 0, 6;

If I use this following code for each specific category I get the results I want:

如果我对每个特定类别使用以下代码，我会得到我想要的结果：

SELECT
    category.id
    , category.title
    , count(ts1.section_id) AS doc1
FROM
    category 
    LEFT JOIN category_link_section AS ts1
        ON (category.id = ts1.category_id AND ts1.section_id = 1)
GROUP BY category.id, ts1.section_id

but I then need to cycle through the database each time for each section.

但是我每次都需要为每个部分循环访问数据库。

So my question is, do I need to step through and call each section in turn, constructing my table outside the SQL, or can this be done in a single query?

所以我的问题是，我是否需要遍历并依次调用每个部分，在 SQL 之外构建我的表，还是可以在单个查询中完成？

回答by Bill Karwin

@VoteyDisciple's answeris on the right track, but his query needs some improvements:

@VoteyDisciple 的回答是正确的，但他的查询需要一些改进：

SELECT c.id, c.title,
    SUM(ts1.section_id = 1) AS doc1,
    SUM(ts1.section_id = 2) AS doc2,
    SUM(ts1.section_id = 3) AS doc3,
    SUM(ts1.section_id = 4) AS doc4
FROM category AS c
  LEFT JOIN category_link_section AS ts1
    ON (c.id = ts1.category_id)
GROUP BY c.id;

---

---

Explanations:

说明：

• The IF()expressions are redundant because equality already returns 1 or 0.
• Take the ts1.section_id=1out of the join condition, or you'll never get the other section_idvalues.
• Group by c.idonly. I assume the OP only wants one row per category, and columns for counts of each section_idvalue for the respective category. If the query grouped by c.id, ts1.section_id, then there'd be up to four rows per category.
• Move the commas in the select-list. Commas floating at the start of the line look ugly. ;-)

• 该IF()表达式是多余的，因为平等已经返回1或0。
• 取ts1.section_id=1出来的连接条件，否则你将永远不会得到其他section_id值。
• c.id仅按分组。我假设 OP 只需要每个类别的一行，以及section_id相应类别的每个值的计数的列。如果查询按 分组c.id, ts1.section_id，则每个类别最多有四行。
• 在选择列表中移动逗号。行首浮动的逗号看起来很难看。;-)

回答by VoteyDisciple

You might want to try something like this:

你可能想尝试这样的事情：

SELECT
    category.id
    , category.title
    , SUM(IF(ts1.section_id = 1, 1, 0)) AS doc1
    , SUM(IF(ts1.section_id = 2, 1, 0)) AS doc2
    , SUM(IF(ts1.section_id = 3, 1, 0)) AS doc3
    , SUM(IF(ts1.section_id = 4, 1, 0)) AS doc4
FROM
    category 
    LEFT JOIN category_link_section AS ts1
        ON (category.id = ts1.category_id AND ts1.section_id = 1)
GROUP BY category.id, ts1.section_id