如何编写查询以比较具有逗号分隔值的列？

I am Using Joomla 2.5. I am using MYSQL database. I have a table job_field with the following columns:

我正在使用Joomla 2.5。我正在使用MYSQL数据库。我有一个表job_field,其中包含以下列:

cat_id  |  location_id
-----------------------
 1,4    |   66,70

I need to compare it with another table job_value

我需要将它与另一个表job_value进行比较

cat_id  |  location_id | name
--------------------------------
 1      |   70         | Atul
 4      |   70,80      | Amit
 4      |   80,66      | Amol
 1      |   66         | Pritam    
 3      |   70         | Rahul
 2      |   66,90      | Ajit
 1      |   74         | Raju
 4      |   65,22      | Manoj

I want output to compare cat_id and location_id columns from first table job_details with second table job_value columns cat_id and location_id.

我希望输出将第一个表job_details中的cat_id和location_id列与第二个表job_value列cat_id和location_id进行比较。

And it will check each value from 1st table (job_details) that is location_id column value (66, 70) separately with 2nd table (job_value) location_id column. I will get output array as

并且它将与第二个表(job_value)location_id列分别检查第一个表(job_details)中的每个值,即location_id列值(66,70)。我将输出数组作为

  Array (
    1 70     Atul
    4 70,80  Amit
    4 80,66  Amol
    1 66     Pritam
 )

2 个解决方案

#1

Try this ::

尝试这个 ::

SELECT 
* 
FROM table1
JOIN table2  ON FIND_IN_SET(table1.location_id, table2.location_id) > 0

You can also refer Comma separated values in MySQL "IN" clause

您还可以在MySQL“IN”子句中引用逗号分隔值

#2

This is a _badbad^bad structure. Even if the problem can be solved, it shouldn't be solved. It will be slow, and unmaintainable.

这是一个badbadbad结构。即使问题可以解决,也不应该解决。它会很慢,而且不可维护。

Instead of the poor structure, something along these lines should be created for this part of the DB:

而不是糟糕的结构,应该为这部分DB创建沿着这些线的东西:

CREATE TABLE PERSON (
    person_id BIGINT,
    name VARCHAR(64),
    PRIMARY KEY (person_id)
);

CREATE TABLE LOCATION (
    location_id BIGINT,
    name VARCHAR(64),
    PRIMARY KEY (location_id)
);
CREATE TABLE CAT (
    cat_id BIGINT,
    name VARCHAR(64),
    PRIMARY KEY (cat_id)
);

CREATE TABLE CAT_LOCATION (
    cat_id BIGINT,
    location_id BIGINT,
    PRIMARY KEY (cat_id,location_id),
    FOREIGN KEY (cat_id) REFERENCES cat(cat_id),
    FOREIGN KEY (location_id) REFERENCES location(location_id)
);

CREATE TABLE CAT_LOCATION_PERSON (
    cat_id BIGINT,
    location_id BIGINT,
    person_id BIGINT,
    PRIMARY KEY (cat_id,location_id,person_id),
    FOREIGN KEY (cat_id) REFERENCES cat(cat_id),
    FOREIGN KEY (location_id) REFERENCES location(location_id),
    FOREIGN KEY (person_id) REFERENCES person(person_id)
);

And then it is easier than easy to get what you want by doing simple joins:

然后通过简单的连接获得你想要的东西比简单容易:

SELECT cl.cat_id, cl.location_id, p.name
FROM CAT_LOCATION cl 
JOIN CAT_LOCATION_PERSON clp on cl.cat_id = clp.cat_id and cl.location_id=clp.location_id
JOIN PERSON p on clp.person_id = p.person_id

(I refuse to write a query that would provide the output in the specified format, with numeric values separated by comma... (though it is possible through MySQL's GROUP_CONCAT functionality easily))

(我拒绝编写一个以指定格式提供输出的查询,数字值用逗号分隔......(尽管可以通过MySQL的GROUP_CONCAT功能轻松实现))

#1