具有唯一日期排序的 MySQL varchar

Question

我有一个 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;

感谢您的任何帮助。

Answer 1

您应该

能够拉出年-月-日部分并在该列上使用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;

Answer 2

尝试这样的事情...

SELECT DISTINCT build_number
FROM test_case_executions
WHERE
    build_number LIKE 'TKSUS%'
    AND build_number IS NOT NULL
    --this clause gives you only records with date-stamps in the past 4 days
    AND DATE(SUBSTRING(build_number, 7, 9)) >= DATESUB(CURDATE(), INTERVAL 4 DAY)
ORDER BY
    --these two order by clauses will sort by the date descending, and the time ascending
    SUBSTRING(build_number, 7, 9) DESC,
    SUBSTRING(build_number, 15, 4) ASC
;

Answer 3

我想我会考虑将build_number字段拆分为单独的客户端和日期字段，但这应该有效：

SELECT DISTINCT build_number FROM test_case_executions
  WHERE build_number LIKE CONCAT('TKSUS-',REPLACE(CURRENT_DATE - INTERVAL 4 DAY,'-',''),'%')
ORDER BY build_number DESC;