假设我有两个现有的桌子,“狗”和“猫”：
- dog_name | owner
- ---------+------
- Sparky | Bob
- Rover | Bob
- Snoopy | Chuck
- Odie | Jon
- cat_name | owner
- ---------+------
- Garfield | Jon
- Muffy | Sam
- Stupid | Bob
- owner | num_dogs | num_cats
- ------+----------+---------
- Bob | 2 | 1
- Chuck | 1 | 0
- Sam | 0 | 1
- Jon | 1 | 1
解决方法
- select owner,sum(num_dogs),sum(num_cats) from
- (select owner,1 as num_dogs,0 as num_cats from dogs
- union
- select owner,0 as num_dogs,1 as num_cats from cats)
- group by owner
