在MySQL中,给定下表,其中Tags
是包含逗号分隔的字符串的列
Id Name Salary Tags
----------------------------
1 James 5000 Sales, Marketing
2 John 4000 Sales, Finance
3 Sarah 3000 HR, Marketing, Finance
我们如何获得标签中每个单词/标签的总和(薪水)?所以结果看起来像这样吗?
Tag TotalSalary
------------------------
Sales 9000
Marketing 8000
Finance 7000
HR 3000
任何帮助将不胜感激。谢谢!
最佳答案
尽管我强烈建议规范化您的数据结构,而不要存储用逗号分隔的列表,但这是一种使用“数字”表的便捷方法:
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', n.n), ',', -1) value, sum(salary)
FROM tags 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(tags) - LENGTH(REPLACE(tags, ',', '')))
GROUP BY SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', n.n), ',', -1)
SQL Fiddle Demo
它利用
substring_index
并支持多达100个定界项目-易于根据需要进行调整。我已经多次使用这种方法,效果很好–我首先在this post中看到了它。关于mysql - 按逗号分隔的字符串中的单词分组,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/29090222/