例题：
●哪些项目消费了不止一次
1. select distinct name'消费了不止一次的项目' 2. from shopping 3. group by name 4. having count(*)>1
注：加disdinct去除重复的返回值，不加默认为all
●按照不同消费项目的总金额从高到低的顺序，列出不同消费项目的名称，消费
次数，消费总数量和消费总金额
select name 消费项目, count(number)消费次数, sum(number)消费总数量, sum(price*number)消费总金额 from shopping group by name order by 4 desc;
●查询消费次数最多的项目的名称及其消费次数。
select name'消费次数最多的项目',count (number)”消费的次数 from shopping group by name having count(number) =( select top 1 count(number from shopping group by name order by count(number desc );
●查询消费总数量最多的项目的名称及其消费总数量。
select name'消费总数量最多的项目',sum(number)"消费总数量 from shopping group by name having sum(number) =( select top 1 sum(number) from shopping group by name order by sum(number) desc );
●查询所有累计花费低于200元的项目及其出现次数和累计消费
select name 项目, count (name) 出现次数, sum(price*number)累计花费 from shopping group by name having sum(price*number)<200
●最贵的项目总消费、最便宜的项目总消费，以及它们之间的价格差分别是多少?
select max(tota1)最贵消费, min(tota1)最便宜消费, max(total)-min(tota1)差值 from (select sum(price*number) total from shopping group by name) as a
●下个月除了不买手机之外，其他消费不变，3000元够吗?如果有多，返回“足
够了";如果刚好够，返回“刚好够”;如果不够，返回“太少了”。
重要：SIGN函数和case语句
select case SIGN(3000 - sum(price *number)) when -1 then'太少了' when 0 then'刚好够' else'足够了' end'3000元够不够?' from shopping where name not like'%手机%';
●查询手机消费占总消费的百分比
重要：转字符
select str((select sum (price * number) from shopping where name like'%手机%') *100.0/(select sum(price * number) from shopping))+'%''买手机所占总消费百分比'
