在ONE ROW中获取Prevision Next记录 [英] Get Prevision Next record in ONE ROW

问题描述

我想在一行中获得上一个\下一个ID.如果不是next或prev rec的值，则必须为NULL.

I want get prev\next id in one row. If not value for next or prev rec its must be NULL.

mysql> select picsid  from pics where  albid_pics=15;
+--------+
| picsid |
+--------+
|    110 |
|    111 |
|    112 |
|    113 |
|    114 |
|    115 |
|    116 |
|    117 |
|    131 |
|    132 |
|    133 |
|    134 |
|    135 |
|    136 |
|    153 |
|    154 |
|    155 |
|    156 |
|    157 |
|    159 |
|    160 |
+--------+
21 rows in set (0.00 sec)

mysql> 

1.如果我愿意

(select picsid prv from pics where picsid<136 and albid_pics=15 order by prv desc limit 1)
union
(select picsid nxt from pics where picsid>136 and albid_pics=15 order by nxt asc limit 1);
+-----+
| prv |
+-----+
| 135 |
| 153 |
+-----+
2 rows in set (0.00 sec)

mysql> 

但是我想在1行中得到qresult，

But i want get qresult in 1 row,

mysql> (select picsid prv from pics where picsid<160 and albid_pics=15 order by prv desc limit 1)  union  (select picsid nxt from pics where picsid>160 and albid_pics=15 order by nxt asc limit 1);
+-----+
| prv |
+-----+
| 159 |
+-----+
1 row in set (0.00 sec)

mysql> 

如果下一个或上一个记录的值不是，则必须为NULL.

If not value for next or prev record its must be NULL.

推荐答案

您要查找的内容非常简单.例如，这:

What you're looking for is quite simple. For instance, this:

SELECT (1+1) AS sum1,(2+2) AS sum2

将导致:

+------+------+
| sum1 | sum2 |
+------+------+
|    2 |    4 |
+------+------+

您所要做的就是将子查询放在其中:

All you have to do is put your subqueries in there:

SELECT (SELECT picsid 
        FROM pics 
        WHERE picsid < 136 AND 
              albid_pics = 15 
        ORDER BY picsid DESC 
        LIMIT 1) AS previous,
        (SELECT picsid 
        FROM pics 
        WHERE picsid > 136 AND 
              albid_pics = 15 
        ORDER BY picsid ASC 
        LIMIT 1) AS `next`;

注意:next是MySQL中的关键字，因此是反引号.

Note: next is a keyword in MySQL, hence the backticks.

这篇关于在ONE ROW中获取Prevision Next记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！