你如何在 mysql 中加入同一个表,两次?

2021-11-20 00:00:00 mysql

我有两张桌子.一个(域)有域 id 和域名(dom_id、dom_url).

I have 2 tables. One (domains) has domain ids, and domain names (dom_id, dom_url).

另一个包含实际数据,其中 2 列需要 TO 和 FROM 域名.所以我有 2 列 rev_dom_from 和 rev_dom_for,它们都存储域表中的域名 id.

the other contains actual data, 2 of which columns require a TO and FROM domain names. So I have 2 columns rev_dom_from and rev_dom_for, both of which store the domain name id, from the domains table.

简单.

现在我需要在网页上实际显示两个域名.我知道如何通过 LEFT JOIN 域 ON review.rev_dom_for = domain.dom_url 查询来显示一个或另一个,然后您回显 dom_url,这将回显 rev_dom_for 列中的域名.

Now I need to actually display both domain names on the webpage. I know how to display one or the other, via the LEFT JOIN domains ON reviews.rev_dom_for = domains.dom_url query, and then you echo out the dom_url, which would echo out the domain name in the rev_dom_for column.

但是我如何让它在 dom_rev_from 列中与第二个域名相呼应?

But how would I make it echo out the 2nd domain name, in the dom_rev_from column?

推荐答案

你会使用另一个连接,大致如下:

you'd use another join, something along these lines:

SELECT toD.dom_url AS ToURL, 
    fromD.dom_url AS FromUrl, 
    rvw.*

FROM reviews AS rvw

LEFT JOIN domain AS toD 
    ON toD.Dom_ID = rvw.rev_dom_for

LEFT JOIN domain AS fromD 
    ON fromD.Dom_ID = rvw.rev_dom_from

编辑:

您所做的只是多次加入表格.查看帖子中的查询:它从评论表(别名为 rvw)中选择值,该表为您提供了对域表的 2 个引用(一个 FOR 和一个 FROM).

All you're doing is joining in the table multiple times. Look at the query in the post: it selects the values from the Reviews tables (aliased as rvw), that table provides you 2 references to the Domain table (a FOR and a FROM).

此时,将 Domain 表左连接到 Reviews 表是一件简单的事情.一次(别名为 toD)用于 FOR,第二次(别名为 fromD)用于 FROM.

At this point it's a simple matter to left join the Domain table to the Reviews table. Once (aliased as toD) for the FOR, and a second time (aliased as fromD) for the FROM.

然后在 SELECT 列表中,您将从 DOMAIN 表的两个 LEFT JOINS 中选择 DOM_URL 字段,通过引用域表的每个联接的表别名来引用它们,并将它们别名为 ToURL 和 FromUrl.

Then in the SELECT list, you will select the DOM_URL fields from both LEFT JOINS of the DOMAIN table, referencing them by the table alias for each joined in reference to the Domains table, and alias them as the ToURL and FromUrl.

有关 SQL 中别名的更多信息,请阅读此处.

For more info about aliasing in SQL, read here.

相关文章