Mysql 解决 分组统计问题

 

 我有四张表A、B、C、D，    
  --------------------------------     
  A　     |　B　 |　C　  |　D    
  a   b   |   a   c   |   a   d   |   d   e    
  1   1   |   1   1   |   1   A   |   A   1    
  2   2   |   1   3   |   2   B   |   B   2    
  3   4   |   1   4   |   3   C   |   C   3    
  4   6   |   2   1   |   4   D   |   D   4    
  --------------------------------    
  现在要用一条sql语句将A表所有的列，B表对与A表a字段    
  相关联的列求count,通过C表,将D表与A表关联起来，得到    
  结果    
  1   1   3   1    
  2   2   1   2    
  3   4   0   3    
  4   6   0   4  

Mysql语句解决:

select  A.a,A.b,IFNULL(c, 0 )  as  c,D.a 
from  ( select   1   as  a, 2   as  b  union   all
select   2   as  a, 2   as  b  union   all
select   3   as  a, 4   as  b  union   all
select   4   as  a, 6   as  b)  as  A 
left   outer   join      
( select  a, count (a)  as  c  from (
select   1   as  a, 3   as  c  union   all
select   1   as  a, 3   as  c  union   all
select   1   as  a, 3   as  c  union   all
select   2   as  a, 3   as  c ) B  group   by  a)  as  B
on  A.a = B.a 
join  
( select   1   as  a, ' A '   as  d  union   all
select   2   as  a, ' B '   as  d  union   all
select   3   as  a, ' C '   as  d  union   all
select   4   as  a, ' D '   as  d )  as  C 
on  A.a = C.a 
join ( select   1   as  a, ' A '   as  d  union   all
select   2   as  a, ' B '   as  d  union   all
select   3   as  a, ' C '   as  d  union   all
select   4   as  a, ' D '   as  d )  as  D
on  D.d = C.d