mysql - mysql中的count()函数

Question

我的 count 函数对第一个表的记录进行两次计数，然后添加到相同的结果，而不是添加两个表记录的 count() 。我该如何解决这个问题？

SELECT 
uln.user_id, count(uln.user_id)+count(uln.user_id) as user_pet_notif_count
FROM
user_legislation_notifications uln
join user_petition_notifications upn
on upn.user_id = uln.user_id
where
uln.user_id = '7' and uln.isRead = '0'
and upn.user_id = '7' and upn.isRead = '0'

结果是 user_id --> 7 user_pet_notif_id --> 28 而不是 user_id --> 7 user_pet_notif_id --> 9

Answer 1

您需要在不连接的情况下进行计数，否则您将计算连接结果中的行数。

SELECT (SELECT COUNT(*)
        FROM user_legislation_notifications
        WHERE user_id = '7' and isRead = '0')
    +  (SELECT COUNT(*)
        FROM user_petition_notifications
        WHERE user_id = '7' and isRead = '0') AS user_pet_notif_count