如表A
ID xm
5 '张三'
5 '李四'
5 '王王'
5 '赵六'
要得到如下结果
ID xm
5 '张三'
6 '李四'
7 '王王'
8 '赵六'
各位大侠，请出高招，看看谁的方法最好，谢谢咯！
------解决方案--------------------
row_number()
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-05-20 15:38:45
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([ID] int,[xm] nvarchar(4))
insert [A]
select 5,N'张三' union all
select 5,N'李四' union all
select 5,N'王王' union all
select 5,N'赵六'
--------------生成数据--------------------------
;WITH cte AS
(
SELECT * ,ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP)id2
FROM a
),
cte1 AS
(
SELECT *
FROM cte
WHERE id2=1
UNION ALL
SELECT b.id+1 AS id,a.xm,a.id2
FROM cte a INNER JOIN cte1 b ON a.id2=b.id2+1
)
SELECT id,xm
FROM cte1
----------------结果----------------------------
/*
id xm
----------- ----
5 张三
6 李四
7 王王
8 赵六
*/
------解决方案--------------------
WITH cte AS
(
SELECT * ,ROW_NUMBER() OVER(ORDER BY [ID])id2
FROM a
)
SELECT rn=id+id2-1,xm FROM cte
------解决方案--------------------
只需要查一遍表搞定，楼上的都查了两遍原表
select [A].xm,ROW_NUMBER() over (order by getdate()) +ID-1 as ID
from [A]