mysql - WHERE 子句中嵌套 SQL 语句的结果

Question

我有一个节拍表，对于每个节拍，定价表中有多行包含不同的价格。

SELECT b.id, b.name,
(SELECT p.price FROM pricing as p WHERE p.license = 1 AND p.beat_id = b.id) as price_1,
(SELECT p.price FROM pricing as p WHERE p.license = 2 AND p.beat_id = b.id) as price_2
FROM beats as b 
WHERE b.added > 0 
AND b.active = 1 
AND b.deleted = 0 
AND price_1 > 0
ORDER BY b.id DESC 
LIMIT 50

我试图确保仅当 price_1 大于 0 时才检索 beat。

这不起作用，因为您无法在 WHERE 子句中使用嵌套 SQL 语句的结果，但我尝试过 HAVING Price_1 > 0 并这也行不通。

如何测试 price_1 和 price_2？

Answer 1

您可以将该条件移至 having 子句。这是MySQL的特性，其他数据库不支持:

SELECT b.id, b.name,
       (SELECT p.price FROM pricing as p WHERE p.license = 1 AND p.beat_id = b.id) as price_1,
       (SELECT p.price FROM pricing as p WHERE p.license = 2 AND p.beat_id = b.id) as price_2
FROM beats as b 
WHERE b.added > 0 AND b.active = 1 AND b.deleted = 0 
GROUP BY b.producer 
HAVING price_1 > 0
ORDER BY b.id DESC 
LIMIT 50;