MySQL查询WHERE子句语法错误求助：按用户ID筛选失败

嘿，我一眼就看到你SQL里的问题了，咱们一步步来修正：

核心错误点 #

1. JOIN WHERE的语法完全错误：WHERE子句必须放在所有JOIN操作之后，而且你最后那个多余的JOIN没有关联任何表，直接导致语法报错。
2. 用户ID筛选的位置不对：你只在最后加spend_user_id='{$globId}'是没用的——每个子查询（today/yesterday/week/month）已经查询了全表数据，最后加WHERE根本筛选不到目标用户的数据，必须在每个子查询里都加上用户ID的筛选条件！
3. 额外小优化：你的月份日期计算可以更简洁，LAST_DAY(CURDATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH等价于DATE_FORMAT(CURDATE(), '%Y-%m-01')，可读性更强。

修正后的查询语句 #

$dates = DB::getInstance()->select("
    SELECT 
        today.clicks AS clicksToday, 
        today.spend_amount AS amountToday, 
        yesterday.clicks AS clicksYesterday, 
        yesterday.spend_amount AS amountYesterday, 
        week.clicks AS clicksWeekly, 
        week.spend_amount AS amountWeekly, 
        month.clicks AS clicksMonthly, 
        month.spend_amount AS amountMonthly 
    FROM 
        (SELECT COUNT(*) clicks, SUM(spend_amount) spend_amount 
         FROM spending 
         WHERE spend_date >= CURDATE() 
           AND spend_user_id='{$globId}') today
    CROSS JOIN
        (SELECT COUNT(*) clicks, SUM(spend_amount) spend_amount 
         FROM spending 
         WHERE spend_date >= CURDATE() - INTERVAL 1 DAY 
           AND spend_date < CURDATE()
           AND spend_user_id='{$globId}') yesterday
    CROSS JOIN
        (SELECT COUNT(*) clicks, SUM(spend_amount) spend_amount 
         FROM spending 
         WHERE spend_date >= CURDATE() - INTERVAL 7 DAY 
           AND spend_date < CURDATE()
           AND spend_user_id='{$globId}') week
    CROSS JOIN
        (SELECT COUNT(*) clicks, SUM(spend_amount) spend_amount 
         FROM spending 
         WHERE spend_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
           AND spend_user_id='{$globId}') month
");

重要提醒：防止SQL注入！ #

你现在直接把{$globId}拼进SQL语句里是非常危险的，很容易被SQL注入攻击。如果是Laravel环境，建议用参数绑定的方式：

$dates = DB::getInstance()->select("
    SELECT 
        today.clicks AS clicksToday, 
        today.spend_amount AS amountToday, 
        yesterday.clicks AS clicksYesterday, 
        yesterday.spend_amount AS amountYesterday, 
        week.clicks AS clicksWeekly, 
        week.spend_amount AS amountWeekly, 
        month.clicks AS clicksMonthly, 
        month.spend_amount AS amountMonthly 
    FROM 
        (SELECT COUNT(*) clicks, SUM(spend_amount) spend_amount 
         FROM spending 
         WHERE spend_date >= CURDATE() 
           AND spend_user_id=?) today
    CROSS JOIN
        (SELECT COUNT(*) clicks, SUM(spend_amount) spend_amount 
         FROM spending 
         WHERE spend_date >= CURDATE() - INTERVAL 1 DAY 
           AND spend_date < CURDATE()
           AND spend_user_id=?) yesterday
    CROSS JOIN
        (SELECT COUNT(*) clicks, SUM(spend_amount) spend_amount 
         FROM spending 
         WHERE spend_date >= CURDATE() - INTERVAL 7 DAY 
           AND spend_date < CURDATE()
           AND spend_user_id=?) week
    CROSS JOIN
        (SELECT COUNT(*) clicks, SUM(spend_amount) spend_amount 
         FROM spending 
         WHERE spend_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
           AND spend_user_id=?) month
", [$globId, $globId, $globId, $globId]);

这样既安全，还能避免ID包含特殊字符时的语法问题。

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