logo标签列表

MySQL在连接时丢失了行。

mysqlsqljoin
4
我有两个表格，我想以特定的方式将它们连接在一起。我希望得到的结果是：
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;

感谢您能提供任何帮助。

（编辑：现在按照Tim的建议使用 CROSS JOIN。）

在两个查询之间使用 UNION ALL 运算符。 - Vipin Jain
你可以看看这个问题，可能会对你有帮助。虽然是关于Oracle的，但是涉及到相同的逻辑。链接 - sameh.q
你可能得到了空行，对吗？ - pratz9999
2个回答
4
< p > 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
我不希望我的查询中有任何 UNION 运算符。我使用 UNION 的示例是为了展示原始查询应该返回更多的数据。 - Alan C.
嗨@tim...我不确定我理解了。在那个没有正常工作的查询中，我没有WHERE子句。在额外长的查询中，只要有两个站点，它就可以工作（但我不想使用那个查询），我确实有一个WHERE子句，但是长查询（带有WHERE和UNION）的目的是显示我认为内部查询返回了我想要的所有内容，但是外部查询或LEFT JOIN正在丢弃数据。 - Alan C.
1
抱歉，我的意思是不要通过FROM table1, table2这种方式进行连接，因为不清楚连接列是什么。为了得到你真正想要的答案，需要有人（或某个程序）重写查询语句。你当前的结构使得这更加困难。 - Tim Biegeleisen
啊，是的，我明白你在说什么。感谢你指出这一点。所以我可能走了错误的路线，但实际上我不想要一个连接列。我想生成站点=（1,2）和状态=（1,2,3,4）的交叉积...以获得8行，如（1,1），（1,2），（1,3），（1,4），（2,1），（2,2），（2,3），（2,4）。 （顺便说一下，我知道你不必帮助我，但我真的很感激） - Alan C.
1
也许我可以帮忙。这听起来像是你想要执行一个CROSS JOIN以获取两个集合的笛卡尔积。 - Tim Biegeleisen
显示剩余5条评论
0
在@Tim的帮助下，我得以得出答案：
SELECT t2.site, t2.statusname AS statusname, COALESCE(t1.total,0) AS total
FROM
(
    SELECT site, status, COUNT(*) AS total
    FROM participant
    GROUP BY site, status
) AS t1
RIGHT JOIN
(
    SELECT DISTINCT participant_status.id AS status, participant_status.name AS statusname, participant.site FROM participant
    CROSS JOIN
    participant_status
    ORDER BY status, site
) AS t2
ON  t1.site = t2.site AND t1.status = t2.status
1
通常情况下，最好让FROM引用始终有一行，并且后续引用可能有一行（换句话说，交换两个子查询，并将其设置为LEFT JOIN）。通常还应该将ORDER BY放在最外层 - 我不确定连接后排序的保证程度如何。 - Clockwork-Muse
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接