MYSQL 自联接如何工作? [英] How does a MYSQL Self-Join Work?

问题描述

我最近问了一个关于自联接的问题，我得到了很好的答案.

I recently asked a question about Self-Joins and I got a great answer.

该查询旨在查找事件 2 的 ID、开始日期和价格，在事件 1 之后 1 天.

The query is meant to find the ID, Start Date, and Price of Event2, Following Event1 by 1 Day.

代码运行良好.但我不明白怎么做.

有人可以尽可能彻底地解释一下 - 查询的不同部分是什么以及它们的作用是什么?

Could someone explain as thoroughly as you can- what the different parts of the query are and what they do?

SELECT event2.id, event2.startdate, event2.price
FROM mm_eventlist_dates event1
JOIN mm_eventlist_dates event2 
ON event2.startdate = date_add(event1.enddate, INTERVAL 1 DAY)
WHERE event1.id=$id

我非常感谢您的帮助，无论出于何种原因，我都很难解决这个问题.

I really appreciate your help, for whatever reason I'm having a really hard time wrapping my head around this.

推荐答案

我试图理解这一点的方法是在标记为 event1 和 event2 的一块上写出两个列表.然后在每个列表中列出一些记录(列表将是相同的)现在从下面描述中的 WHERE 开始.

The way I'd try to understand this is to write out two lists on piece one labelled event1 and one event2. Then list a few records in each list (the lists will be identical) now start at the WHERE in the description below.

我们正在从两个表中获取数据(好吧，同一个表使用了两次，但暂时忽略它)

We're taking data from two tables (OK the same table used twice, but try to ignore that for the moment)

FROM mm_eventlist_dates event1
JOIN mm_eventlist_dates event2 

自下而上阅读其余部分可能会有所帮助.

It probably helps to read the rest from the bottom up.

  WHERE event1.id=$id

所以我们想要来自 event1 的具有指定记录 id 的记录.据推测，这恰好是一项记录.现在我们计算出该事件结束后的第二天.

So we want the record from event1 that has the specified record id. Presumably that's exactly one record. Now we figure out the day after that event ended.

 date_add(event1.enddate, INTERVAL 1 DAY)

现在告诉我们 event2 的记录，它们需要从那个日期开始，

Now that tells us the records from event2, they need to start on that date,

ON event2.startdate = date_add(event1.enddate, INTERVAL 1 DAY)

我们现在确定了两条记录，我们想要哪些字段?

We now have two records identified, what fields do we want?

SELECT event2.id, event2.startdate, event2.price

哦，只是我们算出的开始日期中的字段.

Oh, just the fields from the one whose start date we figured out.

这篇关于MYSQL 自联接如何工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！