当前位置:网站首页>Can MySQL use aggregate functions without GROUP BY?
Can MySQL use aggregate functions without GROUP BY?
2022-08-05 09:32:00 【CSDN q&a】
I am a new data product, and I wrote a query today for a store that has not generated orders in the last 15 days.The SQL written by Data Development is 8 times faster than the results I wrote.
There is one place that I really don't understand, please ask for help humbly.
Written by me
SELECT s.id FROM `store`s JOIN ( SELECT `store_id` , MAX( `create_time`)t FROM `order` GROUP BY `store_id` )aON s.id = a.store_idWHERE s.channel = 2 ANDs.show = 1 AND DATEDIFF(now(), a.t ) > 15 Written by Data Development
SELECT idFROM `store`s where s.channel = 2 AND s.show = 1 AND DATEDIFF(now(), (SELECT MAX( `create_time`) FROM `order` o where o.store_id = s.id)) > 15 After reading the performance analysis of my SQL, it is because there are too many rows of order table data (about tens of millions), and the GROPU BY grouping is used to cause performance.
The development and writing avoided the use of GROPU BY, but my skills are relatively shallow, so I can't understand it:
In AND DATEDIFF(now(), (SELECT MAX(`create_time`) FROM `order` o where o.store_id = s.id)) > 15 In this query condition, the subquery statement outputs MAX time, but if group aggregation is not used, the output is notShould there be only one row of data?Why can I achieve my requirement of grouping by store_id?Although I probably guessed that the core lies in the following o.store_id = s.id, but I didn't understand it
边栏推荐
- The Seven Weapons of Programmers
- Going to book tickets tomorrow, ready to go home~~
- 欧盟 | 地平线 2020 ENSEMBLE:D2.13 SOTIF Safety Concept(上)
- Example of Noise Calculation for Amplifier OPA855
- Xcode10的打包方式distribute app和启动项目报错以及Xcode 打包本地ipa包安装到手机上
- IO流篇 -- 基于io流实现文件夹拷贝(拷贝子文件夹及子文件夹内文件)满满的干货
- 无题十一
- matcher中find,matches,lookingAt匹配字符串的不同之处说明
- 无题九
- The Secrets of the Six-Year Team Leader | The Eight Most Important Soft Skills of Programmers
猜你喜欢
随机推荐
如何实现按键的短按、长按检测?
How ali cloud storage database automatically to speed up the loading speed of www.cxsdkt.cn how to set up the case?
21 Days of Deep Learning - Convolutional Neural Networks (CNN): Clothing Image Classification (Day 3)
营销建议 | 您有一份八月营销月历待查收! 建议收藏 !
HStreamDB Newsletter 2022-07|分区模型优化、数据集成框架进一步完善
2.4G无线收发模块的应用
【零基础玩转BLDC系列】无刷直流电机无位置传感器三段式启动法详细介绍及代码分享
ECCV 2022 Oral 视频实例分割新SOTA:SeqFormer&IDOL及CVPR 2022 视频实例分割竞赛冠军方案...
(转)[Json]net.sf.json 和org.json 的差别及用法
Weekly Report 2022-8-4
Imitation SBUS fixed with serial data conversion
PAT乙级-B1021 个位数统计(15)
pytorch余弦退火学习率CosineAnnealingLR的使用
Going to book tickets tomorrow, ready to go home~~
Keil升级到AC6后,到底有哪些变化?
Rotation of the displayed value on the button
什么是CRM决策分析管理?
shell脚本实例
Xcode10的打包方式distribute app和启动项目报错以及Xcode 打包本地ipa包安装到手机上
Xcode 12 ld: symbol(s) not found for architecture armv64









