MySQL分组数据和排序

总结

1. 分组可嵌套，可分得更细致
2. WITH ROLLUP在最后一行返回分组汇总之和
3. HAVING过滤分组，WHERE过滤行，若同时使用HAVING和WHERE，先用WHERE过滤数据后，HAVING用过滤后的数据再分组
4. GROUP BY和ORDER BY的区别，尽量都用ORDER BY避免错误
5. SELECT子句顺序：SELECT→FROM→WHERE→GROUP BY→HAVING→ORDER BY→LIMIT

 

1. GROUP BY加上 WITH ROLLUP，可以在最后一行返回分组汇总之和。

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;

+---------+-----------+

| vend_id | num_prods |

+---------+-----------+

|    1001 |         3 |

|    1002 |         2 |

|    1003 |         7 |

|    1005 |         2 |

|    NULL |        14 |

+---------+-----------+

2. HAVING：可规定 包括或排除哪些分组(基于完整的分组而不是个别行进行过滤)

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*)>2;

+---------+-----------+

| vend_id | num_prods |

+---------+-----------+

|    1001 |         3 |

|    1003 |         7 |

+---------+-----------+

3. WHERE和HAVING同时使用：先用WHERE过滤数据后，HAVING用过滤后的数据再分组

SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price>5 GROUP BY vend_id HAVING COUNT(*)>2;

+---------+-----------+

| vend_id | num_prods |

+---------+-----------+

|    1001 |         3 |

|    1003 |         4 |

+---------+-----------+

4. GROUP BY和ORDER BY的区别：一般用完GROUP BY以后再用ORDER BY，更保险

4.1 不用ORDER BY：

SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price>5 GROUP BY vend_id HAVING COUNT(*)>=2;

+---------+-----------+

| vend_id | num_prods |

+---------+-----------+

|    1001 |         3 |

|    1003 |         4 |

|    1005 |         2 |

+---------+-----------+

4.2 用了ORDER BY：

SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price>5 GROUP BY vend_id HAVING COUNT(*)>=2 ORDER BY num_prods;

+---------+-----------+

| vend_id | num_prods |

+---------+-----------+

|    1005 |         2 |

|    1001 |         3 |

|    1003 |         4 |

+---------+-----------+

5. SELECT子句顺序：

SELECT→FROM→WHERE→GROUP BY→HAVING→ORDER BY→LIMIT