SQL中的join，group by

SQL中的join，group by

1.组合俩个表：

输入: 
Person表:
+----------+----------+-----------+
| personId | lastName | firstName |
+----------+----------+-----------+
| 1        | Wang     | Allen     |
| 2        | Alice    | Bob       |
+----------+----------+-----------+
Address表:
+-----------+----------+---------------+------------+
| addressId | personId | city          | state      |
+-----------+----------+---------------+------------+
| 1         | 2        | New York City | New York   |
| 2         | 3        | Leetcode      | California |
+-----------+----------+---------------+------------+
输出: 
+-----------+----------+---------------+----------+
| firstName | lastName | city          | state    |
+-----------+----------+---------------+----------+
| Allen     | Wang     | Null          | Null     |
| Bob       | Alice    | New York City | New York |
+-----------+----------+---------------+----------+
解释: 
地址表中没有 personId = 1 的地址，所以它们的城市和州返回 null。
addressId = 1 包含了 personId = 2 的地址信息。

select firstName, lastName, city, state
from Person left join Address
on Person.PersonId = Address.PersonId
;

考虑到可能不是每个人都有地址信息，我们应该使用 outer join 而不是默认的 inner join。

inner join：2表值都存在

outer join：附表中值可能存在null的情况。

总结：

①A inner join B：取交集

②A left join B：取A全部，B没有对应的值，则为null

③A right join B：取B全部，A没有对应的值，则为null

④A full outer join B：取并集，彼此没有对应的值为null

超过经理收入的员工

输入: 
Employee 表:
+----+-------+--------+-----------+
| id | name  | salary | managerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | Null      |
| 4  | Max   | 90000  | Null      |
+----+-------+--------+-----------+
输出: 
+----------+
| Employee |
+----------+
| Joe      |
+----------+
解释: Joe 是唯一挣得比经理多的雇员。

使用join进行连接

SELECT a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
ON a.ManagerId = b.Id
AND a.Salary > b.Salary
;

3.group by的分组方法(利于使用聚合函数）：

当你在 SQL 查询中使用 GROUP BY 子句时，结果集会形成一个新的表视图，其中的行是根据指定列的不同值组合进行分组的。每个组代表了原始表中在分组列上具有相同值的所有行的聚合视图。这个“新表”不是物理存在的表，而是查询结果的逻辑展示。

结构和内容

这个通过 GROUP BY 生成的表通常包含：

分组列：

GROUP BY 子句中指定的每个列都将作为结果集的一部分出现。每个组的这些列的值是相同的，即这些值是该组中所有行共有的。

聚合列：

在 SELECT 子句中包含的任何聚合函数（如 SUM(), AVG(), COUNT(), MIN(), MAX() 等）的结果。这些列显示了对分组后的行执行聚合计算的结果。

在使用 SQL 的 GROUP BY 子句时，你不一定必须使用聚合函数，但通常来说，GROUP BY 是为了配合聚合函数（如 COUNT(), SUM(), AVG(), MIN(), MAX() 等）来使用的，因为它的主要功能是对分组后的数据进行聚合计算。单独使用GROUP BY可以去重。

示例

假设有一个 Orders 表，结构如下：

+---------+----------+-------+
| OrderID | Customer | Total |
+---------+----------+-------+
|       1 | Alice    |   100 |
|       2 | Bob      |    50 |
|       3 | Alice    |   150 |
|       4 | Alice    |   200 |
|       5 | Bob      |   100 |
+---------+----------+-------+

如果执行以下 SQL 查询：

SELECT Customer, COUNT(OrderID) AS NumberOfOrders, SUM(Total) AS TotalSpent
FROM Orders
GROUP BY Customer;

结果将是一个由 GROUP BY 子句生成的表，大致如下：

+----------+---------------+-----------+
| Customer | NumberOfOrders| TotalSpent|
+----------+---------------+-----------+
| Alice    |             3 |       450 |
| Bob      |             2 |       150 |
+----------+---------------+-----------+

Activity 表：
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-05-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+

Result 表：
+-----------+-------------+
| player_id | first_login |
+-----------+-------------+
| 1         | 2016-03-01  |
| 2         | 2017-06-25  |
| 3         | 2016-03-02  |
+-----------+-------------+

select a.player_id,min(event_date) as first_login
from activity a
group by a.player_id;

count结合group by，where和having使用

SELECT column_name, COUNT(*)
FROM tableName
WHERE another_column > 10
GROUP BY column_name
HAVING COUNT(*) > 5;