计数(*)的子查询

Question

如何从子查询count(*)数据中获取关联left(number,7)失败的数据？

例如，我这样做了：

SELECT * FROM table1 WHERE outcome = 'Fail' AND left(number,7) = 
   (SELECT count(*) as total, left(number,7) as prefix  
   FROM table1 where outcome like '%Passed%' 
   group by prefix order by total desc limit 250)

这不起作用，因为子查询中有两个字段。那么如何绕过这一点呢？

Answer 1

您可以使用JOIN而不是子查询：

SELECT t1.*, t2.total, ... 
FROM table1 AS t1
INNER JOIN
(
    SELECT count(*) as total, left(number,7) as prefix  
    FROM table1 
    where outcome like '%Passed%' AND outcome = 'Fail'
    group by prefix 
    order by total desc limit 250
) AS t2 ON t2.prefix = left(t1.number,7)

Answer 2

尝试此查询

 SELECT * 
 FROM 
   table1 a 
 INNER JOIN 
   (SELECT 
      count(*) as total, 
      left(number,7) as prefix  
   FROM 
      table1 
   where 
      outcome like '%Passed%' 
   group by 
      prefix 
   order by 
      total desc limit 250)b
 ON 
   a.outcome = 'Fail' AND 
   left(number,7) = b.prefix