3个表上的MySQL INNER/LEFT JOIN，其中第3个表中的记录可能不存在 [英] MySQL INNER/LEFT JOIN on 3 tables, where records in 3rd table might not exist
问题描述
我遇到了一个问题，在一堆失败的尝试之后，我似乎无法弄清.
我有3个表需要进行联接才能进行某些报告，并且在第3个表中可能没有记录.但是，如果第3个表中的记录不存在，我需要为第3个表中的数据报告一个空值，并获取与其他条件匹配的所有记录.
深入到相关列，以下是表结构:
成员-此表包含所有注册到网站的成员
| memberId | insertDate |
| ==========|=====================|
| 1 | 2013-08-01 18:18:16 |
| 2 | 2013-08-02 18:18:16 |
| 3 | 2013-08-03 18:18:16 |
| 4 | 2013-08-04 18:18:16 |
| 5 | 2013-08-05 18:18:16 |
registration_steps -此表保存注册过程的进度以及注册是否完成
| memberId | completed |
| ==========|===========|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 0 |
| 5 | 1 |
购买-此表可以保存，..购买
| memberId | insertDate |
| ==========|=====================|
| 1 | 2013-08-02 18:18:16 |
| 1 | 2013-08-03 17:18:16 |
| 1 | 2013-08-03 18:18:16 |
| 5 | 2013-08-07 18:18:16 |
这是我到目前为止提出的查询:
SELECT `m`.`memberId`,
DATE(`m`.`insertDate`) AS `regDate`,
COUNT(`p`.`memberId`) AS `totalTransactions`,
DATE(MIN(`p`.`insertDate`)) AS `firstPurchaseDate`,
DATE(MAX(`p`.`insertDate`)) AS `latestPurchaseDate`,
DATEDIFF(DATE(MIN(`p`.`insertDate`)), DATE(`m`.`insertDate`)) AS `daysBetweenRegAndFirstPurchase`
FROM `db`.`members` `m`
INNER JOIN `db`.`registration_steps` `r` ON `m`.`memberId` = `r`.`memberId`
INNER JOIN `db`.`purchases` `p` ON `m`.`memberId` = `p`.`memberId`
WHERE `m`.`insertDate` BETWEEN '2013-07-01 00:00:00' AND '2013-07-31 23:59:59'
AND `r`.`completed` = 1
GROUP BY `m`.`memberId`
;
它向我显示了我想要的一切，但成员的表购买记录缺失.
这就是我得到的:
| memberId | regDate | totalTransactions | firstPurchaseDate | latestPurchaseDate | daysBetweenRegAndFirstPurchase |
| ==========|=====================|===================|=====================|=====================|================================|
| 1 | 2013-08-01 18:18:16 | 3 | 2013-08-02 18:18:16 | 2013-08-03 18:18:16 | 1 |
| 5 | 2013-08-05 18:18:16 | 1 | 2013-08-07 18:18:16 | 2013-08-07 18:18:16 | 2 |
但是我需要的是:
| memberId | regDate | totalTransactions | firstPurchaseDate | latestPurchaseDate | daysBetweenRegAndFirstPurchase |
| ==========|=====================|===================|=====================|=====================|================================|
| 1 | 2013-08-01 18:18:16 | 3 | 2013-08-02 18:18:16 | 2013-08-03 18:18:16 | 1 |
| 2 | 2013-08-02 18:18:16 | 0 | NULL | NULL | -1 |
| 3 | 2013-08-03 18:18:16 | 0 | NULL | NULL | -1 |
| 5 | 2013-08-05 18:18:16 | 1 | 2013-08-07 18:18:16 | 2013-08-07 18:18:16 | 2 |
为了实现这一点，我尝试将第二个内部联接更改为左联接，左外部联接，并将where条件设置为第一个内部联接条件.但是，我无法获得理想的结果. (必须承认我对一些非常长时间运行的查询进行了插补，尽管这些查询可能是正确的(?)(实际情况下成员的总数约为20k).)
有人吗?
提前谢谢！
要从member表中获取所有结果，您需要left join其他表并为member表添加group by. /p>
样品
FROM `db`.`members` `m`
LEFT OUTER JOIN `db`.`registration_steps` `r` ON `m`.`memberId` = `r`.`memberId`
LEFT OUTER JOIN `db`.`purchases` `p` ON `m`.`memberId` = `p`.`memberId`
向左[外部]
指定左表中不满足联接条件的所有行都包括在结果集中，并且另一个表的输出列除内部联接返回的所有行之外均设置为NULL.
I've got a problem that I can't seem to figure out after a bunch of failed attempts.
I've got three tables that I need to do a join on for some reporting, and in the 3rd table a record might not exist. But if the record in the 3rd table doesn't exist, I need to report a null value for the data that comes from the 3rd table and get all records that match the other conditions.
Stripped down to the relevant columns, here are the table structures:
members - this table holds all members that register to a website
| memberId | insertDate |
| ==========|=====================|
| 1 | 2013-08-01 18:18:16 |
| 2 | 2013-08-02 18:18:16 |
| 3 | 2013-08-03 18:18:16 |
| 4 | 2013-08-04 18:18:16 |
| 5 | 2013-08-05 18:18:16 |
registration_steps - this table holds the progress of the registration processes and whether the registration was completed or not
| memberId | completed |
| ==========|===========|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 0 |
| 5 | 1 |
purchases - this table holds, well.. purchases
| memberId | insertDate |
| ==========|=====================|
| 1 | 2013-08-02 18:18:16 |
| 1 | 2013-08-03 17:18:16 |
| 1 | 2013-08-03 18:18:16 |
| 5 | 2013-08-07 18:18:16 |
This is the query I've come up with so far:
SELECT `m`.`memberId`,
DATE(`m`.`insertDate`) AS `regDate`,
COUNT(`p`.`memberId`) AS `totalTransactions`,
DATE(MIN(`p`.`insertDate`)) AS `firstPurchaseDate`,
DATE(MAX(`p`.`insertDate`)) AS `latestPurchaseDate`,
DATEDIFF(DATE(MIN(`p`.`insertDate`)), DATE(`m`.`insertDate`)) AS `daysBetweenRegAndFirstPurchase`
FROM `db`.`members` `m`
INNER JOIN `db`.`registration_steps` `r` ON `m`.`memberId` = `r`.`memberId`
INNER JOIN `db`.`purchases` `p` ON `m`.`memberId` = `p`.`memberId`
WHERE `m`.`insertDate` BETWEEN '2013-07-01 00:00:00' AND '2013-07-31 23:59:59'
AND `r`.`completed` = 1
GROUP BY `m`.`memberId`
;
It shows me everything I want but the members with a missing record in table purchases.
Here is what I get:
| memberId | regDate | totalTransactions | firstPurchaseDate | latestPurchaseDate | daysBetweenRegAndFirstPurchase |
| ==========|=====================|===================|=====================|=====================|================================|
| 1 | 2013-08-01 18:18:16 | 3 | 2013-08-02 18:18:16 | 2013-08-03 18:18:16 | 1 |
| 5 | 2013-08-05 18:18:16 | 1 | 2013-08-07 18:18:16 | 2013-08-07 18:18:16 | 2 |
But what I need is:
| memberId | regDate | totalTransactions | firstPurchaseDate | latestPurchaseDate | daysBetweenRegAndFirstPurchase |
| ==========|=====================|===================|=====================|=====================|================================|
| 1 | 2013-08-01 18:18:16 | 3 | 2013-08-02 18:18:16 | 2013-08-03 18:18:16 | 1 |
| 2 | 2013-08-02 18:18:16 | 0 | NULL | NULL | -1 |
| 3 | 2013-08-03 18:18:16 | 0 | NULL | NULL | -1 |
| 5 | 2013-08-05 18:18:16 | 1 | 2013-08-07 18:18:16 | 2013-08-07 18:18:16 | 2 |
In order to achieve this, I tried to change the second inner join to a left join, a left outer join and put the where conditions to the first inner join condition. However, I wasn't able to get the desired result. (Must admit I interupted a few VERY long running queries that might have been correct(?) though (total count for members in real scenario is about 20k).)
Anyone?
Thanks in advance!
To get the all the result from the member table you need to left join other table and add group by for the member table.
Sample
FROM `db`.`members` `m`
LEFT OUTER JOIN `db`.`registration_steps` `r` ON `m`.`memberId` = `r`.`memberId`
LEFT OUTER JOIN `db`.`purchases` `p` ON `m`.`memberId` = `p`.`memberId`
LEFT [ OUTER ]
Specifies that all rows from the left table not meeting the join condition are included in the result set, and output columns from the other table are set to NULL in addition to all rows returned by the inner join.
这篇关于3个表上的MySQL INNER/LEFT JOIN，其中第3个表中的记录可能不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
