求一条SQL语句？

[pyxyu]

Tab1表内容如下：　
ID Name
1 N1
2 N2
3 N3
4 N4
5 N5
6 N6
7 N7
8 N8
得到的结果如下：
ID Name 或 ID Name
1 N1 1
2 N2 2
3 N3 3
4 N4 4 N4
5 N5 5 N5
6 N1 6 N1
7 N2 7 N2
8 N3 8 N3

就是把表中前三条记录移到后三条，前三条内容不变或删除都行。
注：ID是关键字不一定是有序的，上边的表只是模拟的，执行这样操作表中至少有>=6条记录
记得要用一条语句哦！

[wgzaaa]

不至于没有排序字段吧，那就用指针吧

[中国风]

declare @ta table(ID int, Name varchar(2))
insert @ta select 1, 'N1'
insert @ta select 2, 'N2'
insert @ta select 3, 'N3'
insert @ta select 4, 'N4'
insert @ta select 5, 'N5'
insert @ta select 6, 'N6'
insert @ta select 7, 'N7'
insert @ta select 8, 'N8'

select *,num='N'+rtrim((stuff(name,1,1,'')-1)%5+1) from @ta

ID Name num
----------- ---- -------------
1 N1 N1
2 N2 N2
3 N3 N3
4 N4 N4
5 N5 N5
6 N6 N1
7 N7 N2
8 N8 N3

(8 行受影响)

[中国风]

declare @ta table(ID int, Name varchar(2))
insert @ta select 1, 'N1'
insert @ta select 2, 'N2'
insert @ta select 3, 'N3'
insert @ta select 4, 'N4'
insert @ta select 5, 'N5'
insert @ta select 6, 'N6'
insert @ta select 7, 'N7'
insert @ta select 8, 'N8'

select id,Name='N'+rtrim((stuff(name,1,1,'')-1)%5+1) from @ta

id Name
----------- -------------
1 N1
2 N2
3 N3
4 N4
5 N5
6 N1
7 N2
8 N3

(8 行受影响)

[中国风]

declare @ta table(ID int, Name varchar(2))
insert @ta select 1, 'N1'
insert @ta select 2, 'N2'
insert @ta select 3, 'N3'
insert @ta select 4, 'N4'
insert @ta select 5, 'N5'
insert @ta select 6, 'N6'
insert @ta select 7, 'N7'
insert @ta select 8, 'N8'

select id,Name=case when id!>3 and name='N'+rtrim((stuff(name,1,1,'')-1)%5+1)
then '' else 'N'+rtrim((stuff(name,1,1,'')-1)%5+1) end
from @ta

(1 行受影响)
id Name
----------- -------------
1
2
3
4 N4
5 N5
6 N1
7 N2
8 N3

