SELECT * FROM table1 WHERE id IN (SELECT id FROM table2); SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);然而,在很多情況下,你可以重寫查詢,而不用子選擇: SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id; SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL對于更複雜的子查詢,通常你可以創建臨時的表保存子查詢。
想了一下, 楼主的问题可以这么解决:select name,id,address,link_mode,mobile from T_company left join t_user on id = cid where not (ctype = 1 and cflag = 1); 以下是验证 mysql> select * from t_co; +-------+---------+-------+ | cname | ctel | id | +-------+---------+-------+ | Co 1 | 1234567 | 00001 | | Co 5 | 1234567 | 00005 | | Co 2 | 34567 | 00002 | | Co 3 | 234567 | 00003 | | Co 4 | 4567 | 00004 | +-------+---------+-------+ 5 rows in set (0.01 sec)mysql> select * from t_user; +-------+-------+-------+ | cid | ctype | cflag | +-------+-------+-------+ | 00001 | 1 | 0 | | 00002 | 1 | 1 | | 00003 | 1 | 1 | | 00004 | 1 | 0 | | 00005 | 1 | 1 | +-------+-------+-------+ 5 rows in set (0.00 sec)mysql> select t_co.* from t_co left join t_user -> on t_co.id = t_user.cid -> where -> not (ctype = 1 and cflag = 1) -> ; +-------+---------+-------+ | cname | ctel | id | +-------+---------+-------+ | Co 1 | 1234567 | 00001 | | Co 4 | 4567 | 00004 | +-------+---------+-------+ 2 rows in set (0.00 sec)
可以使用临时表: create temporary table if not exists tmptable1 select name,id,address,link_mode,mobile from T_company create temporary table if not exists tmptable2 select cid from T_user where type=1 and flog=1select * from tmptable1 A,tmptable2 B where A.id<>B.cid
SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);然而,在很多情況下,你可以重寫查詢,而不用子選擇: SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL對于更複雜的子查詢,通常你可以創建臨時的表保存子查詢。
on id = cid
where
not (ctype = 1 and cflag = 1);
以下是验证
mysql> select * from t_co;
+-------+---------+-------+
| cname | ctel | id |
+-------+---------+-------+
| Co 1 | 1234567 | 00001 |
| Co 5 | 1234567 | 00005 |
| Co 2 | 34567 | 00002 |
| Co 3 | 234567 | 00003 |
| Co 4 | 4567 | 00004 |
+-------+---------+-------+
5 rows in set (0.01 sec)mysql> select * from t_user;
+-------+-------+-------+
| cid | ctype | cflag |
+-------+-------+-------+
| 00001 | 1 | 0 |
| 00002 | 1 | 1 |
| 00003 | 1 | 1 |
| 00004 | 1 | 0 |
| 00005 | 1 | 1 |
+-------+-------+-------+
5 rows in set (0.00 sec)mysql> select t_co.* from t_co left join t_user
-> on t_co.id = t_user.cid
-> where
-> not (ctype = 1 and cflag = 1)
-> ;
+-------+---------+-------+
| cname | ctel | id |
+-------+---------+-------+
| Co 1 | 1234567 | 00001 |
| Co 4 | 4567 | 00004 |
+-------+---------+-------+
2 rows in set (0.00 sec)
create temporary table if not exists tmptable1
select name,id,address,link_mode,mobile from T_company create temporary table if not exists tmptable2
select cid from T_user where type=1 and flog=1select * from tmptable1 A,tmptable2 B where A.id<>B.cid