如何编写SQL语句查询当前自然周内的所有用户预约记录

• WEEK函数匹配逻辑缺陷：原生WEEK()仅返回当年的周序号，未关联年份，会出现跨年周数据匹配失效问题；且默认以周日为一周起始，和国内常用的周一为周起始的规则不符也会导致结果偏差
• 额外发现：你贴出的「今日」「明日」SQL代码写反了，且「今日」对应的SQL缺少WHERE关键字、错误使用AND开头拼接条件，实际运行会报错，可核对线上实际生效的代码

1. 当前自然周预约记录查询（默认周一为一周起始，可按需调整） #

// 方案1：同时匹配周数+年份，解决跨年问题，WEEK第二个参数1表示周一为周起始，改为0则是周日为起始
$sql = "SELECT * FROM sreservation WHERE user_fk = $user AND WEEK(rdate,1) = WEEK(NOW(),1) AND YEAR(rdate) = YEAR(NOW()) UNION
SELECT * FROM preservation WHERE user_fk = $user AND WEEK(rdate,1) = WEEK(NOW(),1) AND YEAR(rdate) = YEAR(NOW()) UNION
SELECT * FROM greservation WHERE user_fk = $user AND WEEK(rdate,1) = WEEK(NOW(),1) AND YEAR(rdate) = YEAR(NOW()) ORDER BY rdate";

如果要彻底避免极端跨年场景（比如12月最后一周跨到下一年1月），可以用更稳妥的范围匹配方案：

// 方案2：范围匹配，无跨年问题，周一为周起始
$sql = "SELECT * FROM sreservation WHERE user_fk = $user AND rdate >= DATE_SUB(CURDATE(), INTERVAL (WEEKDAY(CURDATE())) DAY) AND rdate < DATE_ADD(CURDATE(), INTERVAL (7 - WEEKDAY(CURDATE())) DAY) UNION
SELECT * FROM preservation WHERE user_fk = $user AND rdate >= DATE_SUB(CURDATE(), INTERVAL (WEEKDAY(CURDATE())) DAY) AND rdate < DATE_ADD(CURDATE(), INTERVAL (7 - WEEKDAY(CURDATE())) DAY) UNION
SELECT * FROM greservation WHERE user_fk = $user AND rdate >= DATE_SUB(CURDATE(), INTERVAL (WEEKDAY(CURDATE())) DAY) AND rdate < DATE_ADD(CURDATE(), INTERVAL (7 - WEEKDAY(CURDATE())) DAY) ORDER BY rdate";

2. 修正后的今日/明日查询代码 #

今日预约 #

$sql = "SELECT * FROM sreservation WHERE user_fk = $user AND DATE(rdate) = CURDATE() UNION
SELECT * FROM preservation WHERE user_fk = $user AND DATE(rdate) = CURDATE() UNION
SELECT * FROM greservation WHERE user_fk = $user AND DATE(rdate) = CURDATE() ORDER BY rdate";

明日预约 #

$sql = "SELECT * FROM sreservation WHERE user_fk = $user AND DATE(rdate) = CURDATE() + INTERVAL 1 DAY UNION
SELECT * FROM preservation WHERE user_fk = $user AND DATE(rdate) = CURDATE() + INTERVAL 1 DAY UNION
SELECT * FROM greservation WHERE user_fk = $user AND DATE(rdate) = CURDATE() + INTERVAL 1 DAY ORDER BY rdate";

• 直接拼接$user变量到SQL存在SQL注入风险，建议替换为预处理语句绑定参数，避免安全问题
• 如果三张预约表结构完全一致，建议后续合并为一张表，通过类型字段区分不同预约类型，减少多表UNION的冗余查询逻辑

内容的提问来源于stack exchange，提问作者ch3ckth3b4ck