我们如何模拟返回多个表达式的MySQL INTERSECT查询？
由于我们无法在MySQL中使用INTERSECT查询，因此我们将使用EXIST运算符来模拟INTERSECT查询。通过以下示例可以理解-
示例
在此示例中，我们有两个表，即Student_detail和Student_info，具有以下数据-
mysql> Select * from Student_detail; +-----------+---------+------------+------------+ | studentid | Name | Address | Subject | +-----------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | Literature | | 130 | Ram | Jhansi | Computers | | 132 | Shyam | Chandigarh | Economics | | 133 | Mohan | Delhi | Computers | | 150 | Rajesh | Jaipur | Yoga | | 160 | Pradeep | Kochi | Hindi | +-----------+---------+------------+------------+ 7 rows in set (0.00 sec) mysql> Select * from Student_info; +-----------+-----------+------------+-------------+ | studentid | Name | Address | Subject | +-----------+-----------+------------+-------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | Literature | | 130 | Ram | Jhansi | Computers | | 132 | Shyam | Chandigarh | Economics | | 133 | Mohan | Delhi | Computers | | 165 | Abhimanyu | Calcutta | Electronics | +-----------+-----------+------------+-------------+ 6 rows in set (0.00 sec)
现在，下面的查询使用带有WHERE子句的EXIST运算符将模拟INTERSECT返回两个表中都存在的'studentid'，Name，Address，其中名称不是'Yashpal'-
mysql>Select Student_detail.studentid,Student_detail.name, student_detail.address FROM student_detail WHERE Student_detail.studentid >100 AND EXISTS (SELECT * FROM Student_info WHERE Student_info.Name <> 'Yashpal' AND Student_info.studentid = Student_detail.studentid AND Student_info.name = Student_detail.name); +-----------+--------+------------+ | studentid | name | address | +-----------+--------+------------+ | 105 | Gaurav | Chandigarh | | 130 | Ram | Jhansi | | 132 | Shyam | Chandigarh | | 133 | Mohan | Delhi | +-----------+--------+------------+ 4 rows in set (0.00 sec)
热门推荐
10 学生节祝福语大全简短
11 生日祝福语大全女孩简短
12 祝福语大全简短6个
13 女朋友考试祝福语简短
14 短祝福语简短暖心
15 女儿生日祝福语说说简短
16 杨奕老师祝福语简短
17 关于开车的祝福语简短
18 国庆简短幽默祝福语