(8 行受影响

[paoluo]

Create Table Tab1
(ID Int,
Name Varchar(100))
Insert Tab1 Select 1, 'N1'
Union All Select 2, 'N2'
Union All Select 3, 'N3'
Union All Select 4, 'N4'
Union All Select 5, 'N5'
Union All Select 6, 'N6'
Union All Select 7, 'N7'
Union All Select 8, 'N8'
GO
Select
T1.ID,
(Case When CountID - T1.AscID < 3 Then T2.Name Else T1.Name End) As Name
From
(
Select
*,
(Select Count(ID) From Tab1 Where ID <= A.ID) As AscID,
(Select Count(ID) From Tab1) As CountID
From
Tab1 A
) T1
Left Join
(
Select
*,
(Select Count(ID) From Tab1 Where ID <= A.ID) As AscID
From
Tab1 A
) T2
On T1.AscID + 3 -T1.CountID = T2.AscID
GO
Drop Table Tab1
--Result
/*
ID Name
1 N1
2 N2
3 N3
4 N4
5 N5
6 N1
7 N2
8 N3
*/

[paoluo]

roy_88(中国风_燃烧你的激情！！！) , 樓主有強調“不一定是有序的”，你還是使用了ID來做判斷，另外是“%5”，明顯是已知表中數據的條數的情況下的，所以你寫的只是針對樓主的貼出來的數據是實現了，加上一條數據得到的結果都會有問題的。

[paoluo]

--另外一種結果


Create Table Tab1
(ID Int,
Name Varchar(100))
Insert Tab1 Select 1, 'N1'
Union All Select 2, 'N2'
Union All Select 3, 'N3'
Union All Select 4, 'N4'
Union All Select 5, 'N5'
Union All Select 6, 'N6'
Union All Select 7, 'N7'
Union All Select 8, 'N8'
GO
Select
T1.ID,
(Case When CountID - T1.AscID < 3 Then T2.Name
When T1.AscID <= 3 Then ''
Else T1.Name End) As Name
From
(
Select
*,
(Select Count(ID) From Tab1 Where ID <= A.ID) As AscID,
(Select Count(ID) From Tab1) As CountID
From
Tab1 A
) T1
Left Join
(
Select
*,
(Select Count(ID) From Tab1 Where ID <= A.ID) As AscID
From
Tab1 A
) T2
On T1.AscID + 3 -T1.CountID = T2.AscID
GO
Drop Table Tab1
--Result
/*
ID Name
1
2
3
4 N4
5 N5
6 N1
7 N2
8 N3
*/

[wgzaaa]

写的很好，可是达不到他要的效果我用数据验证了

[paoluo]

哪個達不到效果？我寫的兩個都實現了。

[wgzaaa]

ID是关键字不一定是有序的　我想后面的NXX也不应该用来排序吧

[wgzaaa]

以下是根据楼主的意思作了变化，上面是原表，下面是你执行的结果
1 N1
6 N2
5 N3
4 N4
3 N5
2 N6
7 N7
8 N8


1
6 N1
5 N3
4 N4
3
2
7 N6
8 N5

[中国风]

鱼兄，偶是用的Name取值啊~~~~~~~~~

[paoluo]

roy_88, 我覺得實際的Name應該不會是N1，N2這樣的數據，你這麼寫有點投機了，不過看樓主的需求了，說不定滿足他的意思。

[paoluo]

wgzaaa() ( ) 信誉：100 Blog 加为好友 2007-04-16 17:35:18 得分: 0


ID是关键字不一定是有序的　我想后面的NXX也不应该用来排序吧


-----------------
不知道樓主的“不一定是有序”是指不一定連續還是順序會被打亂，我理解成第一種了。

先不改了，看樓主的意思。

如果數據的順序是打亂的，一條語句應該是做不了的，至少要借用臨時表。

[wgzaaa]

鱼：你写得不错，你说的可能性大

[中国风]

create table ta(ID int, Name varchar(2))
insert ta select 1, 'N1'
insert ta select 2, 'N2'
insert ta select 3, 'N3'
insert ta select 4, 'N4'
insert ta select 5, 'N5'
insert ta select 6, 'N6'
insert ta select 7, 'N7'
insert ta select 8, 'N8'

--改改

select id,
[name]=case when id in(select top 3 id from ta ) then ''
when id=(select top 1 id from ta where id not in
(select top (select count(1)-3 from ta) id from ta))
then
(select top 1 Name from ta)
when
id =(select top 1 id from ta where id not in
(select top (select count(1)-2 from ta) id from ta))
then
(select top 1 name from ta where id not in (select top 1 id from ta))
when
id =(select top 1 id from ta where id not in
(select top (select count(1)-1 from ta) id from ta))
then
(select top 1 name from ta where id not in(select top 2 id from ta))
else
Name
end
from ta a


id name
----------- ----
1
2
3
4 N4
5 N5
6 N1
7 N2
8 N3

(8 行受影响)

[中国风]

以上是根据id为主键的情况取值

[pyxyu]

声明：Name字段里的内容是随意的！
这是我应聘时的一道测试题，人家给提示说：把Tab1表当作两个表来用，用其中一个表的前三条记录去更新另一表的后三条记录！

[中国风]

声明：Name字段里的内容是随意的！
与排序没关系，语句没有指定order by
只要是主键就OK了