mysql - H2提示语法错误，MySQL接受它-但是，语法错误会给出正确的结果: Column … must be in the GROUP BY list;

Question

注意:已对问题进行了编辑，以包括表格并显示结果的确切含义。
可以说我们有一个SQL表，它是通过以下方式生成的:

CREATE TABLE T1 (
  `a` INTEGER,
  `b` DATETIME,
  `c` VARCHAR(5)
);

INSERT INTO T1
  (`a`, `b`, `c`)
VALUES
  ('5678', '2008-01-01 12:00', '12.34'),
  ('5678', '2008-01-01 12:01', NULL),
  ('5678', '2008-01-01 12:02', NULL),
  ('5678', '2008-01-01 12:03', '23.45'),
  ('5678', '2008-01-01 12:04', NULL);

我需要执行的是

SELECT * FROM(
  SELECT a, b, c  from T1
)AS Q GROUP BY c ORDER BY a, b;

这使:

5678    2008-01-01 12:00:00     12.34
5678    2008-01-01 12:01:00     NULL
5678    2008-01-01 12:03:00     23.45

H2建议(并接受)的是

SELECT * FROM(
  SELECT a, b, c  from T1
)AS Q GROUP BY a,b,c ORDER BY a, b, c;

这使

5678    2008-01-01 12:00:00     12.34
5678    2008-01-01 12:01:00     NULL
5678    2008-01-01 12:02:00     NULL
5678    2008-01-01 12:03:00     23.45
5678    2008-01-01 12:04:00     NULL

根据您的一些建议，这些是查询和结果。
建议1:

SELECT  max(a) as a, max(b) as b, c
FROM (
  SELECT a, b, c  from T1
) AS Q 
GROUP BY c 
ORDER BY a, b;

给

5678    2008-01-01 12:00:00     12.34
5678    2008-01-01 12:03:00     23.45
5678    2008-01-01 12:04:00     NULL

建议2:

SELECT * 
FROM (
  SELECT a, b, c from T1
) AS Q 
GROUP BY c, a, b 
ORDER BY a, b;

给

5678    2008-01-01 12:00:00     12.34
5678    2008-01-01 12:01:00     NULL
5678    2008-01-01 12:02:00     NULL
5678    2008-01-01 12:03:00     23.45
5678    2008-01-01 12:04:00     NULL

=====================================
我需要获取第一个查询执行的结果。
如何在获得所需结果的同时适当更改语法以适应H2要求？

Answer 1

现在，针对修改后的问题。根据样本数据，在H2中可以执行以下操作:

select t1.*
from t1
join (
  select c, min(b) as min_b from t1 group by c
) x on t1.c is not distinct from x.c and t1.b = x.min_b
order by t1.b;

结果:

A     B                      C     
----  ---------------------  ------
5678  2008-01-01 12:00:00.0  12.34 
5678  2008-01-01 12:01:00.0  <null>
5678  2008-01-01 12:03:00.0  23.45 

重现此情况的样本数据脚本是:

create table t1 (
  a integer,
  b datetime,
  c varchar(5)
);

insert into t1 (a, b, c) values
  ('5678', timestamp '2008-01-01 12:00:00', '12.34'),
  ('5678', timestamp '2008-01-01 12:01:00', null),
  ('5678', timestamp '2008-01-01 12:02:00', null),
  ('5678', timestamp '2008-01-01 12:03:00', '23.45'),
  ('5678', timestamp '2008-01-01 12:04:00', null);