SQL 如何从另一个表中与另一个表中匹配的 id 获取名称？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15193986/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to get name from another table with matching id in another table?
提问by Mausami
I am using sql server 2008 r2 with php in my website. I have 2 tables.
我在我的网站上使用带有 php 的 sql server 2008 r2。我有2张桌子。
1 is for employees.
1是给员工的。
(int) (nvarchar) (nvarchar)
id name type
1 john 2
2 peter 1
3 leah 2
4 frank 1
5 tang 3
2 is for work
2 是为了工作
(int) (nvarchar) (nvarchar)
workid name employees
1 task1 1,3
2 task2 2,3
3 task3 1,3,4
4 task4 2
I want to make query which give me work description with employee name where type < 3.
我想进行查询，该查询为我提供员工姓名的工作描述，其中类型 < 3。
Means i want to get result like this.
意味着我想得到这样的结果。
workid name employee
1 task1 john, leah
2 task2 peter, leah
3 task3 john,leah,frank
like wise
同样地
so how can i achieve this result with sql query ?
那么我怎样才能用 sql 查询达到这个结果呢？
I can not change in table schema.
我无法更改表架构。
i tried to use with case when statement but its not working.
我尝试使用 case when 语句，但它不起作用。
Please help me to get this working..
请帮我解决这个问题..
采纳答案by John Woo
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.
本文的内容并不能完全回答问题，但它会建议您如何正确规范化表格以简化问题。
This is a Many-to-ManyRelationship.
这是一种Many-to-Many关系。
Employees
- ID (Primary Key)
- Name
- Type
Task
- ID (Primary Key)
- Name
Work
- EmployeeID (Foreign Key)
- TaskID (Foreign Key)
EMPLOYEE TABLE
员工表
id name type
1 john 2
2 peter 1
3 leah 2
4 frank 1
5 tang 3
TASK TABLE
任务表
id name
1 task1
2 task2
3 task3
4 task4
WORK TABLE
工作表
TaskID EmployeeID
1 1
1 3
2 2
2 4
3 1
3 2
3 3
4 4
Query,
询问，
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
回答by sgeddes
Here's one way you can split a comma delimited list using For XML:
这是您可以使用以下方法拆分逗号分隔列表的一种方法For XML：
SELECT w.workid, w.name,
STUFF((
SELECT ',' + E.Name AS [text()]
FROM (
SELECT A.workid,
Split.a.value('.', 'VARCHAR(100)') AS EmpId
FROM
(SELECT workid,
CAST ('<M>' + REPLACE(employees, ',', '</M><M>') + '</M>' AS XML) AS String
FROM work
) AS A
CROSS APPLY String.nodes ('/M') AS Split(a)
) A
JOIN employees E ON A.EmpId = E.Id
WHERE WorkId = w.WorkId
FOR XML PATH('')
), 1, 1, '') AS Employees
FROM work w
This results in:
这导致：
WORKID NAME EMPLOYEES
1 task1 john,leah
2 task2 peter,leah
3 task3 john,leah,frank
4 task4 peter
