mysql - 基于表的条件连接

Question

我正在尝试编写一个 MySQL 查询，该查询将一行中的三个表左连接到主表，但有条件地根据最后一个连接表的值执行此操作。我尝试做的两种方式都有问题:

• 没有显示我需要的所有主要行
• 或者我在那里得到一些额外的不相关的行。

表结构是现有系统的一部分，我无法更改它。此外，一旦我弄清楚了 SQL，我将不得不找到一种将其与其查询系统集成的方法，因此 SQL 越简单越好!

我要在这里放很多细节，抱歉这篇文章太长了，但我认为最好包含所有信息。

这里有更多信息:

数据模型:

有用户、类(class)和中间内容类型，它存储特定类(class)中每个用户的用户信息，称为 cui。我需要查看所有用户的列表，如果有该用户和类(class)的 cui，也将其包括在内。

在数据库中，cui的字段内容存储在单独的表中，并通过cui_id连接，这是麻烦的一部分。

简化的数据库结构:

• 主表:user(字段:uid，name)
• cui表:节点(字段:cui_id)
• 用户字段表(字段:cui_id、uid)
• 类(class)字段表(字段:cui_id、course_id)

加入

user     user_field         node         course_field
----     ----------         -------      ------------
uid ==== uid          
name     cui_id =========== cui_id ===== cui_id
                                         course_id    = 202

尝试查询:

以下查询在具有以下用户的小样本数据集上运行:

• 用户二:对所查询的类(class)有一个 cui，并且对其他类(class)。
• 用户一:有其他类(class)的成绩，但没有一个被质疑。
• 用户六:没有 cuis。

第一个查询是对最后一个表的条件连接，问题是它为用户二包含了一个额外的行，这不是正确的 cui。我不想 GROUP BY，因为我认为它不一定会给我正确的行。

SELECT user.uid, user.name, field_user.cui_id,
field_course.course_id
FROM user
LEFT JOIN field_user
ON
user.uid = field_user.uid
LEFT JOIN node AS cui
ON
cui.id = field_user.cui_id
LEFT JOIN field_course
ON
field_course.cui_id = cui.id
AND
field_course.course_id = 202

结果:

uid     name        cui_id  course_id
---------------------------------------------
4       User One    772     NULL
5       User Two    434     202
5       User Two    771     NULL
35      User Six    NULL    NULL

替代方案是这个:

SELECT user.uid, user.name, field_user.cui_id,
field_course.course_id
FROM user
LEFT JOIN field_user
ON
user.uid = field_user.uid
LEFT JOIN node AS cui
ON
cui.id = field_user.cui_id
LEFT JOIN field_course
ON
field_course.cui_id = cui.id
WHERE (
field_course.course_id = 202 OR ISNULL(field_course.course_id))

结果:

uid     name    cui_nid     course_nid
---------------------------------------------
5       User Two    434     202
35      User Six    NULL    NULL

问题在于它遗漏了用户一，因为用户一确实有其他 cuis，只是不适合正确的类(class)。

所以问题是，我如何获得它？

uid     name        cui_id  course_id
--------------------------------------------
4       User One    772     NULL
5       User Two    434     202
35      User Six    NULL    NULL

Answer 1

确实不是那么漂亮，但应该可以解决问题:

-- Select to get all users irrespective of whether they have a row in field_course or field_user
SELECT user.uid, user.name, field_user.cui_id,
null as course_id
FROM user
LEFT JOIN field_user ON user.uid = field_user.uid
LEFT JOIN node AS cui ON cui.id = field_user.cui_id 
where user.uid not in
(
-- Inline view to get only users that have both a field_course & field_user row
SELECT user.uid
FROM user
INNER JOIN field_user ON user.uid = field_user.uid
INNER JOIN node AS cui ON cui.id = field_user.cui_id
INNER JOIN field_course ON field_course.cui_id = cui.id
where field_course.course_id = 202
)
union all
-- Select to get only users that have both a field_course & field_user row
SELECT user.uid, user.name, field_user.cui_id,
field_course.course_id
FROM user
INNER JOIN field_user ON user.uid = field_user.uid
INNER JOIN node AS cui ON cui.id = field_user.cui_id
INNER JOIN field_course ON field_course.cui_id = cui.id
where field_course.course_id = 202;

编辑

下面是我用来测试的表结构和数据:

CREATE TABLE `user` (
  `uid` int(10) unsigned NOT NULL,
  `name` varchar(20) NOT NULL
);

CREATE TABLE `node` (
`id` int(10) unsigned not null primary key
);

CREATE TABLE `field_user` (
  `uid` int(10) unsigned NOT NULL,
  `cui_id` int(10) unsigned NOT NULL
);

CREATE TABLE `field_course` (
  `uid` int(10) unsigned NOT NULL,
  `cui_id` int(10) unsigned NOT NULL,
`course_id` int(10) unsigned NOT NULL
);

insert into user (uid,name) values (4,"User One");
insert into user (uid,name) values (5,"User Two");
insert into user (uid,name) values (35,"User Six");

insert into node (id) values (771);
insert into node (id) values (772);
insert into node (id) values (434);

insert into field_user (uid,cui_id) values (4,772);
insert into field_user (uid,cui_id) values (5,434);
insert into field_user (uid,cui_id) values (5,771);

insert into field_course (uid,cui_id,course_id) values (5,434,202);