1 123
2 123
3 456
4 456
5 789
6 789
改成
1 123-1
2 123-2
3 456-1
4 456-2
5 789-1
6 789-2
7 789-3
的sql 陳述句是什么？
uj5u.com熱心網友回復：
CREATE TABLE #T
(
id INT,
val VARCHAR(100)
)
INSERT INTO #T VALUES(1, 123)
INSERT INTO #T VALUES(2, 123)
INSERT INTO #T VALUES(3, 456)
INSERT INTO #T VALUES(4, 456)
INSERT INTO #T VALUES(5, 789)
INSERT INTO #T VALUES(6, 789)
--方法一
;WITH ct
AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY val ORDER BY id) AS x FROM #T
)
UPDATE ct SET val=CONCAT(val,'-',x)
--方法二
UPDATE #T SET val=CONCAT(val,'-',(SELECT COUNT(1) FROM #T a WHERE val=#T.val AND id<=#T.id ))
DROP TABLE #T
uj5u.com熱心網友回復：
create table #t(a int,b varchar(10))
insert into #t(a,b)
select 1,'123' union all
select 2,'123' union all
select 3,'456' union all
select 4,'456' union all
select 5,'789' union all
select 6,'789' union all
select 7,'789'
update t
set b=b+'-'+rtrim((select count(1)
from #t u
where u.b=t.b
and u.a<=t.a))
from #t t
select * from #t
/*
a b
----------- ----------
1 123-1
2 123-2
3 456-1
4 456-2
5 789-1
6 789-2
7 789-3
(7 行受影響)
*/
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/250990.html
標籤：應用實例
上一篇：小白