Py学习  »  DATABASE

返回mysql查询中所有的wow、mached和unmatched

user1504222 • 6 年前 • 1262 次点击  

我有两个表,我想查询两个表来获得一个报告。

POSITION
+-------------+---------------+
| position_id | position_name |
+-------------+---------------+
|     1       |     E1P1      |
|     2       |     E1P2      |
|     3       |     E3P3      |
|     4       |     E4P4      |
+-------------+---------------+   

PEOPLE
+------------+-------------+--------------------+
| people_id  | people_name | people_position_id |
+------------+-------------+--------------------+
|     1      |    JOHN     |         2          |
|     2      |    MARK     |         4          |
+------------+-------------+--------------------+

查询

SELECT position_id, position_name, people_name FROM position
RIGHT JOIN people ON people_position_id = position_id

当我使用简单的查询时,我只得到匹配的行,如何获取所有行?

我想得到这个结果

+----+----------+--------+
| ID | POSITION | STATUS |
+----+----------+--------+
| 1  |   E1P1   | Empty  |
| 2  |   E1P2   | JOHN   |
| 3  |   E3P3   | Empty  |
| 4  |   E4P4   | MARK   |
+----+----------+--------+
Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/34278
 
1262 次点击  
文章 [ 1 ]  |  最新文章 6 年前
Tim Biegeleisen
Reply   •   1 楼
Tim Biegeleisen    6 年前

SELECT
    po.position_id,
    po.position_name,
    COALESCE(pe.people_name, 'EMPTY') AS STATUS
FROM position po
LEFT JOIN people pe
    ON po.position_id = pe.people_position_id;

people

SELECT
    po.position_id,
    po.position_name,
    COALESCE(pe.people_name, 'EMPTY') AS STATUS
FROM people pe
RIGHT JOIN position po
    ON po.position_id = pe.people_position_id;