mysql - 如何将3个表与其他表中具有最大值的表组合起来

Question

我有下面 3 个表:

Table 1:              Table 2:                     Table 3:
+------+-----+----+   +------+-----+----+-------+  +------+-----+----+---+-----+
| a    | b   | c  |   | a    | b   | c  | d     |  | a    | b   | c  | e | f   |
+------+-----+----+   +------+-----+----+-------+  +------+-----+----+---+-----+
| 1000 |  10 |  0 |   | 1000 |  10 |  0 |  5000 |  | 1000 |  10 |  0 | 3 |  40 | 
| 1000 |  20 |  1 |   | 1000 |  10 |  0 | 10000 |  | 1000 |  10 |  0 | 6 |  80 |
| 1000 | 100 |  1 |   | 1000 |  20 |  1 |  7500 |  | 1000 |  20 |  1 | 5 |  30 |  
| 1000 | 100 |  0 |   | 1000 | 100 |  0 |  1000 |  | 1000 |  20 |  1 | 6 |  70 |  
| 1000 | 100 |  2 |   | 1000 | 100 |  0 |  4000 |  | 1000 |  20 |  1 | 8 | 100 | 
| 2000 | 200 | 10 |   | 1000 | 100 |  1 | 10000 |  | 1000 | 100 |  0 | 2 |  20 |
                      | 1000 | 100 |  2 |  5000 |  | 1000 | 100 |  0 | 7 |  70 |
                      | 1000 | 100 |  2 | 20000 |  | 1000 | 100 |  1 | 2 |  20 |
                      | 2000 | 200 | 10 |  1000 |  | 1000 | 100 |  1 | 6 |  60 |
                      | 2000 | 200 | 10 |  4000 |  | 1000 | 100 |  2 | 2 |  20 |
                      | 2000 | 200 | 10 | 20000 |  | 1000 | 100 |  2 | 6 |  80 |
                                                   | 1000 | 100 |  2 | 8 |  90 |
                                                   | 2000 | 200 | 10 | 6 |  80 |

预期结果是这样的:
列 (b) 应该是唯一的
(c)列是a和b中的最大值
第(d)列是求c的最大值的和(如果a和b的c中有0,1,2，则只需要取值2)丢弃值0和1
(e)栏和(f)栏也相同。只需要获取数据a和b的最大值

+------+-----+----+-------+---+-----+
| a    | b   | c  | d     | e | f   |
+------+-----+----+-------+---+-----+
| 1000 |  10 |  0 | 15000 | 6 |  80 | 
| 1000 |  20 |  1 |  7500 | 8 | 100 |
| 1000 | 100 |  2 | 25000 | 8 |  90 |  
| 2000 | 200 | 10 | 25000 | 6 |  80 |
+------+-----+----+-------+---+-----+   

Answer 1

您可以在子查询中查找最大值。这允许您使用结果在下一个表中进行搜索。例如:

select  t1.a
,       t1.b
,       t1.max_c
,       t2.sum_d
,       t3.max_e
,       t3.max_f
from    (
        select  a
        ,       b
        ,       max(c) as max_c
        from    table1
        group by
                a
        ,       b
        ) t1
left join    
        (
        select  a
        ,       b
        ,       c
        ,       sum(d) as sum_d
        from    table2
        group by
                a
        ,       b
        ,       c
        ) t2
on      t1.a = t2.a
        and t1.b = t2.b
        and t1.max_c = t2.c
left join    
        (
        select  b
        ,       max(e) as max_e
        ,       max(f) as max_f
        from    table3
        group by
                b
        ) t3
on      t1.b = t3.b

Example at SQL Fiddle.