数据库sql语句练习（三）

例题：

 ●哪些项目消费了不止一次

select distinct name'消费了不止一次的项目'
from shopping
group by name
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(total)最贵消费,
min(total)最便宜消费,
max(total)-min(total)差值
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))+'%''买手机所占总消费百分比'