SQL查询插入超过此日期的记录

Following is my SQL query I have successfully made it to perform the functionality of getting input from one table and inserting to another one and getting maximum date from z_events there is just one thing I got stuck in that how it will modify the following code to compare the maximum date (r_added) present in the z_events table and then store only those records whose dates (event_attendees.i_last_updated) are greater than the maximum date come from z_events (r_added). Kindly help I'll be very thankful to you

以下是我的SQL查询我已经成功地执行了从一个表获取输入并插入到另一个表并从z_events获取最大日期的功能,我只有一件事情就是如何修改以下代码来比较z_events表中存在的最大日期(r_added),然后仅存储日期(event_attendees.i_last_updated)大于最大日期的记录来自z_events(r_added)。请帮助我非常感谢你

# Current Week

INSERT into z_events (resident_id, fascility_id, 
                      u_status, u_first, u_last, 
                      r_present, r_reminder, r_added)
SELECT event_attendees.fk_resident_id, user_facility.fk_facility_id, 
       users.i_status, users.v_first_name, users.v_last_name, 
       SUM(event_attendees.i_present), SUM(event_attendees.i_reminder_sent), CURDATE()
FROM event_attendees 
LEFT JOIN (users, users_roles, user_facility) 
       ON (event_attendees.fk_resident_id = users.id 
           AND event_attendees.fk_resident_id = users_roles.fk_user_id 
           AND event_attendees.fk_resident_id = user_facility.fk_user_id)
WHERE users_roles.fk_role_id = 4 
      AND FROM_UNIXTIME(event_attendees.i_last_updated) > DATE_SUB(CURDATE(), interval 7 day)
      AND NOT EXISTS (SELECT * 
                      FROM z_events 
                      WHERE z_events.resident_id = event_attendees.fk_resident_id 
                            AND WEEK(z_events.r_added) = WEEK(CURDATE()))
GROUP BY event_attendees.fk_resident_id

1 个解决方案

#1

Splitting the code in two queries will solve your problem

在两个查询中拆分代码将解决您的问题

# Current Week
$sql = "SELECT r_added FROM z_events ORDER BY r_added DESC LIMIT 1";
$re = mysql_query($sql, $con) or die(mysql_error());
$row = mysql_fetch_assoc($re);
$d = $row['r_added'];

$week_cur = "INSERT into z_events (resident_id, fascility_id, u_status, u_first,
u_last, r_present, r_reminder, r_added)
SELECT event_attendees.fk_resident_id, user_facility.fk_facility_id, users.i_status, users.v_first_name, users.v_last_name,
SUM(event_attendees.i_present), SUM(event_attendees.i_reminder_sent),
CURDATE()
FROM event_attendees 
LEFT JOIN (users,users_roles,user_facility) ON
(event_attendees.fk_resident_id = users.id AND
event_attendees.fk_resident_id = users_roles.fk_user_id AND
event_attendees.fk_resident_id = user_facility.fk_user_id)
WHERE 
event_attendees.i_last_updated > '$d' AND
users_roles.fk_role_id = 4 AND
FROM_UNIXTIME(event_attendees.i_last_updated) > DATE_SUB(CURDATE(),interval 7 day) 
AND NOT EXISTS 
(SELECT * FROM z_events 
WHERE
z_events.resident_id = event_attendees.fk_resident_id AND
WEEK(z_events.r_added) = WEEK(CURDATE()))
GROUP BY event_attendees.fk_resident_id";

// Execute query
mysql_query($week_cur, $con) or die(mysql_error());

Of course, the r_added as well as i_last_updated must be of the same column type.

当然,r_added和i_last_updated必须是相同的列类型。

#1