时间戳

首页 > 解决方案 > SQL Join left, group by 得到 Where ID 只等于一个特定的

mysql - SQL Join left, group by 得到 Where ID 只等于一个特定的

问题描述

所以我有一个如下的 SQL 查询，它可以完美地获得我的平均费率和与之相关的其他值。

  SELECT deal_page.id,
    deal_page.merchant_id,
    deal_page.category_id,
    deal_page.emirate_id,
    deal_page.title,
    deal_page.title,
    deal_page.description,
    deal_page.emirate,
    deal_page.lat,
    deal_page.lng,
    deal_page.address,
    deal_page.thumbnail_name,
    deal_page.price,
    deal_page.created_at,
    AVG(ratings.ratings) AS ratings
FROM deal_page 
LEFT JOIN ratings ON deal_page.id = ratings.deal_id 
GROUP BY deal_page.id 
ORDER BY lat ASC

没有我试图让数据库让我得到 deal_page 上的 emirate_id 等于 2 的列表。我尝试了下面的查询，但它给了我一个错误：

SELECT deal_page.id,
    deal_page.merchant_id,
    deal_page.category_id,
    deal_page.emirate_id,
    deal_page.title,
    deal_page.title,
    deal_page.description,
    deal_page.emirate,
    deal_page.lat,
    deal_page.lng,
    deal_page.address,
    deal_page.thumbnail_name,
    deal_page.price,
    deal_page.created_at,
    AVG(ratings.ratings) AS ratings 
FROM deal_page 
LEFT JOIN ratings ON deal_page.id = ratings.deal_id 
GROUP BY deal_page.id 
ORDER BY lat ASC 
WHERE deal_page.emirate_id = 2;

错误说：#1064 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以在第 1 行的“WHERE deal_page.emirate_id = 2 LIMIT 0, 25”附近使用正确的语法

标签： mysqlsql

解决方案

Where应该在聚合之前进行：

SELECT . . . 
FROM deal_page dp LEFT JOIN 
     ratings r
     ON dp.id = r.deal_id 
WHERE deal_page.emirate_id = 2
GROUP BY dp.id 
ORDER BY lat ASC ;

注意：我使用了表别名，因此很容易为来自的列编写 & 更少的输入（避免完整的表名）。

推荐阅读