为什么使用子查询时我的结果重复? [英] Why is my result duplicated when using subquery?
问题描述
请记住,我对MySQL非常陌生。
我试图选择一组结果时卡住。
从tableOne A选择A.userNum,tableTwo B其中A.userNum!= B.userNum;
(表A包含用户的用户号码和电话号码,表B包含用户的地址簿。
ResultSet包含多组相似的结果。每个结果都省略了我在where子句中指定的一个userNum。 (请参阅此问题的结尾)
如何获取ResultSet只包含一个结果并满足A.userNum!= B.userNum子句? p>
这里有一个特殊的情况,我遇到了麻烦。
说我有两个表,用户和地址簿。
表'adressbook'基本上是一个通讯录。表'users'是包含用户电话号码的表。
我创建了表。为了简单起见,userNum和phone号在users表中相同。
创建表用户$ b userNum int,
phone char(11)
);
insert into users values(1,'1');
insert into users values(2,'2');
insert into users values(3,'3');
insert into users values(4,'4');
insert into users values(5,'5');
insert into users values(6,'6');
insert into users values(7,'7');
insert into users values(8,'8');
insert into users values(9,'9');
insert into users values(10,'10');
表地址簿(
userNum int,
phone char(11)
);
插入到地址簿值(1,'2');
插入到地址簿值(1,'3');
插入到地址簿值(2,'3');
插入到地址簿值(3,'4');
插入到地址簿值(5,'7');
插入到地址簿值(5,'8');
插入到地址簿值(6,'7');
插入到地址簿值(7,'8');
插入到地址簿值(7,'9');
插入到地址簿值(9,'10');
接下来,我将发现'userNum'我的朋友的朋友。让我们说我是userNum 1。
首先,我找到了我的朋友的userNum。
从用户A选择A.userNum,地址簿B其中B.userNum = 1和B.phone = A.phone;
然后使用这个查询,找到我朋友的朋友的userNum。
从用户A,地址簿B选择A.userNum(从用户A选择A.userNum,地址簿B选择B.userNum = 1和B. phone = A.phone)C
其中C.userNum = B.userNum和A.phone = B.phone;
结果是3,4。 (奇怪,当我使用子查询时,ResultSet在这里不重复。)
问题从这里开始。
使用上面的选择规则作为子查询B,我试图获取不是我的朋友的朋友的'userNum'。
从用户A,
选择A.userNum(从用户A,地址簿B选择A.userNum(从用户A选择A.userNum, userNum = 1 and B.phone = A.phone)C其中C.userNum = B.userNum和A.phone = B.phone)B
其中A.userNum!= B.userNum;
结果
code> userNum
1
2
4
5
6
7
8
9
10
1
2
3
5
6
7
8
9
10
从结果中可以看出,有两组结果。第一个结果省略userNum 3,第二个userNum 4。
如何获取一组省略userNum 3,4的用户号码?
< 用户A,地址簿B,用户A,用户A,用户A,用户A (从用户A选择A.userNum,地址簿B,其中B.userNum = 1和B.phone = A.phone)C,其中C.userNum = B.userNum和A.phone = B.phone);
Please bear in mind that I am very new to MySQL.
I am stuck when I try to select a set of results.
select A.userNum from tableOne A, tableTwo B where A.userNum!=B.userNum;
(Table A contains userNum and phone number of a user. Table B contains addressbook of a user.)
The ResultSet contains multiple sets of similar results. Each result omits one userNum that I specified in the where clause. (see the end of this question)
How can I get ResultSet that only contains one result with fulfilling "where A.userNum!=B.userNum" clause?
Here's a specific case that I am having trouble.
Say I have two tables, users and addressbook.
Table 'adressbook' is basically an address book. Table 'users' is a table containing an user's phone number.
I created tables. For simplicity, the 'userNum' and 'phone' number is same in 'users' table.
create table users (
userNum int,
phone char(11)
);
insert into users values (1,'1');
insert into users values (2,'2');
insert into users values (3,'3');
insert into users values (4,'4');
insert into users values (5,'5');
insert into users values (6,'6');
insert into users values (7,'7');
insert into users values (8,'8');
insert into users values (9,'9');
insert into users values (10,'10');
create table addressbook (
userNum int,
phone char(11)
);
insert into addressbook values(1,'2');
insert into addressbook values(1,'3');
insert into addressbook values(2,'3');
insert into addressbook values(3,'4');
insert into addressbook values(5,'7');
insert into addressbook values(5,'8');
insert into addressbook values(6,'7');
insert into addressbook values(7,'8');
insert into addressbook values(7,'9');
insert into addressbook values(9,'10');
Next, I will find out 'userNum' of my friend's friend. Let's say I am userNum 1.
First, I found the 'userNum' of my friend.
select A.userNum from users A, addressbook B where B.userNum=1 and B.phone=A.phone;
then using this query, I found the 'userNum' of my friend's friend.
select A.userNum from users A, addressbook B, (select A.userNum from users A, addressbook B where B.userNum=1 and B.phone=A.phone) C
where C.userNum=B.userNum and A.phone=B.phone;
The result is 3,4. (Strange, the ResultSet here does not duplicate when I use subquery.)
The problem starts here.
Using the select statment above as the subquery B, I tried to get 'userNum' of people who's not a friend's friend of mine.
select A.userNum from users A,
(select A.userNum from users A, addressbook B, (select A.userNum from users A, addressbook B where B.userNum=1 and B.phone=A.phone) C where C.userNum=B.userNum and A.phone=B.phone) B
where A.userNum!=B.userNum;
The result
userNum
1
2
4
5
6
7
8
9
10
1
2
3
5
6
7
8
9
10
As you can see from the result, there are two sets of results. First result omits userNum 3, the second userNum 4.
How can I get one set of userNum that omits userNum 3,4?
select A.userNum from users A where A.userNum NOT IN (select A.userNum from users A, addressbook B, (select A.userNum from users A, addressbook B where B.userNum=1 and B.phone=A.phone) C where C.userNum=B.userNum and A.phone=B.phone) ;
这篇关于为什么使用子查询时我的结果重复?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!