如果你需要两种模式，那么我建议使用 inner join :

SELECT a.aid, b1.area, b2.area  
FROM tablea a1 JOIN
     tableb b1
     ON a1.aid = b1.aid AND b1.mode = 1 JOIN
     tableb b2
     ON a1.aid = b2.aid AND b2.mode = 2;

内部联接将确保这两个值都存在于 b .
因为这两种模式都需要存在，实际上你不需要 tablea :

SELECT b1.aid, b1.area, b2.area  
FROM tableb b1
     ON a1.aid = b1.aid JOIN
     tableb b2
     ON a1.aid = b2.aid AND b1.mode = 1  AND b2.mode = 2;

实际上，如果你只是需要 aid 如果两者都存在，还可以执行以下操作：

select b.aid, max(case when mode = 1 then area end) as area1,
       max(case when mode = 2 then area end) as area2
from tableb b
group by b.aid
having area1 is not null and area2 is not null;

这根本不需要加入表。

http://sqlfiddle.com/#!9/2db6c5/1号
试试这个；

SELECT b1 .aid, b1.area, b2.area  
FROM tablea a1
LEFT JOIN tableb b1 ON a1.aid = b1 .aid
LEFT JOIN tableb b2 ON a1.aid = b2 .aid
where 
b1.mode = 1
and b2.mode = 2

这是一个称为pivot的过程。
并且可以使用group by和 MAX(CASE END) 条款。
having子句确保b.aid同时具有包含1和2模式的记录。

SELECT 
   B.aid 
 , MAX(CASE WHEN B.mode = 1 WHEN B.area ELSE 0 END) AS 'area(mode=1)'
 , MAX(CASE WHEN B.mode = 2 WHEN B.area ELSE 0 END) AS 'area(mode=2)'
FROM 
 B
GROUP BY
 B.aid 
HAVING
   SUM(B.mode = 1) = 1
 AND
   SUM(B.mode = 2) = 1
ORDER BY 
 B.aid ASC

这应该起作用：

select * from(
    select aid, max(case when mode = 1 then area end) as mode1, 
                max(case when mode = 2 then area end) as mode2
    from tableB  
    group by aid order by aid
)where mode1 is not null and mode2 is not null

我不知道为什么表a的目的是什么。但我认为这将是起点。

SELECT DISTINCT a.aid
    ,b1.area as area_mode1
    ,b2.area as area_mode2

FROM TableA a
    JOIN TableB b1 ON (a.aid = b1.aid AND b1.mode = '1')
    JOIN TableB b2 ON (a.aid = b2.aid AND b2.mode = '2')
;

也许一个子选择也可以做到这一点，但是我认为表b的双连接对于较大的表来说会花费更少的内存。
提示：应该避免使用“name”和“mode”等字段名。它们是sql中的保留字，可能会导致混乱的结果。