我有以下声明
SELECT id, descr from mytable
它会返回
1,'Test1‘
4,'Test4‘
6,'Test6‘
8,'Test8‘
22,'Test22‘
现在我想让显示器在前4个结果中添加一个连续的字符...
'A',1,'Test1‘
'B',4,'Test4‘
'C',6,'Test6‘
'D',8,'Test8‘
'',22,'Test22‘
有什么想法?
编辑:如果可能的话,我更喜欢SQL Server 2000的例子。
发布于 2009-01-27 16:48:53
SELECT CASE WHEN ROWNUMBER < 4
THEN CHAR(65 + ROWNUMBER - 1)
ELSE ''
AS <WHATEVER>
,X.id
,X.descr
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY <WHATEVER>) AS ROWNUMBER
,id
,descr
FROM mytable
) AS X
这是在SQL Server 2005及更高版本中实现的。
在SQL Server 2000中(假设id是排序顺序且是唯一的):
SELECT CASE WHEN rownumbers.rownumber < 4
THEN CHAR(65 + rownumbers.rownumber - 1)
ELSE ''
AS <WHATEVER>
,mytable.id
,mytable.descr
FROM (
SELECT l.id, COUNT(*) AS rownumber
FROM mytable AS l
LEFT JOIN mytable AS r
ON l.id >= r.id
GROUP BY l.id
) AS rownumbers
INNER JOIN mytable
ON mytable.id = rownumbers.id
发布于 2009-01-27 17:15:06
在SQL Server2000中,不能在单个select语句中执行此操作。SQL Server 2000没有ROWNUM语句。
如果要使用临时表,可以使用IDENTITY函数。
SELECT IDENTITY(int,1,1) rownum,
id,
descr
INTO #mytable
from mytable
SELECT CASE WHEN ROWNUMBER < 4
THEN CHAR(65 + ROWNUMBER - 1)
ELSE ''
AS <WHATEVER>
,X.id
,X.descr
FROM #mytable X
ORDER BY rownum
注意:标识函数只能在SELECT INTO语句中使用。
https://stackoverflow.com/questions/484165
复制相似问题