代码之家 › 专栏 › 技术社区 › Zephyr

如何将一个表中的多个列联接到另一个查阅表?

sql-server sql

Zephyr · 技术社区 · 6 年前

当一个表中的多个列引用另一个表时,我无法理解如何将两个表连接在一起。

例如,我有一个“文档状态”表:

document_statuses table:
+-----------+-------------+
| status_id | status_name |
+-----------+-------------+
|         1 | RECEIVED    |
|         2 | MISSING     |
|         3 | NOT_NEEDED  |
+-----------+-------------+

现在在另一个表中,我跟踪多个文档的状态:

filings table:

+-----------+-------------+----------------+----------------+----------------+
| filing_id | filing_name | doc1_status_id | doc2_status_id | doc3_status_id |
+-----------+-------------+----------------+----------------+----------------+
|         1 | John        |              1 |              3 |              2 |
|         2 | Mikaela     |              2 |              3 |              2 |
|         3 | Sam         |              1 |              2 |              1 |
+-----------+-------------+----------------+----------------+----------------+

我如何编写一个查询来提取 status_name 对于每个列,并生成以下结果:

+-------------+-------------+-------------+------------+
| Filing Name | Doc1 Status | Doc2 Status | Doc3Status |
+-------------+-------------+-------------+------------+
| John        | RECEIVED    | NOT_NEEDED  | MISSING    |
| Mikaela     | MISSING     | NOT_NEEDED  | MISSING    |
| Sam         | RECEIVED    | MISSING     | RECEIVED   |
+-------------+-------------+-------------+------------+

我知道当从 document_statuses 每行,但不是多行。如果我只有一列 documents 指的是 文件状态 我会做一个简单的 JOIN :

SELECT filing_name, status_name
FROM documents d
LEFT JOIN document_statuses ds ON d.doc1_status = ds.status_id

但是当我需要不止一个的时候我该怎么做呢?

3 回复 | 直到 6 年前

SQLing4ever 6 年前

您需要多次联接状态表,然后将select子句中的列别名设置为所需的格式。请注意,您必须为join子句中的表加别名,以便可以引用语句的select子句中的列。

SELECT filing_name
, ds1.status_name AS 'Doc1 Status'
, ds2.status_name AS 'Doc2 Status'
, ds3.status_name AS 'Doc3 Status'
FROM documents d
LEFT JOIN document_statuses ds1 ON d.doc1_status = ds1.status_id
LEFT JOIN document_statuses ds2 ON d.doc2_status = ds2.status_id
LEFT JOIN document_statuses ds3 ON d.doc3_status = ds3.status_id

Milorad 6 年前

您需要使用不同的表别名对同一个表进行三次联接。

    SELECT filing_name, ds.status_name, ds1.status_name, 
    ds2.status_name FROM documents d
   LEFT JOIN document_statuses ds ON d.doc1_status
   LEFT JOIN document_statuses ds1 ON d.doc1_status
   LEFT JOIN document_statuses ds2 ON d.doc1_status

Neeraj Agarwal 6 年前

您可以这样做:

select t1.filing_name, t2.status_name as doc1_status, t3.status_name as doc2_status, t4.status_name as doc3_status
from filings_table t1
inner join statuses_table t2 on t1.doc1_status_id = t2.status_id
inner join statuses_table t3 on t1.doc2_status_id = t3.status_id
inner join statuses_table t4 on t1.doc3_status_id = t4.status_id