如何更新文章上一篇和下一篇id

Question

表结构为：文章编号（BH）  上一篇文章编号(PREID)    下一篇文章编号(NEXTID)    栏目编号(CHANNLID)
现在库中已经有一些数据了想把每条记录的上PREID和NEXTID更新为同一栏目下的上一篇文章的BH和下一遍文章的BH，没有的话就空着例如没更新前：
BH      PREID     NEXTID    CHANNELID
1                           01
2                           01
3                           01
4                           02
5                           02
更新后：
BH      PREID     NEXTID    CHANNELID
1                 2         01
2       1         3         01
3       2                   01
4                 5         02
5       4                   02用一个sql能实现吗？效率会不会很低？数据量不太大 1w条之内

Answer 1

能
mysql> select * from t_finalsyo;
+----+-------+--------+-----------+
| BH | PREID | NEXTID | CHANNELID |
+----+-------+--------+-----------+
|  1 |  NULL |   NULL |         1 |
|  2 |  NULL |   NULL |         1 |
|  3 |  NULL |   NULL |         1 |
|  4 |  NULL |   NULL |         2 |
|  5 |  NULL |   NULL |         2 |
+----+-------+--------+-----------+
5 rows in set (0.00 sec)mysql> update t_finalsyo t
    ->  left join (
    ->          select a.BH,max(b.BH) as PREID
    ->          from t_finalsyo a,t_finalsyo b
    ->          where a.BH>b.BH and a.CHANNELID=b.CHANNELID
    ->          group by a.BH) t1 on t.BH=t1.BH
    ->  left join (
    ->          select a.BH,min(b.BH) as NEXTID
    ->          from t_finalsyo a,t_finalsyo b
    ->          where a.BH<b.BH and a.CHANNELID=b.CHANNELID
    ->          group by a.BH) t2 on t.BH=t2.BH
    -> set t.PREID=t1.PREID,t.NEXTID=t2.NEXTID;
Query OK, 5 rows affected (0.05 sec)
Rows matched: 5  Changed: 5  Warnings: 0mysql> select * from t_finalsyo;
+----+-------+--------+-----------+
| BH | PREID | NEXTID | CHANNELID |
+----+-------+--------+-----------+
|  1 |  NULL |      2 |         1 |
|  2 |     1 |      3 |         1 |
|  3 |     2 |   NULL |         1 |
|  4 |  NULL |      5 |         2 |
|  5 |     4 |   NULL |         2 |
+----+-------+--------+-----------+
5 rows in set (0.00 sec)mysql>