MySQL:动态添加列以查询结果 [英] MySQL: Dynamically add columns to query results

问题描述

我有这张桌子:

update_id | project_id | content | date
------------------------------------------------------
1         | 1          | text... | 2011-12-20 22:10:30 
2         | 2          | text... | 2011-12-20 22:10:30 
3         | 2          | text... | 2011-12-21 22:10:30 
4         | 2          | text... | 2011-12-22 22:10:30 
5         | 2          | text... | 2011-12-23 22:10:30 

要获取特定项目的最新两个更新，请使用:

To get the latest two updates for a specific project I use:

SELECT update_id, title, content, date
FROM updates
WHERE project_id = 2
ORDER BY date DESC
LIMIT 2

现在，我想根据结果是最新更新还是之前的更新，在结果中动态添加一个"update_time"列，并使用值"LATEST"或"PREVIOUS" 像这样:

Now, I want to dynamically add a 'update_time' column to the results, with the values "LATEST" or "PREVIOUS" based on if it is the latest update or the one before that, like this:

update_time | update_id | content | date
------------------------------------------------------
LATEST      | 5          | text... | 2011-12-23 22:10:30 
PREVIOUS    | 4          | text... | 2011-12-22 22:10:30 

仅当您想知道我为什么需要它时:

Only if you want to know why I need this: MySQL: Select row by id with previous and next rows by date

推荐答案

这是一些聪明的SQL.它在第一行中选择最新"，在所有其他行中选择上一步"(应该有多个).

Here's some smart-ass SQL. It select 'LATEST' for the first row, and 'PREVIOUS' for all others (should we have more than one of them).

SELECT IF(@rownum = 0, 'LATEST', 'PREVIOUS') update_time, update_id, 
       title, content, date, (@rownum := @rownum + 1) r
FROM updates, (SELECT @rownum := 0) dummy
WHERE project_id = 2
ORDER BY date DESC
LIMIT 2

它还将另一列添加到结果集中.希望这不是问题.

Also it adds another column to the resultset. Hope it's not a problem.

这篇关于MySQL:动态添加列以查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！