如何获取下一个/上一个记录号? [英] How to get next/previous record number?
问题描述
如何在 SQL Server 中获取以前的记录号?
数据表:
名称 |所以不行.-----------------阿德里安 |SO-00001阿德里安 |SO-00002比安卡 |SO-00003嘉莉 |SO-00004阿德里安 |SO-00005比安卡 |SO-00006阿德里安 |SO-00007
我怎样才能得到如下结果:
名称 |号 |以前的 SO-------------------------------阿德里安 |SO-00005 |SO-00002
这个在所有主要的 RDBMS(包括 SQL Server 和 MySql)中都是一样的
更新基于您的评论
选择名称,所以不行,(选择最大(so_no)从表 1WHERE so_no <t.so_noAND 名称 = t.name) prev_so_noFROM table1 tWHERE so_no = 'SO-00005'
SQL Server:
更新基于您的评论
选择名称,MAX(so_no) so_no,MAX(so_no) = MIN(so_no) 时的情况然后为空ELSE MIN(so_no)END prev_so_no从(选择 TOP 2 t1.name, t1.so_noFROM table1 t1 JOIN table1 t2ON t1.name = t2.nameWHERE t2.so_no = 'SO-00005'AND t1.so_no <= t2.so_noORDER BY so_no DESC) q按名称分组
如果您使用的是 SQL Server 2012,那么您还可以利用分析功能 滞后
选择名称,so_no,prev_so_no从(选择名称,so_no,LAG(so_no, 1, NULL) OVER (ORDER BY so_no) prev_so_no,ROW_NUMBER() OVER (ORDER BY so_no DESC) rnum从表 1WHERE 名称 = '阿德里安'AND so_no <= 'SO-00005') q哪里 rnum = 1
或
SELECT TOP 1 name, so_no, prev_so_no从(选择名称,so_no,LAG(so_no, 1, NULL) OVER (ORDER BY so_no) prev_so_no从表 1WHERE 名称 = '阿德里安'AND so_no <= 'SO-00005') qORDER BY so_no DESC
MySQL:
选择名称,MAX(so_no) so_no,MAX(so_no) = MIN(so_no) 时的情况然后为空ELSE MIN(so_no)END prev_so_no从(选择名称,so_no从表 1WHERE 名称 = '阿德里安'AND so_no <= 'SO-00005'ORDER BY so_no DESC限制 2) q按名称分组
或
选择名称,SUBSTRING_INDEX(so_no, ',', 1) so_no,CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(so_no, ',', 2), ',', -1) = SUBSTRING_INDEX(so_no, ',', 1)然后为空ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(so_no, ',', 2), ',', -1)END prev_so_no从(SELECT 名称,GROUP_CONCAT(so_no ORDER BY so_no DESC) so_no从表 1WHERE 名称 = '阿德里安'AND so_no <= 'SO-00005'按名称分组) q
<小时>
所有查询的输出:
<前>|姓名 |SO_NO |PREV_SO_NO ||--------|-----------|------------||阿德里安 |SO-00005 |SO-00002 |这是 SQLFiddle 演示(SQL服务器 2008) 更新
这是 SQLFiddle 演示(SQL Server 2012)
这是 SQLFiddle 演示(MySQL)
How to get previous record number in SQL Server?
Data Table:
Name | SO No.
-----------------
Adrian | SO-00001
Adrian | SO-00002
Bianca | SO-00003
Carrie | SO-00004
Adrian | SO-00005
Bianca | SO-00006
Adrian | SO-00007
How can I get the result as below:
Name | SO No. | Previous SO
-------------------------------
Adrian | SO-00005 | SO-00002
This one will work the same in all major RDBMS (including SQL Server and MySql)
UPDATED based on your comments
SELECT name,
so_no,
(
SELECT MAX(so_no)
FROM table1
WHERE so_no < t.so_no
AND name = t.name
) prev_so_no
FROM table1 t
WHERE so_no = 'SO-00005'
SQL Server:
UPDATED based on your comments
SELECT name,
MAX(so_no) so_no,
CASE WHEN MAX(so_no) = MIN(so_no)
THEN NULL
ELSE MIN(so_no)
END prev_so_no
FROM
(
SELECT TOP 2 t1.name, t1.so_no
FROM table1 t1 JOIN table1 t2
ON t1.name = t2.name
WHERE t2.so_no = 'SO-00005'
AND t1.so_no <= t2.so_no
ORDER BY so_no DESC
) q
GROUP BY name
if you're using SQL Server 2012 then you can also utilize analytic function LAG
SELECT name, so_no, prev_so_no
FROM
(
SELECT name, so_no,
LAG(so_no, 1, NULL) OVER (ORDER BY so_no) prev_so_no,
ROW_NUMBER() OVER (ORDER BY so_no DESC) rnum
FROM table1
WHERE name = 'Adrian'
AND so_no <= 'SO-00005'
) q
WHERE rnum = 1
or
SELECT TOP 1 name, so_no, prev_so_no
FROM
(
SELECT name, so_no,
LAG(so_no, 1, NULL) OVER (ORDER BY so_no) prev_so_no
FROM table1
WHERE name = 'Adrian'
AND so_no <= 'SO-00005'
) q
ORDER BY so_no DESC
MySQL:
SELECT name,
MAX(so_no) so_no,
CASE WHEN MAX(so_no) = MIN(so_no)
THEN NULL
ELSE MIN(so_no)
END prev_so_no
FROM
(
SELECT name, so_no
FROM table1
WHERE name = 'Adrian'
AND so_no <= 'SO-00005'
ORDER BY so_no DESC
LIMIT 2
) q
GROUP BY name
or
SELECT name,
SUBSTRING_INDEX(so_no, ',', 1) so_no,
CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(so_no, ',', 2), ',', -1) = SUBSTRING_INDEX(so_no, ',', 1)
THEN NULL
ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(so_no, ',', 2), ',', -1)
END prev_so_no
FROM
(
SELECT name, GROUP_CONCAT(so_no ORDER BY so_no DESC) so_no
FROM table1
WHERE name = 'Adrian'
AND so_no <= 'SO-00005'
GROUP BY name
) q
Output for all queries:
| NAME | SO_NO | PREV_SO_NO | |--------|----------|------------| | Adrian | SO-00005 | SO-00002 |
Here is SQLFiddle demo (SQL Server 2008) UPDATED
Here is SQLFiddle demo (SQL Server 2012)
Here is SQLFiddle demo (MySQL)
这篇关于如何获取下一个/上一个记录号?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!