一些分类统计项目实例
来源:互联网 发布:java手写ioc 编辑:程序博客网 时间:2024/06/09 13:03
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 53连表分组查询统计
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 -- 多利用索引查询--限制查询范围--避免返回数据量过大
使用缓存--做完之后再去做水平垂直切分--优化表结构
阅读全文
0 0
- 一些分类统计项目实例
- 分类统计
- 项目中的一些Javascript应用实例
- 在github上一些好的项目统计给大家
- 按客户分类统计
- 分类统计的SQL- -
- 分类统计数据库数据
- sql 统计和分类
- 1053 字符分类统计
- 统计学习分类名录
- SQL 分类条件统计
- 统计算法分类
- mysql 实现分类统计
- 分类统计数量sql
- 1.1 统计学习分类
- 1066: 字符分类统计
- 按月分类累计统计库存
- Matlab统计工具箱分类实验
- C语言常量字符串
- g++编译opencv项目
- 成绩录入
- lvm让你的设备无限拉伸
- templibrary
- 一些分类统计项目实例
- 笔记11
- ubuntu14.04 下安装 gsl 科学计算库
- IO
- Android学习--封装成json数据和解析json数据
- IDEA设置生成类基本注释信息
- Jquery.sumoselect插件实现下拉复选框
- 输入输出姓名
- 如何移植开源软件到嵌入式linux系统