PHP PDO SQlite单个查询，用于查找仅出现一个值的值，以逗号分隔值的行

Question

For example, I have the following table:

 id  Name - Target 
 1    A       B     
 2    C       D,G      
 3    E       D
 4    F       G,B
 5    L       M,D,B
 6    Q       G,N

I have an over 2 million records database, and I think I made an error by storing values with a comma inside a row. My goal is to query the whole database on the "Target" row, for unique, non-repeating values, in my example the query should return only "M" and "N", because "B", "D" and "G" are repeating..

Is there a single query line to get this done ?

Answer 1

I agree that you should change your database schema.

Your question looks like duplicate of:

https://stackoverflow.com/a/17942691/4421474

So your approach if you really need one could be like:

http://sqlfiddle.com/#!9/0c3ce/4

SELECT
  SUBSTRING_INDEX(SUBSTRING_INDEX(t1.Target, ',', numbers.n), ',', -1) letter
FROM
  numbers 
INNER JOIN t1
  ON CHAR_LENGTH(t1.Target)
     -CHAR_LENGTH(REPLACE(t1.Target, ',', ''))>=numbers.n-1
GROUP BY letter
HAVING COUNT(*)=1

Notice that you need to set numbers table with values from 1 to max index of values in target column of your table.