MySQL:子选择不返回预期计数结果

Question

我有下面的查询，它运行并处理ok，问题是子select只返回1或0，当它应该返回所有记录的COUNT时，这些记录在过去24小时内都是在WHERE子句中指定的，而且都属于子选择标准。

我怀疑问题在于每次只在ID上比较子select，但是我尝试将WHERE tbr.ID = ID更改为WHERE tbr.ID IN (ID)，但它仍然只返回1或0。

别名总数上的COUNT返回728，因此数据确实存在，并且在过去24小时内有效，这一点已经得到验证。

SELECT COUNT(tbr.`ID`) AS `total`
, (SELECT COUNT(`ID`) FROM `cred` WHERE tbr.`ID` = `ID` AND `match` = 'EXACT' AND `number` <> '') AS `exact`
, DATE(NOW()) AS `reportDate`
FROM `cred` tbr
WHERE tbr.`processed` >= (NOW() - INTERVAL 1 DAY);

Answer 1

也许有条件的汇总会有所帮助。

SELECT 
   COUNT(tbr.`ID`) AS `total`
 , COUNT(case when match` = 'EXACT' AND `number` <> '' then tbr.`ID` end) AS `exact`
 , DATE (NOW()) AS `reportDate`
FROM `cred` tbr
WHERE  tbr.`processed` >= (NOW() - INTERVAL 1 DAY);

样本数据和预期结果有助于选择解决方案。

Answer 2

SELECT COUNT(tbr.`ID`) AS `total`
, (SELECT COUNT(`ID`) 
    FROM `cred` 
    WHERE `ID` IN (tbr.`ID`)    --try with inverting this condition
        AND `match` = 'EXACT' 
        AND `number` <> '') AS `exact`
, DATE(NOW()) AS `reportDate`
FROM 
    `cred` tbr
WHERE 
    tbr.`processed` >= (NOW() - INTERVAL 1 DAY);