不关联的数据:什么标准SELECT t.tid FROM A tt LEFT JOIN B t ON t.tid=tt.tid where t.tid is null;
mysql> SELECT * FROM T1 A JOIN T2 B ON A.ID<>B.ID
恩,类似这种SELECT * FROM A WHERE tid not in(select tid from B)的另外一种写法 需要效率高点的,因为数据量比较大
恩 试过了,SELECT tt.* FROM A tt LEFT JOIN B t ON t.tid=tt.tid where t.tid is null 是Empty set (0.00 sec)SELECT * FROM A WHERE tid not in(select tid from B) 查询的结果应该是有一条记录的
A表 tid subject create table A(tid int(6) NOT NULL AUTO_INCREMENT UNIQUE,subject VARCHAR(20)); INSERT INTO A(subject) VALUES('a'); INSERT INTO A(subject) VALUES('b'); INSERT INTO A(subject) VALUES('c'); INSERT INTO A(subject) VALUES('d'); SELECT * FROM A; threads +-----+---------+ | tid | subject | +-----+---------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | +-----+---------+B表 tid namecreate table B(tid int(6),name VARCHAR(20)); INSERT INTO B(tid,name) VALUES(1,'aa'); INSERT INTO B(tid,name) VALUES(1,'bb'); INSERT INTO B(tid,name) VALUES(1,'cc'); INSERT INTO B(tid,name) VALUES(1,'dd'); INSERT INTO B(tid,name) VALUES(2,'q'); INSERT INTO B(tid,name) VALUES(2,'w'); SELECT * FROM B; threadtags +------+------+ | tid | name | +------+------+ | 1 | aa | | 1 | bb | | 1 | cc | | 1 | dd | | 2 | q | | 2 | w | +------+------+ SELECT * FROM A WHERE tid not in(select tid from B); +-----+---------+ | tid | subject | +-----+---------+ | 3 | c | | 4 | d | +-----+---------+ 我想获取的效果就是上面这个。 这个是按照大家给出的答案写的: SELECT t.* FROM A t LEFT JOIN B tt ON t.tid=tt.tid where t.tid is null; mysql> SELECT t.* FROM A t LEFT JOIN B tt ON t.tid=tt.tid where t.tid is null; +-----+---------+ | tid | subject | +-----+---------+ | 4 | d | +-----+---------+ 1 row in set (0.00 sec)
一楼的语句的结果不就是你所需要的吗? 难道是楼主从来没看过一楼的回复?mysql> SELECT tt.* FROM A tt LEFT JOIN B t ON t.tid=tt.tid -> where t.tid is null; +-----+---------+ | tid | subject | +-----+---------+ | 3 | c | | 4 | d | +-----+---------+ 2 rows in set (0.05 sec)mysql>
SELECT t.* FROM A t LEFT JOIN B tt ON t.tid=tt.tid WHERE tt.tid IS NULL;
SELECT tt.* FROM A tt LEFT JOIN B t ON t.tid=tt.tid
where t.tid is null
(不要高估你的汉语表达能力或者我的汉语理解能力)
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
mysql> SELECT * FROM T1 A JOIN T2 B ON A.ID<>B.ID
需要效率高点的,因为数据量比较大
where t.tid is null
是Empty set (0.00 sec)SELECT * FROM A WHERE tid not in(select tid from B)
查询的结果应该是有一条记录的
tid
subject
create table A(tid int(6) NOT NULL AUTO_INCREMENT UNIQUE,subject VARCHAR(20));
INSERT INTO A(subject) VALUES('a');
INSERT INTO A(subject) VALUES('b');
INSERT INTO A(subject) VALUES('c');
INSERT INTO A(subject) VALUES('d');
SELECT * FROM A; threads
+-----+---------+
| tid | subject |
+-----+---------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+-----+---------+B表
tid
namecreate table B(tid int(6),name VARCHAR(20));
INSERT INTO B(tid,name) VALUES(1,'aa');
INSERT INTO B(tid,name) VALUES(1,'bb');
INSERT INTO B(tid,name) VALUES(1,'cc');
INSERT INTO B(tid,name) VALUES(1,'dd');
INSERT INTO B(tid,name) VALUES(2,'q');
INSERT INTO B(tid,name) VALUES(2,'w');
SELECT * FROM B; threadtags
+------+------+
| tid | name |
+------+------+
| 1 | aa |
| 1 | bb |
| 1 | cc |
| 1 | dd |
| 2 | q |
| 2 | w |
+------+------+
SELECT * FROM A WHERE tid not in(select tid from B);
+-----+---------+
| tid | subject |
+-----+---------+
| 3 | c |
| 4 | d |
+-----+---------+
我想获取的效果就是上面这个。
这个是按照大家给出的答案写的:
SELECT t.* FROM A t LEFT JOIN B tt ON t.tid=tt.tid where t.tid is null;
mysql> SELECT t.* FROM A t LEFT JOIN B tt ON t.tid=tt.tid where t.tid is null;
+-----+---------+
| tid | subject |
+-----+---------+
| 4 | d |
+-----+---------+
1 row in set (0.00 sec)
难道是楼主从来没看过一楼的回复?mysql> SELECT tt.* FROM A tt LEFT JOIN B t ON t.tid=tt.tid
-> where t.tid is null;
+-----+---------+
| tid | subject |
+-----+---------+
| 3 | c |
| 4 | d |
+-----+---------+
2 rows in set (0.05 sec)mysql>