.. 谢谢楼上的指点 问题补充mysql> select * from a; +----+--------+----------+ | id | touser | fromuser | +----+--------+----------+ | 1 | 1/abc | a | | 2 | 2/a1 | a | | 3 | 3/b1 | a | +----+--------+----------+ mysql> select * from b; +----+--------+------+ | id | number | name | +----+--------+------+ | 1 | 2 | a1 | | 2 | 3 | b1 | +----+--------+------+ 问题 如何查出这条数据 +----+--------+----------+ | id | touser | fromuser | +----+--------+----------+ | 1 | 1/abc | a |
mysql> select * from a where touser-fromuser not in (select number from b); +------+--------+----------+ | id | touser | fromuser | +------+--------+----------+ | 1 | 1/abc | a | +------+--------+----------+
select * from a where not exists (select 1 from b where a.touser like concat(b.number,'/','%'));
where SUBSTR(A.touser, 1, INSTR(A.touser, '/') - 1) not in B.number试试看行不?
+----+--------+----------+
| id | touser | fromuser |
+----+--------+----------+
| 1 | 1/abc | abc |
| 4 | 1/abc | abc |
| 5 | 2/a1 | abc |
+----+--------+----------+mysql> select * from b;
+----+--------+------+
| id | number | name |
+----+--------+------+
| 2 | 2 | a1 |
| 3 | 3 | aaav |
+----+--------+------+mysql> select * from a where touser-fromuser not in (select number from b);
+----+--------+----------+
| id | touser | fromuser |
+----+--------+----------+
| 1 | 1/abc | abc |
| 4 | 1/abc | abc |
+----+--------+----------+
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式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 a;
+----+--------+----------+
| id | touser | fromuser |
+----+--------+----------+
| 1 | 1/abc | a |
| 2 | 2/a1 | a |
| 3 | 3/b1 | a |
+----+--------+----------+
mysql> select * from b;
+----+--------+------+
| id | number | name |
+----+--------+------+
| 1 | 2 | a1 |
| 2 | 3 | b1 |
+----+--------+------+
问题 如何查出这条数据
+----+--------+----------+
| id | touser | fromuser |
+----+--------+----------+
| 1 | 1/abc | a |
+------+--------+----------+
| id | touser | fromuser |
+------+--------+----------+
| 1 | 1/abc | a |
+------+--------+----------+
where not exists (select 1 from b where a.touser like concat(b.number,'/','%'));