逗号分隔值分隔 [英] comma separated value separation

问题描述

我有一张桌子，里面有以下几种记录.我有Salesman和关联的帐号(以逗号分隔).

I have one table with the following kinds of records in it. I have Salesman and associated account numbers (comma separated).

+----------+----------+
| Salesman | Acct     |
+----------+----------+
| Ron      | 1,2,3,4  |
| Kin      | 6,7,8    |
| Joe      | 10,23,45 |

I am looking for output some thing like,

+----------+----------+
| Salesman | Acct     |
+----------+----------+
| Ron      | 1        |
| Ron      | 2        |
| Ron      | 3        |

请帮助我写出可以给我以上结果的查询.

Please help me write the query that can give me above result.

谢谢您的帮助.

推荐答案

我假设您只需要一次操作就可以将架构重构为可用的东西.

I assume that you need this just as a one-off operation in order to restructure your schema into something usable.

您是否还有另一个表列出了所有可能的帐号?如果这样做，则可以执行以下操作:

Do you have another table which lists all the possible account numbers? If you do then you can do this:

SELECT salesman, a.Acct
FROM account AS a
JOIN salesman_account AS sa
ON FIND_IN_SET(a.Acct, sa.Acct)

这篇关于逗号分隔值分隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！