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.
表“类别”有一个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个count列应该返回: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之外构造表,还是可以在单个查询中完成?
2 个解决方案
#1
26
@VoteyDisciple's answer is on the right track, but his query needs some improvements:
@ votey门徒的回答是正确的,但是他的问题需要改进:
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. - IF()表达式是冗余的,因为等式已经返回1或0。
- Take the
ts1.section_id=1
out of the join condition, or you'll never get the othersection_id
values. - 壹空间。section_id=1脱离join条件,否则永远不会得到其他section_id值。
- Group by
c.id
only. I assume the OP only wants one row per category, and columns for counts of eachsection_id
value for the respective category. If the query grouped byc.id, ts1.section_id
, then there'd be up to four rows per category. - 集团由c。id。我假设OP只希望每个类别有一行,每个类别的section_id值的计数要有列。如果查询按c分组。id,壹空间。section_id,然后每个类别最多有4行。
- Move the commas in the select-list. Commas floating at the start of the line look ugly. ;-)
- 移动选择列表中的逗号。开头的逗号看起来很难看。:-)
#2
6
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
#1
26
@VoteyDisciple's answer is on the right track, but his query needs some improvements:
@ votey门徒的回答是正确的,但是他的问题需要改进:
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. - IF()表达式是冗余的,因为等式已经返回1或0。
- Take the
ts1.section_id=1
out of the join condition, or you'll never get the othersection_id
values. - 壹空间。section_id=1脱离join条件,否则永远不会得到其他section_id值。
- Group by
c.id
only. I assume the OP only wants one row per category, and columns for counts of eachsection_id
value for the respective category. If the query grouped byc.id, ts1.section_id
, then there'd be up to four rows per category. - 集团由c。id。我假设OP只希望每个类别有一行,每个类别的section_id值的计数要有列。如果查询按c分组。id,壹空间。section_id,然后每个类别最多有4行。
- Move the commas in the select-list. Commas floating at the start of the line look ugly. ;-)
- 移动选择列表中的逗号。开头的逗号看起来很难看。:-)
#2
6
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