mysql操作遇到的坑(第一版)
1、当我们要统计数据表数量时,如果遇到多表查询,会出现一个主表对应多个子表的维度,我们会用到group by,但是不要再用统计函数去操作数据,因为统计还是会统计原数据
案例
SELECT
sum(`order`.goods_amount) AS total_goods_amount,
sum(`order`.order_amount) AS total_order_amount,
sum(`order`.redbag_amount) AS total_redbag_amount,
sum(`order`.spread_amount) AS total_spread_amount,
sum(`order`.redrain_amount) AS total_redrain_amount
FROM
ds_order AS `order`
LEFT JOIN ds_refundreturn AS refundreturn ON `order`.order_id = refundreturn.order_id
WHERE
1 = 1
AND `order`.order_state = '40'
AND refundreturn.refund_state < '3'
AND refundreturn.is_revoke = '0'
GROUP BY
`order`.order_id
这样得到的数据,会把子表多个订单ID给统计上去,而分组并没有限制到
2、可以用数据库子查询,最后再加一个总查询
select sum(a.total_goods_marketprice) from (
SELECT
(
goods.goods_marketprice * ordergoods.goods_num
) AS total_goods_marketprice
FROM
ds_order AS `order`
LEFT JOIN ds_refundreturn AS refundreturn ON `order`.order_id = refundreturn.order_id
LEFT JOIN ds_ordergoods AS ordergoods ON `order`.order_id = ordergoods.order_id
LEFT JOIN ds_goods AS goods ON ordergoods.goods_id = goods.goods_id
WHERE
1 = 1
AND `order`.order_state = '40'
AND refundreturn.refund_state < '3'
AND refundreturn.is_revoke = '0'
GROUP BY
`order`.order_id
)a;
3、mysql group concat 长度是有限制的,如果数据太多,会截取
4、如果在不同电脑查询值,排序值得到的数据是相同的,有可能会显示不同顺序的数据
5、mysql看or与in的条件是一样的,但是数量大的时候,or会影响性能
亲,后面还会陆续更新!
最新文章
- [Linux] 无法访问国外网站,完成epel源安装的解决办法--待续
- Spring Boot项目的打包和部署
- 20145221 《Java程序设计》实验报告二:Java面向对象程序设计
- NSKeyValueObserving(KVO)
- [CCF2015.09]题解
- javascript数组操作汇总
- Linux之旅(1): diff, patch和quilt (下)
- IOS数据持久化之归档NSKeyedArchiver
- [SCOI2007]蜥蜴
- SQL更新语句,Error Code: 1175. You are using safe update(在进行视图更新的时候遇到)
- memcached实战系列(二)memcached参数以及启动
- jQuery.prop , jQuery.attr ,jQuery.data
- 多线程的实现方式01 Thread
- 怎样用CMD命令强行删除文件?
- MongoDB学习3 $操作符表达式大全及实例
- 【二维树状数组】【CF10D】 LCIS
- 题解【bzoj3240 [NOI2013]矩阵游戏】
- SpringMVC组件配置
- idea的常用设置
- Zabbix: Database Monitor Installation
热门文章
- HelloDjango 第 13 篇:分类、归档和标签页
- P3317 [SDOI2014]重建 变元矩阵树定理 高斯消元
- 【CF 1059C】 Sequence Transformation 数学
- lightoj 1086 - Jogging Trails(状压dp)
- SSM框架学习笔记(一)
- FreeSql (二)自动迁移实体
- Airflow:TypeError an integer is required (got type NoneType) 一次诡异问题排查
- Android mmap 文件映射到内存介绍
- C++11新增容器以及元组
- Aspose.Cell导出带chart图表