php - MySQL 两个数据库，在一种情况下按一列排序，在不同情况下按另一列排序

Question

我有两张 table 。第一张表如下所示:

表:记录

    rid  |   user id   |     title     |    whenadded    | public--------------------------------------------------------------------    1        212           Example        2012-06-28         1    2        217           Test Rec       2012-07-05         1    3        212           Another        2012-07-02         1    4        212           Unlisted       2012-05-02         0    5        217           Success        2012-04-08         1    6        238           Always         2012-04-18         1

Table: Likes

    id  |   user id   |     rid     |    whenliked------------------------------------------------------    1        212            2            2012-07-06    2        205            1            2012-06-30    3        212            5            2012-07-04

In the 'Records' table, 'rid' is set as the primary index. In the 'Likes' table, id is set as the primary index.

I'm using PHP. PHP will provide a value to MySQL to use as reference. I'd like to have a single MySQL query that will do the following:

Pseudo Code:

$userid = 212;
$SQL = 'SELECT DISTINCT records.* 
        FROM records,likes 
        WHERE (records.userid = ' . $userid . 
          ' AND records.public = 1) 
            OR (records.id = likes.rid AND likes.userid = ' . $userid . 
          ' AND records.public = 1) 
        ORDER BY likes.whenliked DESC, records.whenadded DESC 
        LIMIT 50;';

看看我刚刚在上面提供的 $SQL 查询。那是我开发我想要的查询的尝试，但它没有实现我想要的；它非常接近，但它仍然订购不正确。该查询首先由我自己尽可能地开发，然后基于我通过在 StackFlow 和 Google 其他地方搜索解决方案而发现的内容。

这些是我尝试订购的条件:

1. 所有选择的记录都必须是公开的 (records.public = 1)。
2. 如果记录属于用户(在此示例中为 212)，则按 records.whenadded 对记录进行排序。
3. 如果记录不属于用户，但它是用户喜欢的记录，则按 likes.whenliked 对记录进行排序。
4. 日期将从最新到最旧排序。

返回的查询的最终结果如下所示(请记住，当 liked 不在返回数据中时，它仅供引用，因此您可以查看它的排序方式):

    rid  |   user id   |     title     |    whenadded    | public   |  whenliked {not incl}------------------------------------------------------------------------------------    2        217           Test Rec       2012-07-05         1        2012-07-06    3        212           Another        2012-07-02         1    5        217           Success        2012-04-08         1        2012-06-30    1        212           Example        2012-06-28         1

希望这是有道理的。随时提问，我会尽我所能澄清。

提前感谢您抽出时间、考虑并阅读本文。即使没有回应或找不到解决方案，仍然非常感谢您的宝贵时间! :)

Answer 1

您订购的字段不必是数据库中的现有字段。您还可以使用您在选择中定义的字段:

SELECT IF(records.userid = ' . $userid . ', records.whenadded, likes.whenliked) as date 

现在您可以按部分顺序使用它了:

ORDER BY date DESC

来自MySQL Manual :

A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses