解决方案 »
- mysql主从同步master.info保存的信息
- mysql 如何锁表? 让其它进程不能访问表
- load data local infile "d:\\中文.txt" into table p4 提示找不到数据,非中文就可以
- C#如何实现安装MYSQL
- ・ 雜ゞ℡ 飛 ,这些字符如何在mysql里正常显示?
- 求一select语句:如何实现MySQl的递归查询
- 用MySql-Front建表,如何建立一个有默认当前时间的字段?
- 如果一个字段有多个数据
- 请问 LEFT JOIN 跟 LEFT OUTER JOIN 有什么不同,请举例。 (MySQL)
- NUMA 在Mysql 调优的问题
- sqlite3一次插入多条记录出错
- delete表连接不支持limit
where address in(select id from table2);
create table table1 (
id int,
address varchar(255)
);create table table2 (
id int);insert into table1 values (1,'1,2');
insert into table1 values (2,'2,3');
insert into table1 values (3,'1,4');
insert into table1 values (4,'1,3');
insert into table1 values (5,'4,5');insert into table2 values (1);
insert into table2 values (2);
insert into table2 values (3);
from (
select id,substring_index(address,',',1) as add
from table1
union all
select id,substring_index(address,',',-1) as add
from table1
)T
where T.add in (select id from table2)
*
FROM table1 t1
JOIN table2 t2 ON (FIND_IN_SET(t2.id,t1.address))
GROUP BY t2.id;这个试下,我测试了下应该和你的要求是差不多的。
比如1,2按逗号拆分后1在select id from table2中,2也在select id from table2中,则应该显示2.
再比如4,5按逗号拆分后4不在select id from table2中,5也不在select id from table2,则应该显示0.
再比如1,3,4应该显示2。
CREATE TABLE table1 (
id INT,
address VARCHAR(255)
);CREATE TABLE table2 (
id INT);INSERT INTO table1 VALUES (1,'1,2,3');
INSERT INTO table1 VALUES (2,'2,3');
INSERT INTO table1 VALUES (3,'1,4');
INSERT INTO table1 VALUES (4,'1,3');
INSERT INTO table1 VALUES (5,'4,5');INSERT INTO table2 VALUES (1);
INSERT INTO table2 VALUES (2);
INSERT INTO table2 VALUES (3);
----------------------SELECT a.id,b.address,b.id, CONCAT(',',a.id ,',') a , CONCAT(',',b.address ,',') b
FROM table2 a
LEFT JOIN table1 b ON LOCATE(CONCAT(',',a.id ,',') , CONCAT(',',b.address ,',') )>0查询的值前后一定要加',',否则有 11,12 查 1 时就有问题了SELECT COUNT(a.id) a
FROM table2 a
LEFT JOIN table1 b ON LOCATE(CONCAT(',',a.id ,',') , CONCAT(',',b.address ,',') )>0
FROM table2 a
LEFT JOIN table1 b ON LOCATE(CONCAT(',',a.id ,',') , CONCAT(',',b.address ,',') )>0
WHERE b.address IS NOT NULL
这个是不你需要的
T1.ADDRESS,
(SELECT COUNT(*)
FROM TABLE2 T2
WHERE ',' || T1.ADDRESS || ',' LIKE '%,' || T2.ID || ',%') QTY
FROM TABLE1 T1;