mysql连接四个表并获取超过1个月的记录

5lhxktic 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(330)

我想加入四个表并获取超过30天的记录。我想我做得很好，但不是在30天以前。
它返回空结果。我的问题：

SELECT b.accountid, 
       a.memberid, 
       c.id,
       d.memberid AS uid,
       d.datecreated 
FROM   `tbl_members` c 
       LEFT JOIN `table1` b 
              ON c.`id` = b.`accountid` 
       LEFT JOIN `table2` a 
              ON b.`accountid` = a.`memberid` 
       LEFT JOIN `table3` d 
              ON a.`id` = d.`memberid`
WHERE  c.`is_active` = 1 
       AND a.memberid IS NULL 
       AND b.accountid IS NULL 
       AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= d.datecreated
ORDER  BY uid

记录：

tbl_members
---------------------------
id | username | is_active |
 1 |   user1  |     1     |
---------------------------
table1
------------------------------
id | accountid | datecreated |
   |           |             |
------------------------------
table2
------------------------------
id | memberid  | datecreated |
   |           |             |
------------------------------
table3
----------------------------------------
id | memberid  |      datecreated      |
 1 |     1     | 2018-06-21 00:12:51   |
----------------------------------------

当我删除此行时：

AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= d.datecreated

我得到了正确的结果。
编辑：我现在使用以下查询得到正确的结果：

SELECT b.accountid, 
       a.memberid, 
       d.memberid, 
       c.memberid AS uid, 
       d.datecreated 
FROM   `tbl_members` c 
       LEFT JOIN `tbl_bonus_direct` b 
              ON c.`memberid` = b.`accountid` 
       LEFT JOIN `tbl_wallet_cash` a 
              ON b.`accountid` = a.`memberid` 
       LEFT JOIN `tbl_members_accounts` d 
              ON c.`memberid` = d.`memberid`
WHERE  c.`is_active` = 1 
       AND a.memberid IS NULL 
       AND b.accountid IS NULL 
       AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) >= date(d.datecreated)
ORDER  BY uid

但是我想区分表3中的memberid

mysql

来源：https://stackoverflow.com/questions/51454017/mysql-join-four-tables-and-get-records-older-than-1-month

2条答案

按热度按时间

anauzrmj1#

比较运算符错误。您正在使用创建过去的日期 DATE_SUB 但只能选择在该日期之后创建的记录。
更改：

AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= d.datecreated

收件人：

AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) >= DATE(d.datecreated)

将date（）添加到比较中会删除您在注解中标识的问题。date_sub（）的默认时间为 00:00:00 比较将考虑一天中的时间。date（）删除时间，因此比较将只在date上进行。
因此，您的查询变成：

SELECT b.accountid, 
       a.memberid, 
       c.memberid,
       d.memberid AS uid,
       d.datecreated 
FROM   `tbl_members` c 
       LEFT JOIN `table1` b 
              ON c.`memberid` = b.`accountid` 
       LEFT JOIN `table2` a 
              ON b.`accountid` = a.`memberid` 
       LEFT JOIN `table3` d 
              ON a.`memberid` = d.`memberid`
WHERE  c.`is_active` = 1 
       AND a.memberid IS NULL 
       AND b.accountid IS NULL 
       AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) >= DATE(d.datecreated)
ORDER  BY uid

赞(0）回复(0）举报 2021-06-21

ogq8wdun2#

你的第一个错误区域

`tbl_members` c 
       LEFT JOIN `table1` b 
              ON c.`memberid` = b.`accountid`

在这里的table里 tbl_members 没有列名是memberid

LEFT JOIN `table3` d 
              ON a.`memberid` = d.`memberid`
``` `table3` 联接将与表一起发生 `tbl_members` 现在我要纠正你的问题，下面是

SELECT b.accountid,
a.memberid,
c.username,
d.memberid AS uid,
d.datecreated
FROM tbl_members c
LEFT JOIN table1 b
ON c.id = b.accountid
LEFT JOIN table2 a
ON b.accountid = a.memberid
LEFT JOIN table3 d
ON c.id = d.memberid
WHERE c.is_active = 1
AND a.memberid IS NULL
AND b.accountid IS NULL
AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(d.datecreated)
ORDER BY uid

我想你想要这样的东西就像你说的你需要不同的会员身份证

SELECT b.accountid,
a.memberid,
c.username,
d.memberid AS uid,
d.datecreated
FROM tbl_members c
LEFT JOIN table1 b
ON c.id = b.accountid
LEFT JOIN table2 a
ON b.accountid = a.memberid
LEFT JOIN
(select distinct memberid, datecreated from table3 ) as d
ON c.id = d.memberid
WHERE c.is_active = 1
AND a.memberid IS NULL
AND b.accountid IS NULL
AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(d.datecreated)
ORDER BY uid

http://sqlfiddle.com/#!9月1日

赞(0）回复(0）举报 2021-06-21

我来回答

mysql连接四个表并获取超过1个月的记录

2条答案

相关问题

热门标签

最新问答