从逗号分隔列表中的一个表中查找主键

Question

我的任务是基于一个设计得非常糟糕的表结构来创建一个报告。

考虑以下两个表。它们包含了每个人都喜欢在每个健身房表演的技术。请记住，一个唯一的人可能会出现在PERSONNEL表的多行中：

PERSONNEL
+-----+-----+-------+--------+-----------+
| ID  | PID | Name  | Gym    | Technique |
+-----+-----+-------+--------+-----------+
| 1   | 122 | Bob   | GymA   | 2,3,4     |
+-----+-----+-------+--------+-----------+
| 2   | 131 | Mary  | GymA   | 1,2,4     |
+-----+-----+-------+--------+-----------+
| 3   | 122 | Bob   | GymB   | 1,2,3     |
+-----+-----+-------+--------+-----------+

TECHNIQUES
+-----+------------+
| ID  | Technique  |
+-----+------------+
| 1   | Running    |
+-----+------------+
| 2   | Walking    |
+-----+------------+
| 3   | Hopping    |
+-----+------------+
| 4   | Skipping   |
+-----+------------+

我遇到的麻烦是MSSQL查询，它将可靠地给出表中正在执行特定技术的每个人的列表。

例如，假设我想要每个喜欢跳过的人的列表。预期的结果将是：

PREFERS_SKIPPING
+-----+-------+--------+
| PID | Name  | Gym    |
+-----+-------+--------+
| 122 | Bob   | GymA   |
+-----+-------+--------+
| 131 | Mary  | GymA   |
+-----+-------+--------+

同样的跳跃：

PREFERS_HOPPING
+-----+-------+--------+
| PID | Name  | Gym    |
+-----+-------+--------+
| 122 | Bob   | GymA   |
+-----+-------+--------+
| 122 | Bob   | GymB   |
+-----+-------+--------+

我可以在ColdFusion中轻松地拆分字符串，但由于PERSONNEL表的大小，这不是一个选项。有人能帮上忙吗？

Answer 1

我认为这个查询看起来更清晰：

SELECT p.*, 
t.Technique as ParsedTechnique
FROM Personnel p
JOIN Techniques t
ON CHARINDEX((','+CAST(t.id as varchar(10))+','), (','+p.technique+',')) > 0
WHERE t.id ='1';

您只需将WHERE t.id =更改为所需的TechniqueId即可。

Fiddle Here

Answer 2

使用此函数

Create FUNCTION F_SplitAsIntTable 
(
@txt varchar(max)
)
RETURNS 
@tab TABLE 
(
 ID int
)
AS
BEGIN
    declare @i int
    declare @s varchar(20)
    Set @i = CHARINDEX(',',@txt)
    While @i>1
        begin
          set @s = LEFT(@txt,@i-1)
          insert into @tab (id) values (@s)
          Set @txt=RIGHT(@txt,Len(@txt)-@i)
          Set @i = CHARINDEX(',',@txt)
        end
    insert into @tab (id) values (@txt) 
    RETURN 
END

您可以像这样查询

declare  @a Table  (id int,Name varchar(10),Kind Varchar(100))
insert into @a values (1,'test','1,2,3,4'),(2,'test2','1,2,3,5'),(3,'test3','3,5')

Select a.ID,Name
from @a a
cross apply F_SplitAsIntTable(a.Kind) b 
where b.ID=2

Answer 3

你必须防止的问题之一是防止"1“匹配"10”和"11“。为此，您需要确保所有值都由分隔符(在本例中为逗号)分隔。

下面是一个使用like的方法，它应该可以有效地工作(尽管性能不会很好)：

SELECT p.*, t.Technique as ParsedTechnique
FROM Personnel p join
     Techniques t
     on ','+p.technique+',' like '%,'+cast(t.id as varchar(255))+',%'
WHERE t.id = 1;

如果性能是一个问题，那么修复您的数据结构并包含一个PersonTechniques表，这样您就可以进行适当的连接。