mysql-从联接表中写入两个计数的JOIN查询
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql-从联接表中写入两个计数的JOIN查询,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含1860字,纯文字阅读大概需要3分钟。
内容图文
![mysql-从联接表中写入两个计数的JOIN查询](/upload/InfoBanner/zyjiaocheng/884/db42702bd88e48f79ff598fdb0700e54.jpg)
我花了几个小时来解决这个问题,但是我无法胜任工作.希望有人可以提供帮助?
我有一个项目表和任务表,链接在project_id上.我可以通过以下查询获取project_id,project_name和status_id:
SELECT
a.project_id,
a.project_name,
b.status_id
FROM project_list as a
INNER JOIN task_list as b
ON a.project_id=b.project_id
我想为每个项目选择一条记录,并根据status_id添加两个计数字段.用伪代码:
SELECT
a.project_id,
a.project_name,
(SELECT COUNT(*) FROM task_list WHERE status_id < 3) as not_completed,
(SELECT COUNT(*) FROM task_list WHERE status_id = 3) as completed
FROM project_list as a
INNER JOIN task_list as b
ON a.project_id=b.project_id
GROUP BY project_id
我的创建表脚本如下:
CREATE TABLE `project_list` (
`project_id` int(11) NOT NULL AUTO_INCREMENT,
`topic_id` int(11) DEFAULT NULL,
`project_name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`project_id`)
)
CREATE TABLE `task_list` (
`task_id` int(11) NOT NULL AUTO_INCREMENT,
`project_id` int(11) DEFAULT NULL,
`task_name` varchar(45) DEFAULT NULL,
`status_id` int(11) DEFAULT '0',
PRIMARY KEY (`task_id`)
)
任何帮助深表感谢.谢谢!
编辑:答案:
SELECT
a.project_id,
project_name,
SUM(status_id != 3) AS not_completed,
SUM(status_id = 3) AS completed,
SUM(status_id IS NOT NULL) as total
FROM tasks.project_list as a
INNER JOIN tasks.task_list as b
ON a.project_id=b.project_id
GROUP BY a.project_id
解决方法:
问题在于,在子查询中,您正在计算整个表中的所有行,而不仅仅是具有正确project_id的行.您可以通过修改每个子查询中的WHERE子句来解决此问题.
(SELECT COUNT(*)
FROM task_list AS c
WHERE c.status_id < 3
AND a.project_id = c.project_id)
但是,一种更简单的方法是对布尔条件使用SUM而不是COUNT来计算与条件匹配的行:
SELECT
a.project_id,
a.project_name,
SUM(b.status_id < 3) AS not_completed,
SUM(b.status_id = 3) AS completed,
FROM project_list as a
INNER JOIN task_list as b
ON a.project_id = b.project_id
GROUP BY project_id
这是可行的,因为TRUE的值为1,而FALSE的值为0.
内容总结
以上是互联网集市为您收集整理的mysql-从联接表中写入两个计数的JOIN查询全部内容,希望文章能够帮你解决mysql-从联接表中写入两个计数的JOIN查询所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。