MySql 查询按存在于另一个表中进行排序

Question

我有三个表(products、product_info 和 specials)

products(id, created_date)
product_info(product_id, language, title, .....)
specials(product_id, from_date, to_date)

product_id是引用产品id的外键

在搜索产品时，我想按特价产品顺序搜索...

这是我的尝试

SELECT products.*, product_info.* FROM products
INNER JOIN product_info ON product_info.product_id = products.id
INNER JOIN specials ON specials.product_d = products.id
WHERE product_info.language = 'en'
AND product_info.title like ?
AND specials.from_date < NOW()
AND specials.to_date > NOW()
ORDER BY specials.product_id DESC, products.created_at DESC

但结果只是特产..

Answer 1

如果不是每个产品都在 specials 表中，您应该使用 LEFT JOIN 代替 specials 并将对 specials 日期的验证放在 ON CLAUSE 中。然后您通过 products.id 订购但将特价商品放在第一位，通过使用 CASE WHEN 对其进行验证:

SELECT products.*, product_info.*
FROM products
INNER JOIN product_info ON product_info.product_id = products.id
LEFT JOIN specials ON specials.product_d = products.id
                  AND specials.from_date < NOW()
                  AND specials.to_date > NOW()
WHERE product_info.LANGUAGE = 'en'
  AND product_info.title LIKE ?
ORDER BY CASE 
    WHEN specials.product_id IS NOT NULL THEN 2
    ELSE 1
    END DESC,
  products.id DESC,
  products.created_at DESC