GROUP BY分组查询与SQL执行顺序
在SQL中使用GROUP BY来对SELECT的结果进行数据分组,在具体使用GROUP BY之前需要知道一些重要的规定。 GROUP BY子句可以包含任意数目的列。也就是说可以在组里再分组,为数据分组提供更细致的控制。 如果在GROUP BY子句中指定多个分组,数据将在最后指定的分
在SQL中使用GROUP BY来对SELECT的结果进行数据分组,在具体使用GROUP BY之前需要知道一些重要的规定。
- GROUP BY子句可以包含任意数目的列。也就是说可以在组里再分组,为数据分组提供更细致的控制。
- 如果在GROUP BY子句中指定多个分组,数据将在最后指定的分组上汇总。
- GROUP BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用了表达式,则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
- 出了聚集计算语句外,SELECT语句中的每一列都必须在GROUP BY子句中给出。
- 如果分组列中有NULL值,则NULL将作为一个分组返回。如果有多行NULL值,它们将分为一组。
- GROUP BY子句必须在WHERE子句之后,ORDER BY之前。
过滤分组
对分组过于采用HAVING子句。HAVING子句支持所有WHERE的操作。HAVING与WHERE的区别在于WHERE是过滤行的,而HAVING是用来过滤分组。
另一种理解WHERE与HAVING的区别的方法是,WHERE在分组之前过滤,而HAVING在分组之后以每组为单位过滤。
分组与排序
一般在使用GROUP BY子句时,也应该使用ORDER BY子句。这是保证数据正确排序的唯一方法。
SQL SELECT语句的执行顺序:
- from子句组装来自不同数据源的数据;
- where子句基于指定的条件对记录行进行筛选;
- group by子句将数据划分为多个分组;
- 使用聚集函数进行计算;
- 使用having子句筛选分组;
- 计算所有的表达式;
- 使用order by对结果集进行排序;
- select 集合输出。
举个例子吧。
select 考生姓名, max(总成绩) as max总成绩 from tb_Grade where 考生姓名 is not null group by 考生姓名 having max(总成绩) > 600 order by max总成绩
在上面的示例中 SQL 语句的执行顺序如下:
- 首先执行 FROM 子句, 从 tb_Grade 表组装数据源的数据
- 执行 WHERE 子句, 筛选 tb_Grade 表中所有数据不为 NULL 的数据
- 执行 GROUP BY 子句, 把 tb_Grade 表按 "学生姓名" 列进行分组
- 计算 max() 聚集函数, 按 "总成绩" 求出总成绩中最大的一些数值
- 执行 HAVING 子句, 筛选课程的总成绩大于 600 分的.
- 执行 ORDER BY 子句, 把最后的结果按 "Max 成绩" 进行排序.

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

HQL and SQL are compared in the Hibernate framework: HQL (1. Object-oriented syntax, 2. Database-independent queries, 3. Type safety), while SQL directly operates the database (1. Database-independent standards, 2. Complex executable queries and data manipulation).

Cloud storage has become an indispensable part of our daily life and work nowadays. As one of the leading cloud storage services in China, Baidu Netdisk has won the favor of a large number of users with its powerful storage functions, efficient transmission speed and convenient operation experience. And whether you want to back up important files, share information, watch videos online, or listen to music, Baidu Cloud Disk can meet your needs. However, many users may not understand the specific use method of Baidu Netdisk app, so this tutorial will introduce in detail how to use Baidu Netdisk app. Users who are still confused can follow this article to learn more. ! How to use Baidu Cloud Network Disk: 1. Installation First, when downloading and installing Baidu Cloud software, please select the custom installation option.

NetEase Mailbox, as an email address widely used by Chinese netizens, has always won the trust of users with its stable and efficient services. NetEase Mailbox Master is an email software specially created for mobile phone users. It greatly simplifies the process of sending and receiving emails and makes our email processing more convenient. So how to use NetEase Mailbox Master, and what specific functions it has. Below, the editor of this site will give you a detailed introduction, hoping to help you! First, you can search and download the NetEase Mailbox Master app in the mobile app store. Search for "NetEase Mailbox Master" in App Store or Baidu Mobile Assistant, and then follow the prompts to install it. After the download and installation is completed, we open the NetEase email account and log in. The login interface is as shown below

MetaMask (also called Little Fox Wallet in Chinese) is a free and well-received encryption wallet software. Currently, BTCC supports binding to the MetaMask wallet. After binding, you can use the MetaMask wallet to quickly log in, store value, buy coins, etc., and you can also get 20 USDT trial bonus for the first time binding. In the BTCCMetaMask wallet tutorial, we will introduce in detail how to register and use MetaMask, and how to bind and use the Little Fox wallet in BTCC. What is MetaMask wallet? With over 30 million users, MetaMask Little Fox Wallet is one of the most popular cryptocurrency wallets today. It is free to use and can be installed on the network as an extension

Download the latest version of 12306 ticket booking app. It is a travel ticket purchasing software that everyone is very satisfied with. It is very convenient to go wherever you want. There are many ticket sources provided in the software. You only need to pass real-name authentication to purchase tickets online. All users You can easily buy travel tickets and air tickets and enjoy different discounts. You can also start booking reservations in advance to grab tickets. You can book hotels or special car transfers. With it, you can go where you want to go and buy tickets with one click. Traveling is simpler and more convenient, making everyone's travel experience more comfortable. Now the editor details it online Provides 12306 users with a way to view historical ticket purchase records. 1. Open Railway 12306, click My in the lower right corner, and click My Order 2. Click Paid on the order page. 3. On the paid page

How to check my academic qualifications on Xuexin.com? You can check your academic qualifications on Xuexin.com, but many users don’t know how to check their academic qualifications on Xuexin.com. Next, the editor brings you a graphic tutorial on how to check your academic qualifications on Xuexin.com. Interested users come and take a look! Xuexin.com usage tutorial: How to check your academic qualifications on Xuexin.com 1. Xuexin.com entrance: https://www.chsi.com.cn/ 2. Website query: Step 1: Click on the Xuexin.com address above to enter the homepage Click [Education Query]; Step 2: On the latest webpage, click [Query] as shown by the arrow in the figure below; Step 3: Then click [Login Academic Credit File] on the new page; Step 4: On the login page Enter the information and click [Login];

Xiaomi car software provides remote car control functions, allowing users to remotely control the vehicle through mobile phones or computers, such as opening and closing the vehicle's doors and windows, starting the engine, controlling the vehicle's air conditioner and audio, etc. The following is the use and content of this software, let's learn about it together . Comprehensive list of Xiaomi Auto app functions and usage methods 1. The Xiaomi Auto app was launched on the Apple AppStore on March 25, and can now be downloaded from the app store on Android phones; Car purchase: Learn about the core highlights and technical parameters of Xiaomi Auto, and make an appointment for a test drive. Configure and order your Xiaomi car, and support online processing of car pickup to-do items. 3. Community: Understand Xiaomi Auto brand information, exchange car experience, and share wonderful car life; 4. Car control: The mobile phone is the remote control, remote control, real-time security, easy

Go language is a simple, efficient, and highly concurrency programming language. It is an open source language developed by Google. In the Go language, the use of spaces is very important, it can improve the readability and maintainability of the code. This article will introduce how to use spaces correctly in Go language and provide specific code examples. Why you need to use spaces correctly In the programming process, the use of spaces is very important for the readability and beauty of the code. Appropriate use of spaces can make code clearer and easier to read, thus reducing
