这将在所有主要的RDBMS（包括SQL Server和MySql）中都相同
*根据您的评论 *更新
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：
*根据您的评论 *更新
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
如果您使用的是 SQL Server 2012，
则还可以利用分析功能
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') qWHERe rnum = 1
或者
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
或者
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
所有查询的输出：
| NAME | SO_NO | PREV_SO_NO || -------- | ---------- | ------------ ||| 阿德里安| SO-00005 | SO-00002 |
这是 SQLFiddle 演示（ SQL Server 2008
）已更新
这是 SQLFiddle 演示（ SQL Server 2012 ）
这是 SQLFiddle 演示（ MySQL ）
