PHP/MySQL在排序时，SELECT结果中的某些行对特定列有不同的值 - PHP/MySQL Have some rows in SELECT result have different values for a particular column when sorting

PHP/MySQL Have some rows in SELECT result have different values for a particular column when sorting

本文关键字：排序 MySQL SELECT PHP 结果 | 更新日期: 2023-09-27

对了，很抱歉，这个复杂的标题我不知道如何最好地解释。

我有以下查询:

SELECT * FROM uc_posts WHERE `postinguser` = 5 AND `id` NOT IN (SELECT dwable FROM uc_redwables WHERE redwabledby = 5) 
UNION ALL 
SELECT * FROM uc_posts WHERE id IN (SELECT dwable FROM uc_redwables WHERE redwabledby = 5) 
ORDER BY time DESC LIMIT 20

表uc_posts看起来像这样:

id | postinguser | time                | message |
1        5         2016-08-08 23:50:45      *
2        5         2016-08-08 23:50:35      *
3        5         2016-08-08 23:50:25      *
4        5         2016-08-08 23:50:15      *

表uc_redwables看起来像这样:

id | dwable | redwabledby | time                | 
1      3          5         2016-08-08 23:51:15

输出如下所示:

   id | postinguser | time                | message |
1        5            2016-08-08 23:50:45      *
2        5            2016-08-08 23:50:35      *
3        5            2016-08-08 23:50:25      *
4        5            2016-08-08 23:50:15      *

我正试图找到一种方法来替换uc_redwables表中所选行(s)中的时间，并与之排序相应的行(而不实际更新uc_posts中的行)。

所以它看起来像这样:

   id | postinguser | time                | message |
3        5            2016-08-08 23:51:15      *     //time changed   
1        5            2016-08-08 23:50:45      *
2        5            2016-08-08 23:50:35      *
4        5            2016-08-08 23:50:15      *

有人能帮忙吗?谢谢!

试试这个:

SELECT 
 p.`id` as id, 
 p.`postinguser` as postinguser,
 p.`message` as message,
 case r.dwable
  when p.id then r.`time`
  else p.`time`
  end as date_time 
FROM `uc_posts` p left join `uc_redwables` r on p.id = r.dwable
WHERE p.`postinguser` = 5 
AND r.redwabledby = 5 
order by date_time desc;

这会产生如下输出:

   id | postinguser | date_time                | message |
3        5            2016-08-08 23:51:15      *      
1        5            2016-08-08 23:50:45      *
2        5            2016-08-08 23:50:35      *
4        5            2016-08-08 23:50:15      *