一些常用的 sql语句总结

-- 查询卖最好的商品名称
select goods_name from indent_detail GROUP BY goods_id ORDER BY sum(goods_num) limit 1;

-- 查询张三购买过的商品名称
select  goods_name
from user join indent on indent.user_id=`user`.id
join indent_detail on indent.id=indent_detail.indent_id
where username='zhansan'

-- 查询消费金额最多的用户名称
select  username
from indent join user on `user`.id=indent.user_id
GROUP BY indent.user_id
order by sum(total_price) DESC
limit 1

--  查询订单量最多的省份名称
select  region_name from indent join region
on indent.province = region.region_id
GROUP BY indent.province
order by count(*) desc limit 1

-- 查询2014年各个省份的订单数量以及订单总金额 显示格式 省份名称  订单数量 订单总金额
select  region_name ,count(*) as number,sum(total_price) as total from indent join region
on indent.province = region.region_id
where year(add_time)=2014
GROUP BY indent.province

-- 查询山东省最近三个月的订单数量
select  count(*) as number from indent join region
on indent.province = region.region_id
where region_name='安徽'
and  date_sub(curdate(),INTERVAL 3 MONTH)<=add_time

-- 查询最近三天卖的最好的前十件商品名称
select goods_name
from indent_detail join indent on indent.id=indent_detail.indent_id
where TO_DAYS(now())-TO_DAYS(add_time)<=3
GROUP BY goods_id ORDER BY sum(goods_num) desc limit 10;

select goods_name
from indent_detail join indent on indent.id=indent_detail.indent_id
where date_sub(CURDATE(),INTERVAL 3 day)<=add_time
GROUP BY goods_id ORDER BY sum(goods_num) desc limit 10;

-- 查询安徽省滁州市最近三天的订单
select  *  from indent join region
on indent.city = region.region_id
where region_name='滁州'
and  date_sub(curdate(),INTERVAL 3 MONTH)<=add_time