这可能会起作用，因为它左键联接两个表以确定tblreadby中是否有任何记录，然后在子查询中使用结果。不幸的是我不能测试它。

SELECT 
t.`ticket_id`, 
t.`subject`, 
t.`user_id`, 
t.`created_date`, 
t.`last_updated`
( 
    SELECT 
        CASE WHEN r.`ticket_id` IS NULL THEN 1 
             ELSE COUNT(*) 
        END AS message_count
    FROM `tblReadBy` 
    WHERE `ticket_id` = t.`ticket_id` AND 
          `user_id` = 'XXX' AND 
          `last_read_time` < t.`last_updated` 
) AS `unread_msg_count`
FROM `tblTickets` AS t
LEFT JOIN `tblReadBy` AS r ON t.`ticket_id` = r.`ticket_id` AND
           r.`user_id` = 'XXX'
ORDER BY t.`last_updated` DESC

您可能需要添加r。 ticket_id 在外部查询的选择列表中，如下所示：

SELECT 
t.`ticket_id`, 
r.`ticket_id`,
t.`subject`, 
t.`user_id`, 
t.`created_date`, 
t.`last_updated`
( 
    SELECT 
        CASE WHEN r.`ticket_id` IS NULL THEN 1 
             ELSE COUNT(*) 
        END AS message_count
    FROM `tblReadBy` 
    WHERE `ticket_id` = t.`ticket_id` AND 
          `user_id` = 'XXX' AND 
          `last_read_time` < t.`last_updated` 
) AS `unread_msg_count`
FROM `tblTickets` AS t
LEFT JOIN `tblReadBy` AS r ON t.`ticket_id` = r.`ticket_id` AND
           r.`user_id` = 'XXX'
ORDER BY t.`last_updated` DESC

在我看来，您需要计算tblreadby中已更新的消息和从未读取过的新消息，即它们存在于tbltickets中，但不存在于tblreadby中。
这是我的解决方案，有点复杂，我很想看到有人改进它，但基本上我加入agains两个'选择。。。计数（*）´, 一个用于全新消息，另一个用于更新消息

SELECT t.ticket_id, subject, t.user_id, created_date, last_updated, IFNULL(c1.count, 0) + IFNULL(c2.count, 0)
FROM tblTickets t
LEFT JOIN (SELECT ticket_id, user_id, COUNT(*) count
           FROM tblTickets t
           WHERE NOT EXISTS (SELECT * 
                             FROM tblReadBy r   
                             WHERE t.ticket_id = r.ticket_id 
                             AND t.user_id = r.user_id)
           GROUP BY ticket_id, user_id) AS c1 ON c1.ticket_id = t.ticket_id AND c1.user_id = t.user_id
LEFT JOIN (SELECT r.ticket_id, r.user_id, COUNT(*) count
           FROM tblReadBy r
           JOIN tblTickets t ON t.ticket_id = r.ticket_id AND t.user_id = r.user_id
           WHERE r.last_read_time < t.last_updated
           GROUP BY r.ticket_id, r.user_id) AS c2 ON c2.ticket_id = t.ticket_id AND c2.user_id = t.user_id
WHERE t.user_id = 'aaa'

我们可以用 SUM() 函数的 NULL 处理行为。它会回来的 NULL 如果不存在行。
如果返回集没有行，sum（）将返回null
mysql自动将布尔比较运算符的结果类型化为0/1。利用这种行为，我们可以 Sum() 以及 Coalesce() .

SELECT 
    t.`ticket_id`, 
    t.`subject`, 
    t.`user_id`, 
    t.`created_date`, 
    t.`last_updated`
    ( 
      -- coalesce handles case when no rows
      SELECT COALESCE(SUM(`ticket_id` > 0), 1)
      FROM `tblReadBy` 
      WHERE `ticket_id` = t.`ticket_id` AND 
            `user_id` = 'XXX' AND 
            `last_read_time` < t.`last_updated` 
    ) AS `has_unread_msg`
FROM `tblTickets` AS t
WHERE t.`creator_id` = 'XXX'
ORDER BY t.`last_updated` DESC