求SQL:一个字段的值按条件重新排列序号。

[jaychensusu]

--建表
CREATE TABLE [dbo].[test](
[mxid] [bigint] IDENTITY(1,1) NOT NULL,
[mon] [varchar](2) COLLATE Chinese_PRC_CI_AS NULL,
[type] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[typehao] [int] NULL
) ON [PRIMARY]


--测试数据

insert into test(mon,type,typehao) values('01','收',1)
insert into test(mon,type,typehao) values('01','收',2)
insert into test(mon,type,typehao) values('01','收',4)
insert into test(mon,type,typehao) values('01','收',9)

insert into test(mon,type,typehao) values('02','付',2)
insert into test(mon,type,typehao) values('02','付',3)
insert into test(mon,type,typehao) values('02','付',6)
insert into test(mon,type,typehao) values('02','付',8)

--实现以下要求，typehao字段update为从1开始重新排列序号：

mxid mon type typehao
1 01 收 1
2 01 收 2
3 01 收 3
4 01 收 4

5 02 付 1
6 02 付 2
7 02 付 3
8 02 付 4

[liangCK]

update a set
    typehao=b.typehao
from test as a
    join (select mxid,typehao=(select count(*) from test
                               where a.mon=mon and mxid<=a.mxid)
           from test as a) as b
on a.mxid=b.mxid

[「已注销」]

----2005以上可用
SELECT mxid,mon,type,ROW_NUMBER()OVER(PARTITION BY mon ORDER BY GETDATE())typehao
FROM #test

mxid                 mon  type                 typehao
-------------------- ---- -------------------- --------------------
1                    01   收                    1
2                    01   收                    2
3                    01   收                    3
4                    01   收                    4
5                    02   付                    1
6                    02   付                    2
7                    02   付                    3
8                    02   付                    4

(8 row(s) affected)

[abuying]

select mxid,mon,[type],typehao=ROW_NUMBER()over(partition by type order by mxid asc) from [test]
mxid                 mon  type                 typehao
-------------------- ---- -------------------- --------------------
5                    02   付                    1
6                    02   付                    2
7                    02   付                    3
8                    02   付                    4
1                    01   收                    1
2                    01   收                    2
3                    01   收                    3
4                    01   收                    4

(8 行受影响)

[abuying]

--sql 2000
select mxid,mon,[type],typehao=(select COUNT(1)+1 from [test] a where a.[type]=[test].[type] and a.mxid<[test].mxid) from [test]

mxid                 mon  type                 typehao
-------------------- ---- -------------------- --------------------
5                    02   付                    1
6                    02   付                    2
7                    02   付                    3
8                    02   付                    4
1                    01   收                    1
2                    01   收                    2
3                    01   收                    3
4                    01   收                    4

(8 行受影响)

[「已注销」]

UPDATE T
SET typehao= M.typehao
FROM #TEST T
LEFT JOIN
(
	SELECT mxid,mon,type,ROW_NUMBER()OVER(PARTITION BY mon ORDER BY GETDATE())typehao
	FROM #test
)M
ON T.MXID=M.mxid

SELECT * FROM #TEST

mxid                 mon  type                 typehao
-------------------- ---- -------------------- -----------
1                    01   收                    1
2                    01   收                    2
3                    01   收                    3
4                    01   收                    4
5                    02   付                    1
6                    02   付                    2
7                    02   付                    3
8                    02   付                    4

(8 row(s) affected)

[Barton]

[Quote=引用 5 楼 zsh0809 的回复:]
SQL code
UPDATE T
SET typehao= M.typehao
FROM #TEST T
LEFT JOIN
(
SELECT mxid,mon,type,ROW_NUMBER()OVER(PARTITION BY mon ORDER BY GETDATE())typehao
FROM #test
)M
ON T.MXID=M.mxid

S……
[/Quote]


+1