更新时间:2023-01-08 15:54:29
这里的内容并不能完全回答问题,但它会建议您如何正确规范化表格以简化问题.
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.
p>
这是一个多对多
关系.
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