sql-server - SQL Server：加入用户表以匹配匹配列员工编号

Question

我正在运行查询以将用户名从users表显示到tickets表requester_id, submitter_id，并assignee_id在其后面添加一个名称列，如下所示：

  ,t.requester_id
  ,u.name as requester_name
  ,t.submitter_id
  ,u.name as submitter_name
  ,t.assignee_id
  ,u.name as assignee_name 

如果我只做 requester_name 并加入join Users as u on t.requester_id = u.ID它就可以了。但我正在尝试对三列执行此操作。

SELECT t.id
 ,t.url
 ,t.external_id
 ,t.type
 ,t.subject
 ,t.description
 ,t.priority
 ,t.status
 ,t.recipient
 ,t.requester_id
 ,u.name as requester_name
 ,t.submitter_id
 ,u.name as submitter_name
 ,t.assignee_id
 ,u.name as assignee_name
 ,t.organization_id
 ,t.group_id
 ,t.collaborator_ids
 ,t.forum_topic_id
 ,t.problem_id
 ,t.has_incidents
 ,t.due_at
 ,t.tags
 ,t.via
 ,t.custom_fields
 ,t.satisfaction_rating
 ,t.sharing_agreement_ids
 ,t.followup_ids
 ,t.ticket_form_id
 ,t.created_at
 ,t.updated_at
 ,t.channel
FROM Tickets as t
join Users as u on t.requester_id = u.ID
join Users as u on t.submitter_id = u.ID
join Users as u on t.assignee_id = u.ID

我试图在每个 requester_id、submitter_id 和 assignee_id 之后添加一个新行，以在用户表中显示名称。

任何帮助将不胜感激。

标签： sql-servertsql

Answer 1

通过三次加入 Users 表，您已经完成了一半，但每次都需要给它一个不同的别名：

FROM Tickets as t
join Users as ru on t.requester_id = ru.ID
join Users as su on t.submitter_id = su.ID
join Users as au on t.assignee_id = au.ID

然后：

  ,t.requester_id
  ,ru.name as requester_name
  ,t.submitter_id
  ,su.name as submitter_name
  ,t.assignee_id
  ,au.name as assignee_name