MySQL多次联接同一个表使计数变得奇怪

发布于11月23日

经过编辑以澄清和简化

我有以下疑问

select 
  Signups.user_id,
  Count(timeoutTable.user_id) as timeoutCount,
  Count(confirmedTable.user_id) as confirmedCount
from Signups
left join (
  select * from Confirmations where action = 'timeout'
) as timeoutTable on signups.user_id = timeoutTable.user_id
left join (
  select * from Confirmations where action = 'confirmed'
) as confirmedTable on signups.user_id = confirmedTable.user_id
group by 
  Signups.user_id,
  timeoutTable.user_id,
  confirmedTable.user_id

在使用以下输入运行时

Signups Table

| user_id | time_stamp          |
| ------- | ------------------- |
| 15      | 2020-07-31 18:26:35 |
| 16      | 2021-05-20 01:38:09 |
| 7       | 2020-08-02 08:45:14 |
| 10      | 2020-06-24 17:13:14 |
| 5       | 2020-06-27 17:59:29 |
| 9       | 2021-11-08 03:05:14 |
| 8       | 2021-12-13 03:38:58 |
| 12      | 2020-09-16 11:17:39 |


Confirmations Table

| user_id | time_stamp          | action    |
| ------- | ------------------- | --------- |
| 7       | 2020-03-31 13:11:43 | timeout   |
| 7       | 2021-03-25 07:40:25 | timeout   |
| 8       | 2020-07-27 19:43:25 | confirmed |
| 8       | 2021-03-07 19:48:06 | timeout   |
| 7       | 2020-01-24 15:43:47 | confirmed |

它的输出:

| user_id | timeoutCount | confirmedCount |
| ------- | ------------ | -------------- |
| 15      | 0            | 0              |
| 16      | 0            | 0              |
| 7       | 2            | 2              |
| 10      | 0            | 0              |
| 5       | 0            | 0              |
| 9       | 0            | 0              |
| 8       | 1            | 1              |
| 12      | 0            | 0              |

我正在try 使USER_ID7的确认值为1，超时值为2，但由于某种原因，它将这两个值都设置为2.任何帮助都将不胜感激.

select user_id, round(case when total_messages = 0 then 0 else total_confirmed / total_messages end, 2) as confirmation_rate from ( select s.user_id, t.total_timeouts, c.total_confirmed, coalesce(t.timeoutCount,0) + coalesce(c.total_confirmed, 0) as total_messages from signups s left join ( select user_id, count(*) as total_timeouts from confirmations where action = 'timeout' group by user_id ) as t on t.user_id = s.user_id left join ( select user_id, count(*) as total_confirmed from confirmations where action = 'confirmed' group by user_id ) as c on c.user_id = s.user_id ) order by user_id;

select user_id, round(case when total_messages = 0 then 0 else total_confirmed / total_messages end, 2) as confirmation_rate from ( select s.user_id, sum(c.action = 'timeout') as total_timeouts, sum(c.action = 'confirmed') as total_confirmed, sum(c.action = 'timeout') + sum(c.action = 'confirmed') as total_messages from signups s left join confirmations c on c.user_id = s.user_id group by s.user_id ) order by user_id;

MySQL多次联接同一个表使计数变得奇怪

推荐答案

Mysql相关问答推荐

MySQL在带有特定属性值上的对象的数组的杨森对象中搜索

用于搜索从各种表中获得的结果的查询

了解MySQL_stmt_BIND_NAMED_Param()-MySQL C API

如何在WooCommerce中更新pm_Virtual.meta_Value=#39；否

MySQL grant语句中的用户名区分大小写

MySQL 搜索列字段字符串

MySQL，递归 CTE.它如何以这种句法形式工作？

错误1075：表定义不正确；只能有一个自动列，它必须定义为一个键(使用 gorm 和 mysql)

如何在 MySQL 中查找重复值和更新值

MYSQL如何将虚拟列添加到现有表中

如何通过未知列中的唯一值有效地更新 MySQL 行

计算每个用户在第二个表中有多少条记录

MySQL Workbench - 如何同步 EER 图

如何在sequelize中定义多列的唯一索引

如何在特定数据库中创建表？

基于字符串动态创建 PHP 对象

MySQL术语约束与外键的区别？

从 MySQL JSON 数据类型中提取不带引号的值

你能推荐一个免费的 Linux 轻量级 MySQL GUI 吗？

MySQL中的多列外键？