mySQL 唯一值

Question

我需要从表中获取唯一值。我有一个包含逗号分隔关键字的列。我需要导出所有没有重复的关键字的单个列表。还可以计算每个关键字出现的频率。

根据我的研究，它是一个类似 UNPIVOTING 的函数，列数未知？

例如:

关键词

<小时/>

• 红、蓝、黄
• 蓝色、橙色、黑色、白色
• 棕色、黑色、透明、粉色
• 蓝色、紫色、橙色

结果

颜色|计数

<小时/>

• 红色1
• 蓝色3
• 黄色1
• 橙色2
• 黑色2
• 白色1
• 棕色1
• 清除1
• 粉色1
• 紫罗兰1

提前谢谢您!!

**到目前为止，我已经尝试添加explode_table类型过程，但意识到我无法从 View 动态调用它。然后我一直在尝试在列上执行反向 GROUP_CONCAT() 。我无法生成可执行的代码。

<小时/>

我的 echo_Me 答案版本:

    SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(sKeywords, ',', n.n), ',', -1) value , count(*) as counts
FROM tblPatternMetadata t CROSS JOIN 
    (SELECT a.N + b.N * 10 + 1 n FROM (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL  SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a, (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION  ALL SELECT 9) b
    ORDER BY n ) n
    WHERE n.n <= 1 + (LENGTH(sKeywords) - LENGTH(REPLACE(sKeywords, ',', ''))) group by value

Answer 1

尝试一下:

    SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(t.keywords, ',', n.n), ',', -1) value , count(*) as counts
    FROM table1 t CROSS JOIN 
   (
   SELECT a.N + b.N * 10 + 1 n
   FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL  SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION  ALL SELECT 9) b

   ORDER BY n
 ) n
 WHERE n.n <= 1 + (LENGTH(t.keywords) - LENGTH(REPLACE(t.keywords, ',', '')))
 group by value

DEMO HERE