带比较运算符的 MySQL WHERE 子查询

Question

我正在尝试从我们的订单表中检索客户和发票 ID 的列表，其中客户只有一个订单。下面的SQL返回零条记录，应该有几十条。如果我将有效的用户 ID 替换到子查询中，子查询似乎可以正常工作。我一定做错了什么，有什么帮助吗？

SELECT tblclients.id AS clientid, tblinvoices.id AS invoiceid
            FROM tblorders
            join tblinvoices ON tblorders.invoiceid = tblinvoices.id
            join tblclients ON tblorders.userid = tblclients.id
            WHERE (SELECT COUNT(*) FROM tblorders WHERE userid = tblorders.userid) = 1;

Answer 1

我认为您的别名有问题。尝试添加 T1 别名:

SELECT tblclients.id AS clientid, tblinvoices.id AS invoiceid
            FROM tblorders T1
            join tblinvoices ON T1.invoiceid = tblinvoices.id
            join tblclients ON T1.userid = tblclients.id
            WHERE (SELECT COUNT(*) FROM tblorders WHERE userid = T1.userid) = 1;