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

我试图从我们的订单表中检索客户和发票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;

最佳答案

我认为您在别名方面遇到了问题。尝试添加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;

关于mysql - 带比较运算符的MySQL WHERE子查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11799599/