SQL子查询试图获得两个表中同一列的最大差异

Question

我正在寻找每天两个表之间的最大差异，每个 id。我在mysql数据库中有以下数据

insert into test.foo values ('2010-01-10', 1, 10);
insert into test.foo values ('2010-01-10', 1, 5);
insert into test.foo values ('2010-01-10', 2, 10);
insert into test.foo values ('2010-01-10', 2, 10);
insert into test.foo values ('2010-01-10', 3, 15);
insert into test.foo values ('2010-01-10', 3, 15);
insert into test.foo values ('2010-01-11', 1, 5);
insert into test.foo values ('2010-01-11', 1, 5);
insert into test.foo values ('2010-01-11', 2, 5);
insert into test.foo values ('2010-01-11', 2, 5);
insert into test.foo values ('2010-01-11', 3, 5);
insert into test.foo values ('2010-01-11', 3, 5);

insert into test.bar values ('2010-01-10', 1, 5);
insert into test.bar values ('2010-01-10', 1, 5);
insert into test.bar values ('2010-01-10', 2, 5);
insert into test.bar values ('2010-01-10', 2, 5);
insert into test.bar values ('2010-01-10', 3, 5);
insert into test.bar values ('2010-01-10', 3, 5);
insert into test.bar values ('2010-01-11', 1, 10);
insert into test.bar values ('2010-01-11', 1, 10);
insert into test.bar values ('2010-01-11', 2, 5);
insert into test.bar values ('2010-01-11', 2, 5);
insert into test.bar values ('2010-01-11', 3, 5);
insert into test.bar values ('2010-01-11', 3, 5);

这是我的查询:

SELECT t1.`date`, t1.id, t1.sums, t2.sums, max(t1.sums - t2.sums) FROM
  (select `date`, id, sum(val) sums
   from test.foo
   group by `date`, id) as t1,
  (select `date`, id, sum(val) sums
   from test.bar
   group by `date`, id) as t2
WHERE t1.`date` = t2.`date` AND t1.id = t2.id
group by t1.`date`

我得到这个结果:

+---------------------+----+------+------+------------------------+
| date                | id | sums | sums | max(t1.sums - t2.sums) |
+---------------------+----+------+------+------------------------+
| 2010-01-10 00:00:00 |  1 |   15 |   10 |                     20 |
| 2010-01-11 00:00:00 |  1 |   10 |   20 |                      0 |
+---------------------+----+------+------+------------------------+
2 rows in set (0.00 sec)

我想得到这个结果:我得到了这个结果:

+---------------------+----+------+------+------------------------+
| date                | id | sums | sums | max(t1.sums - t2.sums) |
+---------------------+----+------+------+------------------------+
| 2010-01-10 00:00:00 |  1 |   15 |   10 |                     20 |
| 2010-01-11 00:00:00 |  2 |   10 |   10 |                      0 |  <----- 
+---------------------+----+------+------+------------------------+

谁能帮帮我？我想得到最大的差异，然后是与之相伴的线。这个查询给了我正确的区别，但没有给出它的 id 和总和。一位同事建议也按 id 分组，但正如我所想，这只是将结果拉平，并且列出了每个 id，而不是具有最大差异的那一天的 id。

提前致谢

Answer 1

这个应该适合你。

它按降序对和进行排序，为它们分配一个等级，然后只获得等级为 1 的那些。

SELECT id, `date`, sums FROM (
  SELECT id, `date`, sums,
  CASE
    WHEN @d != `date` THEN @rownum := 1 
    ELSE @rownum := @rownum + 1
  END AS rank,
  @d := `date`
FROM
(
  SELECT t1.`date`, t1.id, t1.sums t1_sums, t2.sums t2_sums, (t1.sums - t2.sums) sums
  FROM
    (select `date`, id, sum(val) sums
     from foo
     group by `date`, id) as t1,
    (select `date`, id, sum(val) sums
     from bar
     group by `date`, id) as t2,
     (SELECT @rownum := 0, @d := NULL) r
  WHERE t1.`date` = t2.`date` AND t1.id = t2.id
  GROUP BY t1.`date`, t1.id, t2.`date`, t2.id
  ORDER BY t1.`date`, (t1.sums - t2.sums) DESC, t1.id
  ) x
) y
WHERE rank = 1