sql连接表，其中1列具有逗号 [英] sql join tables where 1 column has comma

问题描述

当我搜索"new1"时，如何获取所有用户名.例如:我应该以tblC中的userid 1,2为A和B来获取具有new1的row1的1,2.以上结果? 非常感谢您的帮助.谢谢您.
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中的每个关系将存储在新联结表中，但不会以逗号分隔的列表形式存储，而每行将保留ID为c和每行一个用户ID，如果您无法更改架构，则可以使用find_in_set在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的id列和第二个存储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

And here is your junction table as 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的每个用户关系都存储在新行中，对于您关心的结果集，我在联接中使用了联结表，新查询也将如下所示

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

这篇关于sql连接表，其中1列具有逗号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！