高级MySQL左联接IFNULL查询
高级MySQL左联接IFNULL查询
提问于 2015-04-30 14:05:06
我正在寻找一个高级MySQL查询的帮助。我的当前查询(如下所示)运行良好。我想添加一个额外的字段，这样我就不必创建单独的查询了。新字段count(TableA.Field05)应该会导致来自TableA的记录总数。
TableA有10条记录
TableB有100个记录
SELECT count(TableB.Answer) AS unAnswered
FROM TableA
LEFT JOIN TableB
ON ( TableA.Field01 = TableB.fkField01 AND TableA.Field02 = TableB.fkField02 AND TableB.Answer = '1')
WHERE TableB.fkField03 IS NULL AND TableA.Field04 = 10
GROUP BY TableA.Field01, TableA.Field02结果：
unAnswered = 8这是正确的
预期的结果是：
unAnswered = 8 count(TableA.Field05) = 10
--
这是数据和结果的一个例子。
SELECT count(TableB.Answer) AS unAnswered
FROM TableA LEFT JOIN TableB ON ( TableA.Field01 = TableB.fkField01 AND TableA.Field02 = TableB.fkField02 AND TableB.Answer = '1')
WHERE TableB.fkField03 IS NULL AND TableA.Field04 = 10
GROUP BY TableA.Field01, TableA.Field02
TableA
Field01 = 1, Field02 = 1, Field03 = 10
Field01 = 1, Field02 = 2, Field03 = 21
Field01 = 1, Field02 = 3, Field03 = 22
Field01 = 1, Field02 = 4, Field03 = 34
TableB
Field01 = 1, Field02 = 1, Answer = 1
Field01 = 1, Field02 = 2, Answer = 1
Field01 = 1, Field02 = 3, Answer = 1
Field01 = 2, Field02 = 1, Answer = 1
Field01 = 2, Field02 = 2, Answer = 1
Field01 = 2, Field02 = 3, Answer = 1
Result
count(TableB.Answer) AS unAnswered = 1
Result trying to achive
count(TableB.Answer) AS unAnswered = 1
count(TableA.Field03) = 4任何帮助都将不胜感激。
Stack Overflow用户
回答已采纳
发布于 2015-04-30 16:46:23
我刚刚想出了我自己的问题。
SELECT **COALESCE(count(DISTINCT TableB.Answer)) AS unAnswered, count(DISTINCT TableA.Field05)**
FROM TableA
LEFT JOIN TableB
ON ( TableA.Field01 = TableB.fkField01 AND TableA.Field02 = TableB.fkField02 AND TableB.Answer = '1')
WHERE TableB.fkField03 IS NULL AND TableA.Field04 = 10
GROUP BY TableA.Field01, TableA.Field02加入聚结和清晰解决了我的问题。谢谢大家的帮助。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/29969931
复制相关文章
相似问题
腾讯云开发者
