mysql - SELECT DISTINCT 和 CONCAT 未正确返回值

Question

我有一个具有以下值的数据库:

CREATE TABLE test (
Id CHAR (50),
Time TIMESTAMP ,
Value CHAR (50)
);

INSERT INTO test VALUES ('JOE', '2015-06-05 15:36:21', "ZONE1");
INSERT INTO test VALUES ('ANNA', '2015-06-05 14:27:35', "ZONE2");
INSERT INTO test VALUES ('JOE','2015-06-05 16:27:36', "ZONE2");
INSERT INTO test VALUES ('ANNA', '2015-06-05 14:45:31',"ZONE1");
INSERT INTO test VALUES ('JOE','2015-06-05 11:12:31', "ZONE4");
INSERT INTO test VALUES ('ANNA', '2015-06-05 10:25:21',"ZONE3");

我喜欢在最后一个区域查找用户

我尝试使用这个 SQL 指令:

SELECT DISTINCT(Id), MAX(Time) AS Time,
CONCAT('{ "Time":"', DATE_FORMAT(Time, '%Y-%e-%d %h:%i:%s'), '", "ID":"',     CAST(Id AS CHAR), '", "Zone":"', Value, '" }') json
FROM test
GROUP BY Id;

但是，这是返回:

|   Id |                   Time |                                                         json |

|------|------------------------|----------------------------------------------------------------------------------------------|

| ANNA | June, 05 2015 14:45:31 | { "Time":"2015-5-05 02:27:35", "ID":"ANNA", "Zone":"ZONE2" } |

|  JOE | June, 05 2015 16:27:36 |  { "Time":"2015-5-05 03:36:21", "ID":"JOE", "Zone":"ZONE1" } |

我不明白为什么......它返回的日期不同，无序 在第三栏中...

谢谢!

Answer 1

如果我正确理解你的问题，你想做这样的事情:

  SELECT Id, Time,
         CONCAT('{ "Time":"', DATE_FORMAT(Time, '%Y-%e-%d %h:%i:%s'), '", "ID":"', CAST(Id AS CHAR), '", "Zone":"', Value, '" }') json
  FROM (SELECT * 
        FROM test 
        ORDER BY Time DESC) as t
  GROUP BY Id;

这里是SQL Fiddle看看它是如何工作的...

GL!

P.S.(编辑)您不需要同时使用 DISTINCT 和 GROUP BY 函数来完成相同的工作...而且它们还从组中的第一行中选择值，因此当您想从具有某些值(例如 MAX(time))的行中选择值时，只需将其放在排序的顶部即可。我在这个子查询中做到了这一点

(SELECT * 
 FROM test 
 ORDER BY Time DESC) as t

编辑:再次嗨，根据 Andriy 在评论中的建议，我发现我的第一个答案并不是这个问题的最佳解决方案。因此，感谢 Andriy，我带着更好、更安全的解决方案回来了。

第一个是相关子查询:

SELECT Id, Time,
       CONCAT('{ "Time":"', DATE_FORMAT(Time, '%Y-%e-%d %h:%i:%s'), '", "ID":"', CAST(Id AS CHAR), '", "Zone":"', Value, '" }') json
FROM test t1
WHERE Time = (SELECT MAX(t2.Time)
              FROM test t2
              WHERE t1.id = t2.id);

第二个不相关子查询:

 SELECT t1.Id, t1.Time,
       CONCAT('{ "Time":"', DATE_FORMAT(t1.Time, '%Y-%e-%d %h:%i:%s'), '", "ID":"', CAST(t1.Id AS CHAR), '", "Zone":"', t1.Value, '" }') json
 FROM test t1
 INNER JOIN (SELECT id, MAX(Time) AS Time
             FROM test
             GROUP BY id) AS t2
 ON t1.id = t2.id AND t1.Time = t2.Time;

有关此内容的更多信息，您可以阅读 here ，有三种方法可以解决此类问题，我为您编写了第一种和第二种方法的查询，并让您 LEFT JOIN 来玩一下:)

和here is SQL Fiddle看看这个新解决方案是如何工作的。

再次感谢 Andriy M 的建议，为此使用 Group by 和 Order 子句并不明智......

GL!