select * from `a` ,`b` where `a`.`bid` like concat('%',`b`.`bid`,'%')如果要查a表id等于1对应的关联数据,可以写成 select * from `a` ,`b` where `a`.`id`='1' and `a`.`bid` like concat('%',`b`.`bid`,'%')
CREATE TEMPORARY TABLE a (id int, bid varchar(10), content varchar(10)); INSERT INTO a VALUES('1','1,5,2','AAAAAAA'); INSERT INTO a VALUES('2','2,4','AAAAAAA'); INSERT INTO a VALUES('3','1,3,2','AAAAAAA'); CREATE TEMPORARY TABLE b (bid int, content varchar(10)); INSERT INTO b VALUES('1','BBBBB'); INSERT INTO b VALUES('2','BBBBB'); INSERT INTO b VALUES('3','BBBBB'); INSERT INTO b VALUES('4','BBBBB'); INSERT INTO b VALUES('5','BBBBB'); select a.id, b.bid as bid, a.content, b.content as bcontent from a, b where find_in_set(b.bid, a.bid) order by a.id
select * from `a` ,`b` where `a`.`id`='1' and `a`.`bid` like concat('%',`b`.`bid`,'%')
{
[id] => 1
[bid] => Object
{
[1] => Object
{
id => 1
content => 'BBBBB'
}
[2] => Object
{
id => 5
content => 'BBBBB'
}
[3] => Object
{
id => 2
content => 'BBBBB'
}
}
conent => 'AAAAA'
}
foreach ($tablea->result as $a)
{
echo $a->id
foreach ($a->bid as $b)
{
echo $b.id;
echo $b.content;
}
echo $a.conent
}
打印结果是:id bid(& b.content) content
------------------------------------
1 1, BBBBBBB AAAAAAAA
1 5, BBBBBBB AAAAAAAA
1 2, BBBBBBB AAAAAAAA
2 2, BBBBBBB AAAAAAAA
2 4, BBBBBBB AAAAAAAA
这是是不是先读b表中的id,然后再模糊查询a表中的bid?
select * from `a` ,`b` where `a`.`bid` like concat('%',`b`.`bid`,'%')
而不是原先a表中bid逗号字符串的顺序:1,5,2——应该是1-5-2的顺序
create table ....; insert into table.... ; 语句
from A表 a, B表 b
where find_in_set(b.id, a.bid)
order by a.id
谢谢版主!
find_in_set(b.id, a.bid)好像只能查询到第一条,比如上面的数据表中a.bid是1,5,2,结果只显示b.id=1的那一条!b.id=5, b.id=2的那几条都没查询出来!
INSERT INTO a VALUES('1','1,5,2','AAAAAAA');
INSERT INTO a VALUES('2','2,4','AAAAAAA');
INSERT INTO a VALUES('3','1,3,2','AAAAAAA');
CREATE TEMPORARY TABLE b (bid int, content varchar(10));
INSERT INTO b VALUES('1','BBBBB');
INSERT INTO b VALUES('2','BBBBB');
INSERT INTO b VALUES('3','BBBBB');
INSERT INTO b VALUES('4','BBBBB');
INSERT INTO b VALUES('5','BBBBB');
select a.id, b.bid as bid, a.content, b.content as bcontent
from a, b
where find_in_set(b.bid, a.bid)
order by a.id
select * from `a` ,`b` where `a`.`bid` like concat('%',`b`.`bid`,'%') order by id