我和他有类似的问题:把逗号分隔成唯一的列。
SELECT
id, n.digit, name,
SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', n.digit+1), ',', -1)
FROM
mytable
INNER JOIN
(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
ON LENGTH(REPLACE(name, ',' , '')) <= LENGTH(name)-n.digit现在,答案奏效了..。不过,我有个不同的案子。我有一个数据库表
ID Transaction
1 Chocolate, Milk
2 Peanut, Beer, Chocolate
3 Chocolate, Beer, Peanut现在,我希望它看起来像用逗号分隔每一项一样
Chocolate
Milk
Peanut
Beer 数一数它在桌子上的次数
Order Count
Chocolate 3
Milk 1
Peanut 2
Beer 2 有办法吗?请用mYSQL表格
发布于 2018-02-25 06:35:41
请通过以下URL
https://www.periscopedata.com/blog/splitting-comma-separated-values-in-mysql here
最后,使用以下查询来避免空格字符(如果有的话)
select items, count(1) from (
select
id,
substring_index(
substring_index(`transaction`, ',', n),
',',
-1
) as items
from txn
join numbers
on char_length(`transaction`)
- char_length(replace(`transaction`, ',', ''))
>= n - 1
) email_recipients_by_dashboard
group by trim(items)发布于 2018-02-25 07:14:10
您没有得到预期的结果,因为逗号之间有某种空间。那就像这样试试。参见这里的演示http://sqlfiddle.com/#!9/7cddb3/1
模式
CREATE TABLE tablename (
id INT,
transaction VARCHAR(50));
INSERT INTO tablename VALUES
(1, 'Chocolate, Milk'),
(2, 'Peanut, Beer, Chocolate'),
(3, 'Chocolate, Beer, Peanut');
CREATE TABLE numbers (
n INT PRIMARY KEY);
INSERT INTO numbers VALUES (1),(2),(3),(4),(5),(6);查询
select transaction, count(1) from (
select
id,
substring_index(
substring_index(transaction, ',', n),
',',
-1
) as transaction
from tablename
join numbers
on char_length(transaction)
- char_length(replace(transaction, ',', ''))
>= n - 1
) tablename_numbers
group by trim(transaction)https://stackoverflow.com/questions/48970592
复制相似问题