mysql - 带 Join 的关联子查询

Question

我有 3 个表:alert、user_to_alert 和 user_device。设备表保存 token 列表并包含 user_id 字段。

user_to_alert 表保存用户读取警报的时间记录。如果相应的alert_id/user_id组合不存在记录，则意味着用户尚未阅读警报。还有一个可以为零的alert_read列(如果用户在阅读后将警报标记为未读)。

我有一个加载设备 token 列表的查询，我需要扩展它以包含未读警报的数量。这是我提出的查询

SELECT d.*, (
SELECT COUNT(*) FROM `alert` AS a 
LEFT JOIN `user_to_alert` AS uta ON 
( uta.`alert_id` = a.`alert_id` AND uta.`user_id` = d.`user_id` ) 
WHERE uta.`alert_read` = 0 OR uta.`alert_read` IS NULL) AS user_badge_number 
FROM `user_device` AS d  
WHERE d.`device_type` = 'iOS' AND d.`device_active` = 1 
GROUP BY d.`device_token`

此查询给我错误 #1054 - Unknown column 'd.user_id' in 'on clause'

我已经使用架构和查询设置了 SQLFiddle:http://sqlfiddle.com/#!9/ee653/2

如果我单独运行子查询并将 d.user_id 替换为有效的 user_id，我会得到预期的结果(即 user_id 10 为 3)。我认为这意味着问题出在我引用列 d.user_id 的地方。我猜 MySQL 正在尝试首先执行子查询，所以 d.user_id 还不是有效的列？但这只是一个猜测。

我尝试移动 uta.user_id = d.user_id我的 ON 子句到 WHERE 子句的一部分，但这只是为 user_badge_number 列的所有行提供了 0。

我无法通过 Google 找到与此问题相关的任何内容。我发现的有关相关子查询的所有内容似乎都是在 ON 或 WHERE 子句中使用它们，而不是使用它们来检索额外的数据。

这是通过 PHP 脚本运行的，因此我知道我可以在 PHP 脚本的结果循环中自行运行子查询。显然，我宁愿弄清楚如何通过一个查询来完成它，因为这样会更有效。

Answer 1

正如您所指出的，计算已读取的警报数量并不难。您可以从警报总数中减去该数字以获得未读数量:

SELECT d.*, 
    ( SELECT COUNT(*) FROM `alert` ) - 
    ( SELECT COUNT(*) FROM `user_to_alert` 
        WHERE `user_id` = d.`user_id` AND `alert_read` = 1 )
FROM `user_device` AS d 
WHERE d.`device_active` AND d.`device_type` = 'iOS';

但请注意，设计存在一个问题，即如果 6 个月后添加设备或用户，则过去的所有警报将在该新设备上显示为未读。如果设备被重新分配给新用户，或者现有用户切换设备(取决于您是计算每个用户还是每个设备的未读警报)，也会出现类似的问题。

为了解决这个问题，您可以将查询限制在添加用户或设备后的特定日期范围内，尽管在重新分配设备的情况下，不清楚要使用哪个日期。)我认为一个更简单的答案是创建每个警报时将行插入到 user_to_alert 中。这有几个优点:

• 轻松计算用户或设备的未读行数
• 新设备/用户不会继承旧提醒
• 可让您添加新功能:仅针对部分用户/设备的提醒