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
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
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必须是相同的列类型。