mysql - MySQL varchar具有唯一的日期排序

我有一个mysql表，它的内部版本号格式如下

client-yearmonthdayhourminute
TKSUS-201310210353

我想建立一个查询，这将使我过去4天都没有问题。我们可能一天要进行多次跑步，所以我想当天进行一次。

TKSUS-201310210353
TKSUS-201310210153
TKSUS-201310200353
TKSUS-201310190353
TKSUS-201310180353

我尝试了以下类似的操作，但如果一天多次运行，则会中断

SELECT DISTINCT build_number FROM test_case_executions WHERE build_number LIKE 'TKSUS%' AND build_number IS NOT NULL ORDER BY build_number DESC LIMIT 4;

甚至尝试过这样的事情

SELECT DISTINCT SUBSTR(build_number, 7, LENGTH(build_number)-6) FROM test_case_executions WHERE build_number LIKE 'TKSUS%' ORDER BY build_number DESC LIMIT 4;

谢谢你的帮助。

最佳答案

您应该可以拉出年月日部分，并在该列上使用GROUP BY，然后在该组中选择MAX(build_number)：

SELECT
  SUBSTR(build_number, 7, 8) AS day,
  MAX(build_number) AS most_recent_build,
  COUNT(build_number) AS num_builds
FROM test_case_executions
  WHERE build_number LIKE 'TKSUS-%'
  GROUP BY day
  ORDER BY day DESC
  LIMIT 4;

关于mysql - MySQL varchar具有唯一的日期排序，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19505569/