【每周一议】干货讨论 如何合并两个查询的结果集的数据

unixfiregs

楼主继续。写的很好。

linjia828

mysql> select * from t_new;
+------+-----------+
| id   | date      |
+------+-----------+
|    1 | 2012-11-1 |
|    3 | 2012-11-2 |
|    8 | 2012-11-5 |
+------+-----------+
3 rows in set (0.00 sec)

mysql> select * from t_old;
+------+-----------+
| id   | date      |
+------+-----------+
|    5 | 2012-11-1 |
|    3 | 2012-11-2 |
+------+-----------+
2 rows in set (0.00 sec)

mysql> select c.date, ifnull(a.id, 0) as new, ifnull(b.id, 0) as old
    ->   from (select date from t_new
    ->         union
    ->         select date from t_old) c
    ->   left join t_new a on c.date = a.date
    ->   left join t_old b on c.date = b.date;
+-----------+-----+-----+
| date      | new | old |
+-----------+-----+-----+
| 2012-11-1 |   1 |   5 |
| 2012-11-2 |   3 |   3 |
| 2012-11-5 |   8 |   0 |
+-----------+-----+-----+
3 rows in set (0.00 sec)

哈哈，好像回复晚了吧。

linjia828

mysql> select c.date,
    ->        max(case c.tag when 'new' then c.id else 0 end) as new,
    ->        max(case c.tag when 'old' then c.id else 0 end) as old
    ->   from (select a.*, 'new' as tag from t_new a
    ->         union all
    ->         select b.*, 'old' as tag from t_old b) c group by c.date;
+-----------+------+------+
| date      | new  | old  |
+-----------+------+------+
| 2012-11-1 |    1 |    5 |
| 2012-11-2 |    3 |    3 |
| 2012-11-5 |    8 |    0 |
+-----------+------+------+
3 rows in set (0.00 sec)

这样也行....

hanbing789

路过学习！