AVG ， COUNT，GROUP的简单用法案例

来源：互联网发布：各种编程语言编辑：程序博客网时间：2024/05/20 05:59

首先建一个orders表如下：

附上代码：

create table orders(O_Id int,OrderDate varchar(20),Orderprice int ,Customer varchar(10));insert into orders values(1,'2008/12/29',1000,'Bush');insert into orders values(2,'2008/11/23',1600,'Carter');insert into orders values(3,'2008/10/05',700,'Bush');insert into orders values(4,'2008/09/28',300,'Bush');insert into orders values(5,'2008/08/06',2000,'Adams');insert into orders values(6,'2008/07/21',100,'Carter');

--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;

阅读全文

1 0