如何规范表中列的逗号分隔值，然后运行查询 [英] How to normalize comma separated values within column of table and then run query

问题描述

假设我有一个具有以下结构的表：

Let's say I have a table with the following structure:

 | column1    |    column2       |
 |------------|------------------|
 | a          |    1,L,3,K,5,    |
 | b          |    R,6,7,8,9     |
 | c          |    8,9,10,D      |
 | d          |    1,2,3,H       |

我们假设column1可以继续通过z，而column2可以继续使用随机数字和字母。我想要一个通用的解决方案，可以应用于任何数量的行和列，以及第2列中的值数量。

Let's say that column1 can potentially continue on through z and that column2 can continue on with random numbers and letters. I would like a general solution that can apply to any number of rows and columns, and number of values in column2.

我想在MySQL中运行一个查询， column2中的所有值，并输出column1中在column2中包含3的字母。输出应为：

I want to run a query in MySQL that will search all the values in column2 and output the letters in column1 that contain a 3 in column2. The output should be:

 | column1    |
 |------------|
 | a          | 
 | d          |  

许多帖子都有直接完成此操作的查询的答案，但我想以正确的方式。我是sql的新手，但我相信这意味着通过创建一个新表，并在这个新表上运行查询来正常化column2中的数据。

Many posts have answers with queries that directly accomplish this, but I would like to do it the correct way. I am new to sql, but I believe that would mean to normalize the data in column2 by creating a new table and running a query on this new table instead.

帮助我的代码规范化并在MySQL中运行此查询？

Can someone help me with the code to normalize and run this query in MySQL? Thanks.

推荐答案

您需要使用 FIND_IN_SET（）

SELECT column1 FROM table WHERE FIND_IN_SET('3', column2);

Fiddle Demo

您应该一个逗号分隔的列表...所以如果我是你，我会考虑将它分成行

You should NEVER store data inside the table as a comma separated list... so if I were you I would consider separating it into rows

来规范你的数据库，你可以做这样的查询...注意你需要知道你在column2中的数量。

to normalize your database you can do a query like this... NOTE you need to know the number of pieces you have in column2..

CREATE TEMPORARY TABLE IF NOT EXISTS normalized_table AS 
(   SELECT
      column1,
      SUBSTRING_INDEX(SUBSTRING_INDEX(column2, ',', n.digit+1), ',', -1) column2
    FROM test
    JOIN(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) n
        ON LENGTH(REPLACE(column2, ',' , '')) <= LENGTH(column2)-n.digit
    ORDER BY column1, n.digit
);

DROP table test;
CREATE table test (column1 varchar(2), column2 varchar(2));
INSERT INTO test (column1, column2) 
SELECT column1, column2 FROM normalized_table;

NORMALIZED RESULTS

每个数字的JOIN是您的钥匙...如果您有6项逗号分隔，然后联合0-5数字加入。

The JOIN with each digit is your key... if you have 6 items comma separated then union 0-5 digits to join on.

如果你不知道有多少，然后只是运行这个来知道有多少数字联合

if you don't know how many there are then just run this to know how many numbers to union

SELECT MAX(LENGTH(REPLACE(column2, ',', ''))) FROM test;

这篇关于如何规范表中列的逗号分隔值，然后运行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！