mysql 先分组在排序

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的执行顺序如图：

 

mytest表 如图：

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

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

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

user表

 

pay 表

spay表：

 

其中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是合并+去重的操作