MySQL在连接上丢失行

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL在连接上丢失行，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3232字，纯文字阅读大概需要5分钟。

内容图文

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

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`

但是,如果我进行了轻微(但不可接受)的修改,将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;

感谢您的任何帮助,您可以提供.

(编辑：现在按照蒂姆的建议使用CROSS JOIN.)

解决方法:

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

内容总结

以上是互联网集市为您收集整理的MySQL在连接上丢失行全部内容，希望文章能够帮你解决MySQL在连接上丢失行所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端