选择满足2个条件的计数？

Question

Select count(*) from Merchant where Email in 
(Select SentEmail from MerchantInvitations where MerchantID = '16092') AND 
CreatedOn>  (Select TimeSent from MerchantInvitations where MerchantID = '16092')

我想要满足其电子邮件位于 MerchantInvitations 的 SentEmail 列中的商家的计数 和 CreatedOn >商户邀请中发送的时间。但我收到一个错误“子查询返回超过 1 个值。当子查询时这是允许的 遵循 =、!=、<、<= 等，或者当子查询用作表达式时“请帮帮我！提前致谢！

Answer 1

MerchantInvitations 中 MerchantID = '16092' 的多个条目会导致此问题。也许您想要最新的 TimeSent，如下所示：

Select count(*) from Merchant where Email in 
(Select SentEmail from MerchantInvitations where MerchantID = '16092') AND 
CreatedOn>  (Select MAX(TimeSent) from MerchantInvitations where MerchantID = '16092')

Answer 2

如果您的第二个子查询返回多于一行，它将永远不会工作。不过，您可以使用 ANY 或 ALL 关键字：

SELECT COUNT(*)
FROM Merchant
WHERE Email IN (SELECT SentEmail
               FROM MerchantInvitations
               WHERE MerchantID = '16092') -- Why not 16092? Is it really a string?
AND CreatedOn > ANY (SELECT TimeSent
                     FROM MerchantInvitations
                     WHERE MerchantID = '16092');

但看起来您只想做一个简单的连接：

SELECT COUNT(*)
FROM Merchant M
INNER JOIN MerchantInvitations I
    ON M.MerchantID = I.MerchantID -- You may have to change this
    AND M.Email = I.SentEmail
    AND M.CreatedOn > I.TimeSent
WHERE M.MerchantID = 16092;

这绝对应该比子查询更快。

Answer 3

看起来您的子查询返回了多个 TimeSent ，与 CreatedOn 进行了比较。要解决此问题，请添加 ALL 说明符：

Select count(*) from Merchant where 
Email in (Select SentEmail from MerchantInvitations where MerchantID = '16092') AND 
CreatedOn > ALL (Select TimeSent from MerchantInvitations where MerchantID = '16092')

Answer 4

问题可能是

 (Select TimeSent from MerchantInvitations where MerchantID = '16092')

如果该子查询返回多个值，那么数据库引擎如何知道要与 CreatedOn 进行比较？

您可以通过在子查询中使用 MIN(TimeSent) 或 MAX(TimeSent)（代替 TimeSent）来解决该问题（以合适的为准）。

Answer 5

使用 where 存在而不是 in

Select count(*) from Merchant where exists
(Select SentEmail from MerchantInvitations where MerchantID = '16092' and MerchantInvitations.sentemail = merchant.email) AND 
CreatedOn>  (Select TimeSent from MerchantInvitations where MerchantID = '16092' and MerchantInvitations.sentemail = merchant.email)

Answer 6

使用单个 EXISTS 子查询：

Select count(*) 
from Merchant M
where exists
(select null
 from MerchantInvitations I
 where M.Email = I.SentEmail AND 
       M.CreatedOn > I.TimeSent AND
       I.MerchantID = '16092')