对复杂的标题,很抱歉,不确定如何最好地解释。
我有以下查询:
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
并用它对相应的行进行排序(实际上不更新中的该行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 *
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句