小弟正在学习sql中的group by,可是不太会写,希望前辈们指点一下!!表:orderid代表订单号,regin代表区域,name代表销售人员,total代表订单金额ID regin name total1 A tom 100
2 B jack 50
3 A gg 200
4 B mm 150
5 B jack 100
7 A tom 50问题一:如何将每个区域中单笔订单金额最小的订单号与对应的销售人员查询出来?
问题二:如何将每个区域中小于本区域内订单金额平均值的订单号与人员查询出来?
问题三:如何将每个区域中每个销售人员的金额查询出来?
2 B jack 50
3 A gg 200
4 B mm 150
5 B jack 100
7 A tom 50问题一:如何将每个区域中单笔订单金额最小的订单号与对应的销售人员查询出来?
问题二:如何将每个区域中小于本区域内订单金额平均值的订单号与人员查询出来?
问题三:如何将每个区域中每个销售人员的金额查询出来?
select ID ,name from order
where total in(select Min(total) from order group by regin)select ID ,name from order
where ID in(select ID from order group by regin having avg(total)>total)select sum(total ) from order
group by regin,name
SELECT id, regin, name, rank() over(partition by regin order by total desc) rn
FROM order)
WHERE rn = 1;select id, name from(
select id, regin, name, total, avg(total) over(partition by regin) avgtotal from order)
where total < avgtotal;select regin, name, sum(total)
from order
group by regin, name;
select ID ,name from "order" a
where exists(select regin, Min(total) from "order" group by regin)--2,如何将每个区域中小于本区域内订单金额平均值的订单号与人员查询出来? select ID ,name from "order"
where ID in(select 1 from "order" group by regin having avg(total)>total)--3,如何将每个区域中每个销售人员的金额查询出来?
select regin,name,sum(total ) from "order"
group by regin,name
select a.name
from order a , (select regin, min(total) as minTotal from order group by regin) b
where a.regin=b.regin and a.total=b.mintotal问题二:如何将每个区域中小于本区域内订单金额平均值的订单号与人员查询出来?
select a,id,a.name
from order a , (select regin, avg(total) as avgTotal from order group by regin) b
where a.regin=b.regin and a.total<b.avgTotal
问题三:如何将每个区域中每个销售人员的金额查询出来?
select regin,name,sum(total)
from order
group by regin,name,sum另外一般来说区域写成region比常见