一些分类统计项目实例

1按时间的分组统计

select from_unixtime(create_time,'%Y%m%d') as days,count(*) as s from tpshop_order where create_time 
        between UNIX_TIMESTAMP(date_sub(now(),interval 30 DAY)) and  UNIX_TIMESTAMP(now()) group by days order by create_time asc

2固定月按天分组统计

select from_unixtime(create_time,'%Y%m%d') as days,count(*) as s from tpshop_order where 
        FROM_unixtime(create_time,'%Y-%m')=date_format(DATE_SUB(curdate(),INTERVAL 1 MONTH),'%Y-%m') and  UNIX_TIMESTAMP(now())
        group by days order by create_time asc limit 30

select sum(a.goods_price) as price,b.goods_name,b.id,FROM_unixtime(c.create_time,'%Y.%m.%d') 
        as time from tpshop_order_goods as a left join tpshop_goods as b on a.goods_id=b.id left join tpshop_order
        as c on a.order_id=c.id group by a.goods_id limit 5

3连表分组查询统计

select t1.id,t1.order_sn,t1.order_amount,FROM_UNIXTIME(t1.create_time, '%Y%m%d %H%i%s' ) 
        as ctime,t1.update_time,t2.username,group_concat(t3.goods_id) as goods_ids,t3.goods_attr_ids from tpshop_order as t1 join 
        tpshop_user as t2 on t1.user_id=t2.id  join tpshop_order_goods as t3 on t1.id=t3.order_id group by t1.id




4按需求查询

select t1.id,t1.order_sn,t1.order_amount,FROM_UNIXTIME(t1.create_time, '%Y%m%d %H%i%s' ) 
        as ctime,t1.update_time,t2.username,group_concat(t3.goods_id) as goods_ids,t3.goods_attr_ids from tpshop_order as t1 join 
         tpshop_user as t2 on t1.user_id=t2.id  join tpshop_order_goods as t3 on t1.id=t3.order_id where t2.username
         like '%$name%' group by t1.id
效率问题

避免全表扫描 少用where -- 多利用索引查询--限制查询范围--避免返回数据量过大
使用缓存--做完之后再去做水平垂直切分--优化表结构