union을 활용해보세요. 더 좋은 답은 잘 모르겠습니다.

그리고 사실 위의 경우에서는 (정확한 상황은 모르겠지만), 테이블의 구조를 바꾸는 편이 나으리라 생각되는 군요.

 

mysql> select * from test_a;

+------------+------------+------+------+

| date1 | user | a1 | a2 |

+------------+------------+------+------+

| 2007-01-01 | anonymous1 | 1000 | 1000 |

| 2007-01-01 | anonymous2 | 5000 | 3000 |

| 2007-01-03 | anonymous3 | 2000 | 3000 |

| 2007-01-04 | anonymous1 | 1000 | 1000 |

+------------+------------+------+------+

4 rows in set (0.00 sec)

 

mysql> select * from test_b;

+------------+------+------+

| date2 | a3 | a4 |

+------------+------+------+

| 2007-01-01 | 1000 | 1000 |

| 2007-01-02 | 5000 | 3000 |

| 2007-01-04 | 100 | 300 |

| 2007-01-04 | 200 | 400 |

+------------+------+------+

4 rows in set (0.00 sec)

 

select `date` ,sum(`a1`), sum(`a2`), sum(`a3`), sum(`a4`) from (

( select `date1` as `date`, `a1`, `a2`, 0 as `a3`, 0 as `a4` from `test_a` ) union ( select `date2` as `date`, 0 as `a1`, 0 as `a2`, `a3`, `a4` from `test_b` ) ) t group by `date`

 

+------------+-----------+-----------+-----------+-----------+

| date | sum(`a1`) | sum(`a2`) | sum(`a3`) | sum(`a4`) |

+------------+-----------+-----------+-----------+-----------+

| 2007-01-01 | 6000 | 4000 | 1000 | 1000 |

| 2007-01-02 | 0 | 0 | 5000 | 3000 |

| 2007-01-03 | 2000 | 3000 | 0 | 0 |

| 2007-01-04 | 1000 | 1000 | 300 | 700 |

+------------+-----------+-----------+-----------+-----------+

4 rows in set (0.00 sec)