mysql - 选择查询对项目进行分组但也进行限制

Question

我正在运行这个sql查询:

SELECT * FROM customer_billing a WHERE 

(SELECT COUNT(*) FROM customer_billing b WHERE 
 a.producttype = 'VoIP Telephony' and 
 a.productname = 'VoIP Geographic Number (01/02)' and 
 a.customer_seq = b.customer_seq) 
 <= 1 order by customer_seq ASC

它应该只显示 producttype = 'VoIP Telephony' 的行，但它显示其他两个

其中producttype =“电话线路”等

Answer 1

我认为你正在尝试这样做:

SELECT *
FROM customer_billing cb
WHERE cb.producttype = 'VoIP Telephony' and 
      cb.productname = 'VoIP Geographic Number (01/02)' and
      (SELECT COUNT(*)
       FROM customer_billing cb2 
       WHERE cb2.producttype = cb.producttype and 
             cb2.productname = cb.productname and
             cb2.customer_seq = cb.customer_seq
     ) <= 1
order by customer_seq ASC;

您的查询版本正在子查询内进行比较。因此它们仅适用于 count(*) 而不是整个查询的过滤。