数据库原理 复杂查询

(1) 在订单明细表中查询订单金额最高的订单。

select orderNO,sum(quantity*price) as ordersum
from OrderDetail a group by orderNo
having sum(quantity*price)=
(select max(ordersum) from (select orderNO,sum(quantity*price) as ordersum
from OrderDetail group by orderNo)a)
(2) 找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。
SELECT a.productNo,orderNo,quantity,(quantity*price) money
FROM OrderDetail a, (SELECT productNo
FROM OrderDetail
GROUP BY productNo
HAVING count(*)>=3) b
WHERE a.productNo =b.productNo
ORDER BY a.productNo,quantity DESC

(3) 查找销售总额少于5000元的销售员编号、姓名和销售额。
select a.employeeNo,a.employeeName,sum(quantity*price) sunmoney
from Employee a,OrderDetail b,OrderMaster c
where a.employeeNo=c.salerNo and b.orderNo=c.orderNo
group by a.employeeNo,a.employeeName
having sum(quantity*price)<5000