一种思路供参考,不过实际应用可能比较麻烦
select b.a2,b.a3,c.a3
from
(select a2,a3
from
(SELECT a1,a2,count(*) as a3 from aa group by a1,a2) a
where a.a1='张三') b ,
(select a2,a3
from
(SELECT a1,a2,count(*) as a3 from aa group by a1,a2) a
where a.a1='李四') c
where
b.a2=c.a2(+)
select b.a2,b.a3,c.a3
from
(select a2,a3
from
(SELECT a1,a2,count(*) as a3 from aa group by a1,a2) a
where a.a1='张三') b ,
(select a2,a3
from
(SELECT a1,a2,count(*) as a3 from aa group by a1,a2) a
where a.a1='李四') c
where
b.a2=c.a2(+)
from aa
group by a2