在不生成列的顺序号前提下对无序列出现的首行进行标注
刚才在微软中文论坛逛的时候看到一个题目，其实方法很多，能不能在不生成列的顺序号（如排名函数，生成唯一编号。。。）前提下对无序列出现的首行进行标注呢，
数据准备：
表TA（myname varchar(10),nameId varchar(10)）
MyName nameId
aaa
aaa
aaa
aaa
bbb
bb
ccc
ccc
bb
要求结果如下：
MyName NameID
aaa new
aaa old
aaa old
aaa old
bbb new
bb new
ccc new
ccc old
bb old
------------------------------------
-- AUTHOR: FLYSTONE
-- VERSION:V1.001
-- DATE:2009-11-05 22:50
------------------------------------
-- TEST DATA: TA
IF OBJECT_ID('TA') IS NOT NULL
DROP TABLE TA
GO
CREATE TABLE TA(MYNAME NVARCHAR(10))
GO
INSERT INTO TA
SELECT 'AAA' UNION ALL
SELECT 'AAA' UNION ALL
SELECT 'AAA' UNION ALL
SELECT 'AAA' UNION ALL
SELECT 'BBB' UNION ALL
SELECT 'BB' UNION ALL
SELECT 'CCC' UNION ALL
SELECT 'CCC' UNION ALL
SELECT 'BB'
GO
--START
ALTER TABLE TA ADD NAMEID VARCHAR(10)
GO
DECLARE @S VARCHAR(10),@S1 VARCHAR(8000)
SET @S1 = '' --这一句不能少，否则狗儿屁
UPDATE A
SET NAMEID = CASE WHEN CHARINDEX( ','+MYNAME +',',','+@S1+',') > 0 THEN 'OLD'
ELSE 'NEW'
END ,
@S1 = CASE WHEN CHARINDEX( ','+CAST(@S+'' AS VARCHAR(10)) +',',','+@S1+',') > 0 THEN @S1
ELSE @S1+','+ISNULL(CAST(@S+'' AS VARCHAR(10)),'')
END ,
@S = MYNAME
FROM TA A
SELECT * FROM TA
--RESULT:
/*
MYNAME NAMEID
---------- ----------
AAA NEW
AAA OLD
AAA OLD
AAA OLD
BBB NEW
BB NEW
CCC NEW
CCC OLD
BB OLD
*/
--END