select count(address) from table1 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);
select count(*) 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)
SELECT * FROM table1 t1 JOIN table2 t2 ON (FIND_IN_SET(t2.id,t1.address)) GROUP BY t2.id;这个试下,我测试了下应该和你的要求是差不多的。
我想要的是在table1中再添加一个字段,显示address字段中的字符串,按逗号拆分后,有多少个在table2的select id from table2中 比如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
很抱歉,少加了个条件SELECT COUNT(a.id) a FROM table2 a LEFT JOIN table1 b ON LOCATE(CONCAT(',',a.id ,',') , CONCAT(',',b.address ,',') )>0 WHERE b.address IS NOT NULL
select count(*) from table1 inner join table2 on find_in_set(table2.id,address); 这个是不你需要的
如查询table1表中id=5,结果自然就是0select count(*) from table1 inner join table2 on find_in_set(table2.id,address) where table1.id=5;
SELECT T1.ID, T1.ADDRESS, (SELECT COUNT(*) FROM TABLE2 T2 WHERE ',' || T1.ADDRESS || ',' LIKE '%,' || T2.ID || ',%') QTY FROM TABLE1 T1;
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;