SQL查询内部联接的值为0 [英] SQL query inner join with 0 values
本文介绍了SQL查询内部联接的值为0的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这张桌子:
idSection | idQuestion | title | enunciation | idScale
1 | 1 | title 1 | question 1 | 3
1 | 1 | title 1 | question 1 | 3
1 | 1 | title 1 | question 1 | 3
1 | 1 | title 1 | question 1 | 2
1 | 1 | title 1 | question 1 | 5
1 | 2 | title 2 | question 2 | 1
1 | 2 | title 2 | question 2 | 3
1 | 3 | title 3 | question 3 | 1
并有此表:
idScale | name
1 | Very Bad
2 | Bad
3 | Good
4 | Very Good
5 | Excellent
我想要一张这样的桌子:
I wanted a table like this:
idSection | idQuestion | title | enunciation | Total | Name
1 | 1 | title 1 | question 1 | 0 | Very Bad
1 | 1 | title 1 | question 1 | 0 | Bad
1 | 1 | title 1 | question 1 | 3 | Good
1 | 1 | title 1 | question 1 | 0 | Very Good
1 | 1 | title 1 | question 1 | 1 | Excellent
1 | 2 | title 2 | question 2 | 0 | Very Bad
1 | 2 | title 2 | question 2 | 1 | Bad
1 | 2 | title 2 | question 2 | 3 | Good
1 | 2 | title 2 | question 2 | 0 | Very Good
1 | 2 | title 2 | question 2 | 0 | Excellent
查询:
SELECT
t1.idSection, t1.idQuestion, t1.title, t1.enunciation,
COUNT(t1.idScale) as Total, t2.name
FROM
table1 AS t1
INNER JOIN
table2 as t2 ON t2.idScale = t1.idScale
GROUP BY
t1.idSection, t1.idQuestion, t1.title, t1.enunciation, t2.name
其结果是查询:
idSection | idQuestion | title | enunciation | Total | Name
1 | 1 | title 1 | question 1 | 3 | Good
1 | 1 | title 1 | question 1 | 1 | Excellent
1 | 2 | title 2 | question 2 | 1 | Bad
1 | 2 | title 2 | question 2 | 3 | Good
此问题是不会出现查询值为0的情况.
The problem with this is that query values that are 0 don't appear.
推荐答案
我认为您正在寻找:
SELECT
t1.idSection,
t1.idQuestion,
t1.title,
t1.enunciation,
SUM(case when t1.idScale=t2.idScale then 1 else 0 end) as Total,
t2.name
FROM
table1 AS t1, table2 as t2
GROUP BY t1.idSection, t1.idQuestion, t1.title, t1.enunciation, t2.name, t2.idScale
ORDER BY t1.idSection, t1.idQuestion, t2.idScale
这不是INNER JOIN,而是笛卡尔式联接(table1的每一行都与table2的每一行相乘).我正在使用SUM来计算INNER JOIN成功的行数.
this is not an INNER JOIN, but it's a cartesian join instead (every row of table1 is multiplied for every row of table2). I'm using SUM to count the rows where the INNER JOIN would have succeeded.
这篇关于SQL查询内部联接的值为0的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文