SELECT * FROM A INNER JOIN B ON INSTR(A.phone,B.phone)>0 INNER JOIN F ON A.ID=F.ID
数据多时如果表A的其中一条跟表B的多条记录都like上了,只算做一条,值打印出1条表A的所有信息。
mysql> select * from ta; +----+------+-----------------------------------+ | id | name | phone | +----+------+-----------------------------------+ | 1 | 张三 | 89-25-468749 | | 2 | 李四 | 89-25-468749 | | 3 | 王五 | 25-2165468 | | 4 | 中牛 | 89-25-216879,216879,13701546318 | | 5 | 你猜 | 0089-25-84613284 | +----+------+-----------------------------------+ 5 rows in set (0.00 sec)mysql> select * from tf; +----+------+ | id | age | +----+------+ | 1 | 50 | +----+------+ 1 row in set (0.00 sec)mysql> select * from tb; +----+----------+ | id | phone | +----+----------+ | 1 | 468749 | | 2 | 216879 | | 3 | 84613284 | +----+----------+ 3 rows in set (0.00 sec)mysql> select ta.id,ta.name,ta.phone,tf.age -> from ta inner join tb on instr(ta.phone,tb.phone) -> left join tf on ta.id=tf.id; +----+------+-----------------------------------+------+ | id | name | phone | age | +----+------+-----------------------------------+------+ | 1 | 张三 | 89-25-468749 | 50 | | 2 | 李四 | 89-25-468749 | NULL | | 4 | 中牛 | 89-25-216879,216879,13701546318 | NULL | | 5 | 你猜 | 0089-25-84613284 | NULL | +----+------+-----------------------------------+------+ 4 rows in set (0.00 sec)mysql>
提供测试数据有时也很重要,尽量能提供全面的测试用例这样可以防止特例。mysql> select distinct ta.id,ta.name,ta.phone,tf.age -> from ta inner join tb on instr(ta.phone,tb.phone) -> left join tf on ta.id=tf.id -> ; +----+------+-----------------------------------+------+ | id | name | phone | age | +----+------+-----------------------------------+------+ | 1 | 张三 | 89-25-468749 | 50 | | 2 | 李四 | 89-25-468749 | NULL | | 4 | 中牛 | 89-25-216879,216879,13701546318 | NULL | | 5 | 你猜 | 0089-25-84613284 | NULL | +----+------+-----------------------------------+------+ 4 rows in set (0.00 sec)mysql>
从你们给的结果 看是可以了不过我改成实际SQL的时候 报错是在ORACLE上运行的
报错后 光标落在 left join 的lift 上
我把instr(ta.phone,tb.phone) 加了个>0 就没报错了但我漏说了个条件 就是ta 的个字段type属性必须是>0的我在最后 加个 and ta.type>0 这样可以吗
SQL> select * from ta; ID NAME PHONE ---------- -------- ---------------------------------------- 1 张三 89-25-468749 2 李四 89-25-468749 3 王五 25-2165468 4 中牛 89-25-216879,216879,13701546318 5 你猜 0089-25-84613284SQL> select * from tf; ID AGE ---------- ---------- 1 50SQL> select * from tb; ID PHONE ---------- ------------------------------ 1 468749 2 216879 3 84613284SQL> select distinct ta.id,ta.name,ta.phone,tf.age 2 from ta inner join tb on instr(ta.phone,tb.phone)>0 3 left join tf on ta.id=tf.id; ID NAME PHONE AGE ---------- -------- ---------------------------------------- ---------- 5 你猜 0089-25-84613284 2 李四 89-25-468749 1 张三 89-25-468749 50 4 中牛 89-25-216879,216879,13701546318SQL>
在你的表ta中找不到这个type字段。当然不行。所以举例时请举清楚,说明你的数据库,表结构,否则是浪费别人时间!select distinct ta.id,ta.name,ta.phone,tf.age from ta inner join tb on instr(ta.phone,tb.phone)>0 left join tf on ta.id=tf.id where ta.type>0
Soryy不加distinct可以很快出结果 但数量就.............但加distinct SQL 跑了5分钟都出不来啊.........如果我现在只要取 ta 的 id 这个SQL 可不可以缩减成 select distinct ta.id from ta inner join tb on instr(ta.phone,tb.phone)>0where ta.type>0
实际中 ta表 type>0的 有4千多tb表 有6W多 这SQL 能再快些吗
如果只需要 ta 的 id 可以 select distinct ta.id from ta inner join tb on instr(ta.phone,tb.phone)>0 where ta.type>0
id-----------name---------phone
1------------张三----------89-25-468749
2------------李四----------89-25-468749
3------------王五----------25-2165468
4------------中牛----------89-25-216879,216879,13701546318
5------------你猜----------0089-25-84613284表F
id-----------age
1------------50表B
id---------phone
1-----------468749
2-----------216879
3-----------84613284表A 与表B phone存在相同 有1245 这4条
把这4条所有信息打印出来结果
表A.ID,表A.name,表A.phone,表F.age1------------张三----------89-25-468749--------------------------20
2------------李四----------89-25-468749------------------------null
4------------中牛----------89-25-216879,216879,13701546318----null
5------------你猜----------0089-25-84613284--------------------null
INNER JOIN F ON A.ID=F.ID
+----+------+-----------------------------------+
| id | name | phone |
+----+------+-----------------------------------+
| 1 | 张三 | 89-25-468749 |
| 2 | 李四 | 89-25-468749 |
| 3 | 王五 | 25-2165468 |
| 4 | 中牛 | 89-25-216879,216879,13701546318 |
| 5 | 你猜 | 0089-25-84613284 |
+----+------+-----------------------------------+
5 rows in set (0.00 sec)mysql> select * from tf;
+----+------+
| id | age |
+----+------+
| 1 | 50 |
+----+------+
1 row in set (0.00 sec)mysql> select * from tb;
+----+----------+
| id | phone |
+----+----------+
| 1 | 468749 |
| 2 | 216879 |
| 3 | 84613284 |
+----+----------+
3 rows in set (0.00 sec)mysql> select ta.id,ta.name,ta.phone,tf.age
-> from ta inner join tb on instr(ta.phone,tb.phone)
-> left join tf on ta.id=tf.id;
+----+------+-----------------------------------+------+
| id | name | phone | age |
+----+------+-----------------------------------+------+
| 1 | 张三 | 89-25-468749 | 50 |
| 2 | 李四 | 89-25-468749 | NULL |
| 4 | 中牛 | 89-25-216879,216879,13701546318 | NULL |
| 5 | 你猜 | 0089-25-84613284 | NULL |
+----+------+-----------------------------------+------+
4 rows in set (0.00 sec)mysql>
-> from ta inner join tb on instr(ta.phone,tb.phone)
-> left join tf on ta.id=tf.id
-> ;
+----+------+-----------------------------------+------+
| id | name | phone | age |
+----+------+-----------------------------------+------+
| 1 | 张三 | 89-25-468749 | 50 |
| 2 | 李四 | 89-25-468749 | NULL |
| 4 | 中牛 | 89-25-216879,216879,13701546318 | NULL |
| 5 | 你猜 | 0089-25-84613284 | NULL |
+----+------+-----------------------------------+------+
4 rows in set (0.00 sec)mysql>
这样可以吗
---------- -------- ----------------------------------------
1 张三 89-25-468749
2 李四 89-25-468749
3 王五 25-2165468
4 中牛 89-25-216879,216879,13701546318
5 你猜 0089-25-84613284SQL> select * from tf; ID AGE
---------- ----------
1 50SQL> select * from tb; ID PHONE
---------- ------------------------------
1 468749
2 216879
3 84613284SQL> select distinct ta.id,ta.name,ta.phone,tf.age
2 from ta inner join tb on instr(ta.phone,tb.phone)>0
3 left join tf on ta.id=tf.id; ID NAME PHONE AGE
---------- -------- ---------------------------------------- ----------
5 你猜 0089-25-84613284
2 李四 89-25-468749
1 张三 89-25-468749 50
4 中牛 89-25-216879,216879,13701546318SQL>
在你的表ta中找不到这个type字段。当然不行。所以举例时请举清楚,说明你的数据库,表结构,否则是浪费别人时间!select distinct ta.id,ta.name,ta.phone,tf.age
from ta inner join tb on instr(ta.phone,tb.phone)>0
left join tf on ta.id=tf.id
where ta.type>0
select distinct ta.id from ta inner join tb on instr(ta.phone,tb.phone)>0where ta.type>0
这SQL 能再快些吗
select distinct ta.id
from ta inner join tb on instr(ta.phone,tb.phone)>0
where ta.type>0