mysql 按两个 unixtime 列组合排序

Question

表用户结构:

id(PK)(int)
name(varchar)
time1(int10) unixtime
time2(int10) unixtime

SELECT * FROM `user` ORDER BY `time1` DESC,`time2` DESC


 +------------------------------------------------------+
 | id        | name   |     time1     |     time2       |
 +------------------------------------------------------+
 | 12345     | Joe    |  1405605785   |   1406733506    |
 | 12346     | John   |  1406733506   |                 |
 | 12347     | David  |               |   1405684190    |
 +------------------------------------------------------+

我正在使用这个 sql:

SELECT * FROM `user` ORDER BY `time1` DESC,`time2` DESC

如何合并两列time对desc进行排序？

============

sagi 的更多示例回复

 +------------------------------------------------------+
 | id        | name   |     time1     |     time2       |
 +------------------------------------------------------+
 | 12345     | Joe    |  1            |   2             |
 | 12346     | John   |  5            |                 |
 | 12347     | David  |               |   4             |
 +------------------------------------------------------+

我想这样排序(DESC)

John (time = 5)
David (time = 4)
Joe (time  max value = 2)

Answer 1

这称为使用 CASE EXPRESSION 的条件排序:

SELECT * FROM `user` t
ORDER BY CASE WHEN t.time1 is null THEN t.time2 ELSE t.time1 END DESC

也可以写成:

ORDER BY coalesce(t.time1,t.time2)

你没有提供任何预期的结果，也没有说明结果应该按哪一列排序，以防 time1,time2 都不是 NULL ，所以我假设你希望它按第一列排序。如果不是这种情况，请将 time1 替换为 time2。

试试这个:

ORDER BY GREATEST(t.time1,t.time2) DESC