MySQL添加联接给出不正确的结果 [英] MySql adding a join gives incorrect results
本文介绍了MySQL添加联接给出不正确的结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我试图在这里联接两个表，所以我根据postStatus获得了特定用户的总行数.没有联接的数据是正确的，但是当我添加联接时，我得到的结果很奇怪.这是怎么回事，在这里使用什么合适的联接?我需要返回两个表中的行(或返回null)，即使其中一个表中不存在userId.
I'm trying to join two tables here, so I get the total rows for a particular user based on postStatus. The data is correct without the join, but when I add a join, I get weird results. How is this happening, and what is the right join to use here? I need rows from both tables returned (or null) even if a userId did not exist in one of them.
**userData**
"postId" "userId" "postStatus" "checked" "postTitle"
"1" "1" "0" "0" "Title 1"
"2" "1" "0" "0" "Title 2"
"3" "1" "0" "0" "Title 3"
"4" "1" "0" "0" "Title 4"
"5" "1" "0" "0" "Title 5"
"6" "1" "1" "0" "Title 6"
"7" "1" "1" "0" "Title 7"
"8" "1" "6" "0" "Title 8"
"9" "1" "5" "0" "Title 9"
"10" "1" "5" "0" "Title 10"
"11" "2" "0" "0" "Title 1"
"12" "2" "0" "0" "Title 2"
"13" "2" "1" "0" "Title 3"
**userDataMod**
"postId" "userId" "postStatus" "checked" "postTitle"
"1" "1" "10" "0" "Title 11"
"2" "1" "10" "0" "Title 12"
"3" "2" "0" "0" "Title 4"
带有连接的SQL
select
a.userId,
sum(a.postStatus = 0) published,
sum(a.postStatus = 1) inactive,
sum(a.postStatus = 5) recalled,
sum(a.postStatus = 6) deleted,
sum(b.postStatus = 10) unChecked
from userData a join userdatamod b on a.userId = b.userId where a.userId = 1;
// what is the correct join to use here that'll return data (null) from both tables
// even when a userId may not be present in one of them?
错误的结果(请参见下面的结果.这些是正确的结果)
"userId" "published" "inactive" "recalled" "deleted" "unChecked"
"1" "10" "4" "4" "2" "20"
没有连接的Sql会给出正确的结果
select
a.userId,
sum(a.postStatus = 0) published,
sum(a.postStatus = 1) inactive,
sum(a.postStatus = 5) recalled,
sum(a.postStatus = 6) deleted
from userData a where a.userId = 1;
不使用联接时更正结果
"userId" "published" "inactive" "recalled" "deleted"
"1" "5" "2" "2" "1"
//Note: there are 5 rows with postStatus 0 in userData, and 2 with postStaus = 1 and
1 with postsStaus = 6 and so on for userId = 1
正确解决此问题的正确方法是什么?
What is the right way to get this right?
使用连接时的预期结果
"userId" "published" "inactive" "recalled" "deleted" "unChecked"
"1" "5" "2" "2" "1" "2"
//unChecked = the 2 rows from userDataMod with postStatus = 10 for useId = 1
詹斯
"postId" "userId" "postStatus" "checked" "postTitle" "postId" "userId" "postStatus" "checked" "postTitle"
"1" "1" "0" "0" "Title 1" "1" "1" "10" "0" "Title 1"
"1" "1" "0" "0" "Title 1" "2" "1" "10" "0" "Title 2"
"2" "1" "0" "0" "Title 2" "1" "1" "10" "0" "Title 1"
"2" "1" "0" "0" "Title 2" "2" "1" "10" "0" "Title 2"
"3" "1" "0" "0" "Title 3" "1" "1" "10" "0" "Title 1"
"3" "1" "0" "0" "Title 3" "2" "1" "10" "0" "Title 2"
"4" "1" "0" "0" "Title 4" "1" "1" "10" "0" "Title 1"
"4" "1" "0" "0" "Title 4" "2" "1" "10" "0" "Title 2"
"5" "1" "0" "0" "Title 5" "1" "1" "10" "0" "Title 1"
"5" "1" "0" "0" "Title 5" "2" "1" "10" "0" "Title 2"
"6" "1" "1" "0" "Title 6" "1" "1" "10" "0" "Title 1"
"6" "1" "1" "0" "Title 6" "2" "1" "10" "0" "Title 2"
"7" "1" "1" "0" "Title 7" "1" "1" "10" "0" "Title 1"
"7" "1" "1" "0" "Title 7" "2" "1" "10" "0" "Title 2"
"8" "1" "6" "0" "Title 8" "1" "1" "10" "0" "Title 1"
"8" "1" "6" "0" "Title 8" "2" "1" "10" "0" "Title 2"
"9" "1" "5" "0" "Title 9" "1" "1" "10" "0" "Title 1"
"9" "1" "5" "0" "Title 9" "2" "1" "10" "0" "Title 2"
"10" "1" "5" "0" "Title 10" "1" "1" "10" "0" "Title 1"
"10" "1" "5" "0" "Title 10" "2" "1" "10" "0" "Title 2"
userData
CREATE TABLE `userdata` (
`postId` INT(10) NOT NULL AUTO_INCREMENT,
`userId` INT(10) NULL DEFAULT '0',
`postStatus` INT(10) NULL DEFAULT '0',
`checked` INT(10) NULL DEFAULT '0',
`postTitle` VARCHAR(50) NULL DEFAULT '0',
PRIMARY KEY (`postId`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=14;
userDataMod
CREATE TABLE `userdatamod` (
`postId` INT(10) NOT NULL AUTO_INCREMENT,
`userId` INT(10) NULL DEFAULT '0',
`postStatus` INT(10) NULL DEFAULT '0',
`checked` INT(10) NULL DEFAULT '0',
`postTitle` VARCHAR(50) NULL DEFAULT '0',
PRIMARY KEY (`postId`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
ROW_FORMAT=DYNAMIC
AUTO_INCREMENT=4;
推荐答案
另一种可能的解决方案:
Another possible solution:
select
a.userId,
sum(a.postStatus = 0) published,
sum(a.postStatus = 1) inactive,
sum(a.postStatus = 5) recalled,
sum(a.postStatus = 6) deleted,
b.unChecked
from userData a left outer join
(select userId, sum(poststatus = 10) unChecked
from userDataMod
group by userId)
b on a.userId = b.userId
where a.userId = 1;
这篇关于MySQL添加联接给出不正确的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
