CriteoDB商品购买场景SQL查询语句正确性核验咨询

原有写法存在的错误 #

你的原有写法不符合SQL规范，也不满足需求，具体错误点如下：

• 语法顺序错误：SQL查询中WHERE子句必须放在FROM和JOIN关联逻辑之后，你将筛选条件写在LEFT JOIN前面，直接会触发语法报错
• 关联字段错误：你写的关联条件ON productID = purchasedID中的purchasedID不存在，Purchased表的关联字段就是productID；多表关联时同名字段需要加表前缀，否则也会触发字段歧义报错
• 日期常量写法错误：日期值需要用引号包裹，直接写2020-12-03会被SQL识别为数字减法，计算结果为2005，完全无法正确筛选12月3日之后的记录
• 缺少去重逻辑：需求要求返回唯一商品列表，你的写法如果同一个商品被多次购买，会返回重复的商品ID
• JOIN类型选择不合理：你要获取的是已购买商品对应的商品信息，用INNER JOIN更合适，LEFT JOIN会保留Purchased表中关联不上Product表的无效脏数据

正确实现语句 #

如果只需要返回唯一商品ID，写法如下：

SELECT DISTINCT p.productID
FROM Purchased pur
INNER JOIN Product p 
  ON pur.productID = p.productID
WHERE pur.creationdate > '2020-12-03'
ORDER BY p.productprice DESC;

如果需要返回包含商品名称、价格的完整商品列表，写法如下：

SELECT DISTINCT p.productID, p.productname, p.productprice
FROM Purchased pur
INNER JOIN Product p 
  ON pur.productID = p.productID
WHERE pur.creationdate > '2020-12-03'
ORDER BY p.productprice DESC;

补充说明 #

• 用DISTINCT关键字实现去重，保证每个商品仅返回一次
• 给表加了别名简化写法，同时明确标注字段所属表，避免字段歧义报错
• 用INNER JOIN过滤无效脏数据，保证返回的都是合法存在的商品信息
• 日期常量加了单引号，符合SQL日期值的书写规范
• 直接按商品表的价格字段降序排序，满足价格最高的商品优先返回的需求

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