mysql 先分组在排序

时间:2021-12-25 00:55:25

mysql语句的语法模板:

select distinct <select_list>

from 

<left_table><join_type> join <right_table> on <join_condition>

where <where_condition>

group by <group_by_rowname>

having <having_condition>

order by <order_by_condition>

limit <limit_number>

mysql的执行顺序如图:mysql 先分组在排序

 

mytest表 如图:

mysql 先分组在排序

想先根据department字段分组,在按照price字段排序:SELECT * FROM mytest ORDER BY department ,price

想查询每个department字段的最大price值时:SELECT id ,department,MAX(price) FROM mytest GROUP BY department

多表关联查询时,表结构如图:

user表

mysql 先分组在排序

 

pay 表

mysql 先分组在排序

spay表:

mysql 先分组在排序

 

其中pay表 是用户缴费表,spay表 是应该缴费表

想要查询用户欠费的记录时查询如下:

SELECT spay.userId, (T.mypay-spay.shouldPay) AS own FROM

(SELECT userId,SUM(mypay) AS mypay FROM pay GROUP BY userId) AS T LEFT JOIN spay ON spay.`userId`=T.userId

 

注意:有时候需要用到关键字union关键字,union是合并+去重的操作