表A
A B
1 张三
2 张三
3 张三
表B
C D
张三 数学
张三 语文
张三 化学
张三 英语
如何才能求出下面这种结果：
表C
A B E
1 张三 4
2 张三 4
3 张三 4
请忽略数据冗余，我只是为了演示下。
------解决思路----------------------
--上边按错，不好意思
select a.a,a.b,c.e
from a join (select c,count(1) as d from b group by c) c on a.a = c.c
------解决思路----------------------
select A,B count(D)as E from (
select * from 表A as a left join 表B as b on a.B=b.C
) as c
------解决思路----------------------
select a.a,a.b,c.e
from a join (select c,count(1) as d from b group by c) c on a.b = c.c
--用姓名关联？
------解决思路----------------------
SELECT A.A,A.B,COUNT(B.A)E FROM A LEFT JOIN B ON A.B=B.A GROUP BY A.A,A.B
------解决思路----------------------
select a.a,a.b,COUNT(a.a) as e from a join b
on a.b=b.c
group by a.a
------解决思路----------------------
select *,(select COUNT(*) from 表B where c=b)e from 表A
------解决思路----------------------
select a.a,a.b,c.e
from a join (select c,count(1) as d from b group by c) c on a.a = c.c