1、查询出销售表中,销售额大于本地区平均水平的记录,用一条sql语句
--创建sales表
create table sales( OrderID int,Region char(1),Total float )
--向表中插入数据
insert into sales
select '2','C',80.00 union
select '3','A',130.00 union
select '4','B',90.00 union
select '6',120.00 union
select '7',90.00 union
select '9',80.00 union
select '1',100.00 union
select '5',100.00 union
select '8',90.00
--容易出现的错误例子:
select *
from sales
where total>sum(total)/count(1)
group by region
--正确的查询:
select a.* from sales a,(select region,sum(total)/count(1) as av from sales group by region) b
where a.region=b.region and a.total>b.av