如何从另一个表中具有匹配ID的另一个表中获取名称? [英] how to get name from another table with matching id in another table?

问题描述

我在我的网站中使用带有 php 的 sql server 2008 r2.我有两张桌子.

I am using sql server 2008 r2 with php in my website. I have 2 tables.

1 是给员工的.

(int)      (nvarchar)   (nvarchar)

id         name        type
 1         john         2
 2         peter        1
 3         leah         2
 4         frank        1
 5         tang         3

2 是为了工作

(int)      (nvarchar)   (nvarchar)

workid      name        employees
  1         task1       1,3
  2         task2       2,3
  3         task3       1,3,4
  4         task4         2

我想进行查询，该查询为我提供带有员工姓名的工作描述，其中类型 <3.

I want to make query which give me work description with employee name where type < 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

很聪明

那么我怎样才能用 sql 查询达到这个结果?

so how can i achieve this result with sql query ?

我无法更改表架构.

我尝试使用 case when 语句，但它不起作用.

i tried to use with case when statement but its not working.

请帮我解决这个问题..

Please help me to get this working..

推荐答案

本文的内容并不能完全回答问题，但它会建议您如何正确规范表格以简化问题.

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

• SQLFiddle 演示

这篇关于如何从另一个表中具有匹配ID的另一个表中获取名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！