我已经写了一个sql查询,但我不知道它是不是最好的。
我有两张table,关系是1-n。
订单表
|---------------------|
| orderNumber |
|---------------------|
| status |
|---------------------|
订单详细信息
|---------------------|
| orderNumber |
|---------------------|
| quantity |
|---------------------|
| price |
|---------------------|
我想得到订单,状态是 shipped
总价格大于1500,总价格呈下降趋势
SELECT * FROM order JOIN
(Select sub1.orderNumber, SUM(sub1.totalEach) AS total FROM (
select (quantity * price) AS totalEach, orderNumber FROM orderDetail)
AS sub1 GROUP BY sub1.orderNumber HAVING total > 1500 ORDER BY total DESC
) AS sub2
ON order.orderNumber = sub2.orderNumber
WHERE order.status = 'shipped'
1条答案
按热度按时间lnxxn5zx1#
可以避免子查询;并使用
Inner join
在两张table之间,使用Group by
与Having
.请尝试以下操作: