sql 连接表，其中 1 列有逗号
当我搜索new1"时如何获取所有用户名.例如:我应该得到 A 和 B，因为 tblC 中的用户 ID 1,2 是 1,2 的 row1，其中有 new1.我应该使用什么查询来获取以上结果?我真的很感谢任何帮助.提前致谢.
http://sqlfiddle.com/#!2/1ab8e/2
how can I get all usernames when I search "new1" .For eg: I should get A and B as userids 1,2 in tblC is 1,2 for row1 which has new1.What query should I use to get the above result?
I really appreciate any help.Thanks in Advance.
http://sqlfiddle.com/#!2/1ab8e/2
CREATE TABLE if not exists tblA
(
id int(11) NOT NULL auto_increment ,
user varchar(255),
category int(255),
PRIMARY KEY (id)
);
CREATE TABLE if not exists tblB
(
id int(11) NOT NULL auto_increment ,
username varchar(255),
userid int(255),
PRIMARY KEY (id)
);
CREATE TABLE if not exists tblC
(
id int(11) NOT NULL auto_increment ,
nname varchar(255),
userids varchar(255),
PRIMARY KEY (id)
);
INSERT INTO tblA (user, category ) VALUES
('1', '1'),
('1', '2'),
('1', '3'),
('1', '1'),
('2', '1'),
('2', '1'),
('2', '1'),
('2', '1'),
('3', '1'),
('2', '1'),
('4', '1'),
('4', '1'),
('2', '1');
INSERT INTO tblB (userid, username ) VALUES
('1', 'A'),
('2', 'B'),
('3', 'C'),
('4', 'D'),
('5', 'E');
INSERT INTO tblC (id, nname,userids ) VALUES
('1', 'new1','1,2'),
('2', 'new2','1,3'),
('3', 'new3','1,4'),
('4', 'new4','3,2'),
('5', 'new5','5,2');
到目前为止的查询:
select * where nname="new1" from tblC
CROSS JOIN tblB
ON tblB.userid=(SELECT userids FROM substr(tblC.userids,','))
推荐答案
你真的应该看看 数据库规范化，首先通过添加一个连接表来规范您的结构，并保存来自 tablec 的关系存储在 tablec 中的每个关系都将存储在新的连接表中，但不是以逗号分隔的列表，每行将保存c 和每行一个用户 ID，如果您无法更改架构，则可以使用 find_in_set 在集合中查找值
You should really look at Database normalization and first normalize your structure by adding a junction table and holds a relation from tablec each relation stored in tablec will be stored in new junction table but not as comma separated list each row will hold id of c and one user id per row ,if you can't alter your schema you can use find_in_set to find values in set
select *
from tblC c
JOIN tblB b
ON (find_in_set(b.userid,c.userids) > 0)
where c.nname="new1"
查看演示
编辑规范化架构
我已经从您的 tblC 中删除了 userids 列，而是创建了一个新的联结表作为 tblC_user 与 2 列 c_id 这将与 tblC 和第二个 userid 的 id 列相关，用于存储 tblC 的用户关系用户，请参阅 tblC 的示例架构代码>tblC
I have removed userids column from your tblC and instead i have created a new junction table as tblC_user with 2 columns c_id this will related to the id column of tblC and second one userid to store user relations users for tblC see sample schema for tblC
CREATE TABLE if not exists tblC
(
id int(11) NOT NULL auto_increment ,
nname varchar(255),
PRIMARY KEY (id)
);
INSERT INTO tblC (id, nname) VALUES
('1', 'new1'),
('2', 'new2'),
('3', 'new3'),
('4', 'new4'),
('5', 'new5');
这是你的连接表 tblC_user
CREATE TABLE if not exists tblC_user
(
c_id int,
userid int
);
INSERT INTO tblC_user (c_id,userid) VALUES
('1','1'),
('1','2'),
('2','1'),
('2','3'),
('3','1'),
('3','4'),
('4','3'),
('4','2'),
('5','5'),
('5','2');
在上面，如果您注意到我没有存储任何逗号分隔的关系，tblC 的每个用户关系都存储在新行中，对于您关心的结果集，我在 join 中使用了连接表也是新的查询将如下所示
In above if you notice i haven't stored any comma separated relations each relation of user for tblC is stored in new row ,for you concerned result set i have used junction table in join also new query will be like below
select *
from tblC c
join tblC_user cu on(c.id = cu.c_id)
join tblB b on (b.userid = cu.userid)
where c.nname="new1"
演示 2
现在可以使用索引优化上面的查询，您可以轻松维护级联关系
Now above query can can be optimized by using indexes you can maintain cascading relations easily
相关文章