Order By 排序、Group By 分組

排序 ORDER BY

• ASC 從小到大(預設)
• DESC 從大到小
• limit 筆數設定

薪水排序

SELECT name, salary
FROM users
ORDER BY salary ASC;

多條件排序方法

SELECT name, salary, team_id
FROM users
ORDER BY team_id ASC, salary ASC;

部門排序後，再用薪資排序。

Join組合與排序

SELECT users.name, users.salary, teams.name
FROM users
INNER JOIN teams ON users.team_id = teams.id
WHERE teams.name = '人事部'
ORDER BY salary ASC;

語法順序
SELEC > FROM > INNER JOIN > WHERE > ORDER BY

分組 GROUP BY

適合將資料進行分組，搭配聚合函數(COUNT、SUM、AVG、MAX、MIN)進行分組計算。

計算每個部門的人數

SELECT
    team_id AS 部門ID,
    COUNT(*) AS 人數
FROM users
GROUP BY team_id;

計算個部門人數(加上部門名稱)

SELECT
    team_name AS 部門名稱,
    COUNT(*) AS 人數
FROM users
JOIN teams ON users.team_id = teams.id  -- (INNER) JOIN
GROUP BY team_id;