mysql - 带有 count(*) 的子查询

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)