sql面试题

--曲奇销售表
create table Sales(
  name varchar(10),
  sales float
)
insert into Sales values('britney',3.4)
insert into Sales values('britney',2.58)
insert into Sales values('britney',4.5)
insert into Sales values('Hardy',12.54)
insert into Sales values('Hardy',6.25)
insert into Sales values('Hardy',7.13)
insert into Sales values('Paris',1.52)
insert into Sales values('Paris',24.19)
insert into Sales values('Paris',31.99)


--平均分
SELECT name, AVG(sales) AS 平均分 FROM Sales GROUP by name ORDER BY 平均分 DESC




--按照姓名求出每个人最大的值
SELECT name , MAX(sales) AS 最大值 FROM Sales GROUP BY name




--安装姓名求出第二大的值
SELECT name, MAX(sales) AS 第二大值 FROM Sales
 WHERE sales NOT IN 
(
(SELECT MAX(sales)
  FROM Sales GROUP BY name)
)
GROUP BY name