mysql命令由两个unixtime列组合而成

Table user structure:

表用户结构:

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    |
 +------------------------------------------------------+

I am using this sql:

我正在使用这个sql:

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

How to combine two columns time to sort desc?

如何组合两列时间来排序desc?

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

more example reply for sagi

更多sagi的回复示例

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

I want to sort like that (DESC)

我想这样排序(DESC)

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

1 个解决方案

#1

This is called conditional ordering using CASE EXPRESSION :

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

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

Could also be written as:

也可以写成:

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

You didn't provide any expected results, and you didn't say by which column the result should be ordered in case both time1,time2 are not NULL , so I assumed you want it to be ordered by the first column. If that's not the case, replace time1 with time2 .

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

Try this:

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

#1