实际应用SQL语句收藏

查询各省每年水果产量前十

SELECT t.*  FROM `friut` t WHERE(SELECT COUNT(*) FROM friut WHERE pri =t.pri AND yea=t.yea AND product>t.product)<10
ORDER BY t.pri DESC,t.yea,t.product DESC
 

 

求选修课程大于2门的同学的基本信息和平均分

select id，name,avg(grade)from sc group by id having count(*)>2

 

 

 

时间戳按天分组

SELECT FROM_UNIXTIME(order_time,'%Y-%m-%d')AS DATE,COUNT(*) 
FROM orders WHERE order_status=1
GROUP BY FROM_UNIXTIME(order_time,'%Y-%m-%d');

持续跟新！！！