Create Table #Test
(
A varchar(20),
B int
)
Insert Into #Test
Select 'aaa',null Union All
Select 'aaa',null Union All
Select 'aaa',null Union All
Select 'aaa',null Union All
Select 'bbb',null Union All
Select 'bbb',null Union All
Select 'bbb',null Union All
Select 'ccc',null如何更新B列,出以下结果,谢谢!
Select * From #Testaaa 1
aaa 2
aaa 3
aaa 4
bbb 1
bbb 2
bbb 3
ccc 1
(
A varchar(20),
B int
)
Insert Into #Test
Select 'aaa',null Union All
Select 'aaa',null Union All
Select 'aaa',null Union All
Select 'aaa',null Union All
Select 'bbb',null Union All
Select 'bbb',null Union All
Select 'bbb',null Union All
Select 'ccc',null如何更新B列,出以下结果,谢谢!
Select * From #Testaaa 1
aaa 2
aaa 3
aaa 4
bbb 1
bbb 2
bbb 3
ccc 1
SELECT
A,
B=ROW_NUMBER() OVER(PARTITION BY A ORDER BY A)
FROM #Test
A,
B=(SELECT COUNT(*)
FROM #Test
WHERE A.A=A
AND flag<=A.flag)
FROM #Test AS AALTER TABLE #Test DROP COLUMN flag
ROW_NUMBER() OVER(PARTTION BY A)
(
SELECT
B,ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS FUCKME
FROM #Test
)
UPDATE FUCK SET B=FUCKME