mysql 은 모르는 관계로 -_-;

mssql 로 작성했습니다.

B와 C 테이블의 차이가 뭔지요????

동일 컬럼에 동일 목적인듯한데..

1개의 Table 로 관리가 되어도 무방할듯한데요..

-_-;;;

동일 목적의 Tbl 에 동일컬럼이라

제 능력에서는 하기 형태로밖에 안나오네요..

DROP TABLE #A
Create Table #A
(
  [num]  [Decimal](18,0) Null
 ,[name] [char](10) Null
)

Drop Table #B
Create Table #B
(
  [num]  [Decimal](18,0) Null
 ,[wdate] [char](10) Null
 ,[etc]   [char](10) Null
)

Drop Table #C
Create Table #C
(
  [num]  [Decimal](18,0) Null
 ,[wdate] [char](10) Null
 ,[etc]   [char](10) Null
)

Insert
  Into #A
Select 1,'NAME1' Union All
Select 2,'NAME2'

Insert
  Into #B
Select 1,'2014-10-13','BBB' Union All
Select 1,'2014-10-13','CCC' Union All
Select 1,'2014-10-14','DDD' Union All
Select 2,'2014-10-13','EEE'

Insert
  Into #C
Select 1,'2014-10-13','EEE' Union All
Select 2,'2014-10-15','FFF' Union All
Select 2,'2014-10-16','GGG'

    Select a.num
          ,a.wdate
          ,a.name
          ,SUM(a.tblb_cnt)
          ,SUM(a.tblc_cnt)
         
      From
            (
                Select #A.num
                      ,d.wdate
                      ,#A.name
                      ,ISNULL(d.cnt,0) As tblb_cnt
                      ,0               As tblc_cnt
                  From #A
                          Inner Join
                                          (
                                               Select COUNT(1) As cnt
                                                     ,a.num
                                                     ,a.wdate
                                                 From #B a
                                             Group By a.num
                                                     ,a.wdate
                                          ) As d
                                                 On #A.num = d.num        
               
             Union All
               
                Select #A.num
                      ,e.wdate
                      ,#A.name
                      ,0               As tblb_cnt
                      ,ISNULL(e.cnt,0) As tblc_cnt
                  From #A
                          Inner Join
                                          (
                                               Select COUNT(1) As cnt
                                                     ,a.num
                                                     ,a.wdate
                                                 From #C a
                                             Group By a.num
                                                     ,a.wdate
                                          ) As e
                                                 On #A.num = e.num        
                                                
            ) a
    Group By a.num
            ,a.wdate
            ,a.name
    Order By a.wdate ASC
            ,a.num
            ,a.name        
           

SELECT b.num
     , b.wdate
     , a.name
     , b.cnt_b
     , b.cnt_c
  FROM (SELECT num, wdate
             , MAX(cnt_b) cnt_b
             , MAX(cnt_c) cnt_c
          FROM (SELECT num, wdate
                     , COUNT(*) cnt_b
                     , 0        cnt_c
                  FROM b
                 GROUP BY num, wdate
                 UNION ALL
                SELECT num, wdate
                     , 0        cnt_b
                     , COUNT(*) cnt_c
                  FROM c
                 GROUP BY num, wdate
                ) b
         GROUP BY num, wdate
        ) b
 INNER JOIN a
    ON a.num = b.num
 ORDER BY b.wdate, b.num
;