mysql - Mysql中的用户和报表表查询

Question

 User Table   
 ------------
| id | Name |
|------------
| 1  |  A   |
| 2  |  B   |
| 3  |  C   |
| 4  |  D   |
 ------------

 report Table   
 --------------------
| id | uid | status |
|--------------------
| 1  |  1   |   0   |
| 2  |  3   |   1   |   
| 3  |  2   |   0   |
| 4  |  3   |   1   |
 --------------------

假设我有两个表用户和报告。我想根据状态列对报告表进行查询，即

WHERE status=0

但我的问题是我不需要 uid，我想要用户表中的用户名，但该怎么做？请帮忙。

Answer 1

也许是这样的:

JOIN 语法

SELECT
    User.id,
    User.Name
FROM
    User
    JOIN report
        ON User.id=report.uid
WHERE
    report.status=0

EXISTS 语法

SELECT
    User.id,
    User.Name
FROM
    User
WHERE EXISTS
(
    SELECT
        NULL
    FROM
        report
    WHERE
        report.status=0
        AND User.id=report.uid
)

IN 语法

SELECT
    User.id,
    User.Name
FROM
    User
WHERE User.id IN
(
    SELECT
        report.uid
    FROM
        report
    WHERE
        report.status=0
)

引用:

• 13.2.8.2 JOIN Syntax
• 13.2.9.6 Subqueries with EXISTS or NOT EXISTS
• expr IN (value,...)