MySQL 在连接时丢失行

Question

我有两个表，我正在尝试以特定方式将它们连接在一起。我正在寻找的结果是:

site statusname total
2   Follow-Up   0
2   Off Study   0
2   Screening   1
2   Treatment   0
1   Follow-Up   0
1   Off Study   0
1   Screening   2
1   Treatment   0

但是，这是返回的内容:

site statusname total
1   Follow-Up   0
1   Off Study   0
1   Screening   2
2   Screening   1
1   Treatment   0

我的实际查询(返回错误结果的查询)如下所示:

SELECT
    sitestatus.site                                 AS site,
    sitestatus.statusname                           AS statusname,
    count(participant.id)                           AS total
FROM
    (SELECT DISTINCT
        participant.`site`                          AS site,
        participant_status.`name`                   AS statusname,
        participant_status.`id`                     AS status
    FROM
        participant_status
    CROSS JOIN
        participant) AS sitestatus
LEFT JOIN
    participant
ON
    participant.`site` = sitestatus.`site`          AND
    participant.`status` = sitestatus.`status`
GROUP BY
    sitestatus.`statusname`,
    participant.`site`

但是，如果我进行轻微(但 Not Acceptable )的修改，向子选择添加一个 WHERE 子句并使用 UNION，我会得到我想要的结果。这是查询:

SELECT
    sitestatus.site                                 AS site,
    sitestatus.statusname                           AS statusname,
    count(participant.id)                           AS total
FROM
    (SELECT DISTINCT
        participant.`site`                          AS site,
        participant_status.`name`                   AS statusname,
        participant_status.`id`                     AS status
    FROM
        participant_status
    CROSS JOIN
        participant
    WHERE site=1) AS sitestatus
LEFT JOIN
    participant
ON
    participant.`site` = sitestatus.`site`          AND
    participant.`status` = sitestatus.`status`
GROUP BY
    sitestatus.`statusname`,
    participant.`site`

UNION

SELECT
    sitestatus.site                                 AS site,
    sitestatus.statusname                           AS statusname,
    count(participant.id)                           AS total
FROM
    (SELECT DISTINCT
        participant.`site`                          AS site,
        participant_status.`name`                   AS statusname,
        participant_status.`id`                     AS status
    FROM
        participant_status
    CROSS JOIN
        participant
    WHERE site=2) AS sitestatus
LEFT JOIN
    participant
ON
    participant.`site` = sitestatus.`site`          AND
    participant.`status` = sitestatus.`status`
GROUP BY
    sitestatus.`statusname`,
    participant.`site`;

我不知道丢失的行要去哪里。

以下是相关的模式:

CREATE TABLE `participant` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `site` int(10) unsigned NOT NULL,
  `status` int(10) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
)

和

CREATE TABLE `participant_status` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

感谢您提供的任何帮助。

(编辑:现在按照 Tim 的建议使用 CROSS JOIN。)

Answer 1

UNION 运算符具有删除重复记录的默认行为，这些记录出现在正在聚合的两个结果集中。如果您想保留两个查询的所有记录，您应该使用 UNION ALL 运算符:

query1
UNION ALL
query2

以下是我对这个查询的正确方法的尝试:

SELECT t2.site, t2.name AS statusname, t1.total
FROM
(
    SELECT site, status, COUNT(*) AS total
    FROM participant
    GROUP BY site, status
) t1
INNER JOIN
(
    (SELECT DISTINCT site FROM participant)
    CROSS JOIN
    participant_status
) t2
ON t1.site = t2.site AND t1.status = t2.id