从多值列查询不同的值。

Question

我试图查询“标记”列中的所有唯一值。标记列中的每一行可以由多个值组成。因此，在不被迫进行规范化的情况下，我如何查询多值列？

示例行：

Networking
Professionalism
Time Management
Communication, Networking
Career Management, Professionalism
Networking
Communication
Attitude, Interpersonal Skills, Professionalism
Business Protocol, Career Management, Communication, Leadership
Business Protocol, Networking

Answer 1

如果最大数量的元素是可预测的，您可以使用它(请注意，您需要使用UNION，而不是UNION ALL)

Select DISTINCT thefield from thetable where Instr(thefield, ',') = 0
UNION 
Select Distinct Mid(thefield, 1, Instr(thefield, ',')) from thetable Where len(thefield) - len(replace(thefield,',','')) = 1
UNION 
Select Distinct Mid(thefield, Instr(thefield, ',')+1) from thetable Where len(thefield) - len(replace(thefield,',','')) = 1
UNION 
Select Distinct Mid(thefield, Instr(thefield, ',')+1, Instr(Instr(thefield, ',')+1,thefield, ',')) from thetable Where len(thefield) - len(replace(thefield,',','')) = 2
UNION 
Select Distinct Mid(thefield, Instr(Instr(thefield, ',')+1,thefield, ',')+1) from thetable Where len(thefield) - len(replace(thefield,',','')) = 2
--.. and so on (repeat last two Selects as many time as you wish, increasing the where condition by one for each pair)

看上去有点笨重，但应该做好这项工作。未经测试，因此，您可能会在值之前或之后出现一个偏离逗号。