java - 选择查询并按条件排序

Question

我想从此 SQL 查询创建条件查询:

SELECT * FROM `Product` p
ORDER BY (SELECT COUNT(*) FROM `Sale` WHERE `product_id` = p.id) DESC

我找不到使用子查询执行此操作的方法...

Answer 1

Order By 仅适用于 select 部分中存在的列。

所以基本上像这样的东西会起作用:

SELECT Group, COUNT(*) as total
FROM table
GROUP BY Group
ORDER BY total DESC

因此，对于您的情况，您可以执行以下操作:

SELECT *, (SELECT COUNT(*) FROM `Sale` WHERE `product_id` = p.id) as total FROM `Product` p
ORDER BY total DESC

注意:这完全未经测试。