现在又数据表结构是这样子的:
id code name
1 A a
2 A a
3 A e
4 B b
5 C c
6 C c
现在想要得到查询结果:
id code name
1 A a
1 A a
2 A e
3 B b
4 C c
4 C c
请问这样的SQL语句怎么写?急求大神回复!
用一条查询语句或更新语句都可以
------解决思路----------------------
sorry
;WITH t (id,code,name)AS
(
SELECT 1, 'A', 'a' UNION ALL
SELECT 2, 'A', 'a' UNION ALL
SELECT 3, 'A', 'e' UNION ALL
SELECT 4, 'B', 'b' UNION ALL
SELECT 5, 'C', 'c' UNION ALL
SELECT 6, 'C', 'c'
)
,t1 AS
(
SELECT code,name,id,1 AS rn FROM t WHERE id=1
UNION ALL
SELECT t.code,t.name,t.id,CASE WHEN t.code=t1.code AND t.name=t1.name
THEN t1.rn ELSE t1.rn+1 END AS rn
FROM t1,t
WHERE t.id=t1.id+1
)
SELECT code,name,rn AS id FROM t1