首先建一个orders表如下:
附上代码:
--AVG的用法
--1.找到 OrderPrice 值高于 OrderPrice 平均值的客户。
select Customer from orders where Orderprice > (select avg(Orderprice) from orders);
--COUNT的用法
--2.希望计算客户 "Carter" 的在customer中出现的次数。
select count(Customer) as '次数' from orders where customer = 'carter';
--3.查找carter中有几种不同的顾客 count(distinct column_name)
select count(distinct customer) as'顾客人数' from orders ;
--GROUP BY的用法
--4.希望查找每个客户的总金额
select Customer'顾客名' ,sum(orderprice)'总金额' from orders group by customer;
--HAVING的用法
--5.查找订单总金额少于 2000 的客户
select customer'客户' ,sum(orderprice)'总金额' from orders group by customer having sum(orderprice)<2000;
--6.希望查找客户 "Bush" 或 "Adams" 拥有超过 1500 的订单总金额
select customer'客户' ,sum(orderprice)'总金额' from orders where customer = 'bush' or customer = 'adams' group by customer having sum(orderprice)>1500;