例如我有两个表
Person Table
ID : Name
1 : Bob
2 : Jane
3 : Mike
4 : John
5: Mary
然后我也有(当前实际上是一个整数,但我使用文本使其更具可读性(:
Job Table
ID : Name : PersonID : JobStatus
1 : Plumber : 1 : current
2 : Doctor : 2 : past
3 : Driver : 2 : current
4 : Cook : 3 : current
5: Programmer : 5 : past
我想联接这两个表,以便我可以得到每个人的列表,但我只想包括左联接的当前作业。换句话说,我希望结果是:
Bob : Plumber : current
Jane : Driver : current
Mike : Cook : current
John : :
Mary : :
我怎样才能通过联接来做到这一点。我尝试做:
SELECT
person.name, job.name, job.status
FROM
person
LEFT JOIN
job ON person.id=job.person.id
但问题是,在这种情况下,我会看到简的两个条目,一个是她过去的医生工作以及她的新工作司机。如果我添加一个 where 子句,例如:
WHERE job.status='current'
然后它从列表中删除约翰和玛丽。
如何编写一个联接,以显示所有人,但仅包含当前作业?
至少在 Oracle 中你应该能够做到这一点:
SELECT
person.name, job.name, job.status
FROM
person
LEFT JOIN
job ON person.id=job.personId AND job.status = 'current';
因此,当person.id=job.person.id AND job.status = 'current'
进行连接时,当该条件为假时,您只会获得person
数据。
我不知道MySQL是否支持ON
子句中的复合条件,但我想是的,它并不完全是突破性的技术。
更新:
看起来它受支持,请阅读 https://dev.mysql.com/doc/refman/8.0/en/join.html
Select p.name, j.name,j.status
From person p
Left Join
(Select name,status,personId
From job
Where status ='current'
) j On p.id = j.personId;
你能试试这个查询吗?
试试这个
SELECT
person.name, job.name, job.status
FROM
person
LEFT JOIN
job ON person.id=job.person.id
WHERE CASE WHEN job.status IS NULL THEN '@' WHEN job.status ='current' THEN '@' else job.status END = '@'
有几种方法可以做到这一点;最佳选项取决于您的数据库以及不同方法的性能。
方法 1:使用 OR 左联接
SELECT p.name, j.name, j.status
FROM person AS p
LEFT JOIN job AS j ON j.personId = p.id AND j.status = 'current';
-- Returns all persons, and only jobs w/ value 'current'
-- If the filter on j.status were in the WHERE clause, it would remove non-matching
方法 2:使用 UNION ALL 进行两个查询(这将删除没有当前或空值的行(
SELECT p.name, j.name, j.status
FROM person AS p
INNER JOIN job AS j ON j.personId = p.id
WHERE j.status = 'current'
OR j.status IS NULL
UNION ALL
SELECT p.name, j.name, j.status
FROM person AS p
LEFT JOIN job AS j ON j.personId = p.id
WHERE j.status IS NULL;
方法 2b:使用 UNION ALL 进行两个查询(这将保留没有当前或空值的行(
SELECT p.name, j.name, j.status
FROM person AS p
INNER JOIN job AS j ON j.personId = p.id
WHERE j.status = 'current'
OR j.status IS NULL
UNION ALL
SELECT p.name, j.name, j.status
FROM person AS p
LEFT JOIN job AS j ON j.personId = p.id
WHERE NOT EXISTS
(SELECT 1 FROM jobs AS t WHERE t.personId=p.Id AND t.status='current')
GROUP BY p.Id;
-- GROUP BY ensures we only get one row back per person, but will be random if there are multiples
方法 3:子选择
SELECT p.name, j.name, j.status
FROM person AS p
LEFT JOIN (
SELECT x.name
FROM job AS x
WHERE x.status = 'current'
) AS j;