mysql - 在 MySQL 中创建具有空值的 VIEW

Question

我正在尝试通过像这样连接 2 个表来在 MySQL 中创建一个 View :

用户表:

+----+------+  
| id | name |
+----+------+
| 1  | Joy  |
+----+------+
| 2  | Roy  |
+----+------+
| 3  | Tony |
+----+------+  

技能表:

+---------+---------+------------+
| auto_id | user_id | skill      |
+---------+---------+------------+
| 1       | 1       | PHP        |
+---------+---------+------------+
| 2       | 1       | CSS        |
+---------+---------+------------+
| 3       | 2       | Ruby       |
+---------+---------+------------+
| 4       | 2       | Javascript |
+---------+---------+------------+  

此处Skills表中的user_id是Users中id的外键> 表格。

创建 View 的查询:

CREATE OR REPLACE VIEW user_view AS SELECT u.id, u.name,  GROUP_CONCAT(s.skill) as 
skills FROM users u, skills s WHERE u.id = s.user_id GROUP BY u.id  

现在的问题是创建的 View 只有 2 行，我知道这是因为 user_id 3 的技能表中没有值。现在我想要的是将具有 Tony's 技能的所有 3 行作为 Null 值，如下所示:

+---------+------+-----------------+
| user_id | name | skills          |
+---------+------+-----------------+
| 1       | Joy  | PHP,CSS         |
+---------+------+-----------------+
| 2       | Roy  | Ruby,Javascript |
+---------+------+-----------------+
| 3       | Tony | Null or empty   |
+---------+------+-----------------+

Answer 1

您正在寻找左连接:

CREATE OR REPLACE VIEW user_view AS
    SELECT 
        u.id,
        u.name,
        GROUP_CONCAT(s.skill) AS skills
    FROM
        users u
            LEFT JOIN
        skills s ON u.id = s.user_id
    GROUP BY u.id