更新时间:2023-01-08 15:58:35
本文的内容并不能完全回答问题,但它会建议您如何正确规范表格以简化问题.
The content of this doesn't totally answers the question but it will suggest on how you can properly normalize the table in order for theproblem to be simplified.
这是一个多对多
关系.
Employees
- ID (Primary Key)
- Name
- Type
Task
- ID (Primary Key)
- Name
Work
- EmployeeID (Foreign Key)
- TaskID (Foreign Key)
员工表
id name type
1 john 2
2 peter 1
3 leah 2
4 frank 1
5 tang 3
任务表
id name
1 task1
2 task2
3 task3
4 task4
工作台
TaskID EmployeeID
1 1
1 3
2 2
2 4
3 1
3 2
3 3
4 4
查询,
SELECT t.ID, t.Name,
STUFF(
(SELECT ',' + b.Name
FROM Work a
INNER JOIN Employee b
ON a.EmployeeID = b.ID
WHERE a.TaskID = t.ID
FOR XML PATH (''))
, 1, 1, '') AS NamesList
FROM Task t
-- WHERE ..... -- add additional conditions...
GROUP BY t.ID, t.Name