declare @var int,@a int
set @var=(select count(1) from 表1)
set @a=0
while @a<=@var
begin
insert 表2
select * from (select (identity(int,1,1)) id,fname from 表1) testtable
where id>@a and id<=@a
set @a=@a+3
end
set @var=(select count(1) from 表1)
set @a=0
while @a<=@var
begin
insert 表2
select * from (select (identity(int,1,1)) id,fname from 表1) testtable
where id>@a and id<=@a
set @a=@a+3
end
解决方案 »
- SQL 查询问题
- 服务器上有sql 2005,在xp上怎么才能有类似sql2000企业管理器的东西。
- 数据库死锁--自己把自己锁住。
- 如何将一个数据库的查询结果插入到另外一个数据中?
- 求解,怎么拼接SQL语句,提示转换成数据类型 int 时失败
- rs.RecordCount 为-1????
- 求教:TEMPDB库频繁出错的问题.请大家帮忙看下~
- 请问如何对一个SQL Server的数据库进行加密?对于加密之后的数据库,数据库管理员能否查看函数及存储过程的源代码?谢谢!
- 数据导入/导出,在最后点"完成"时,系统没反映了
- sql server 2008中项目运行但是master的链接数不断递增
- 视图的问题---很急的问题!!!
- 如何把以下结果集分组,请各位高手赐教!(急)
CREATE TABLE [T] (
[id] [int] NULL ,
[name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GODECLARE @counter smallint
SET @counter = 0
WHILE @counter < 100
BEGIN
insert T(id,name)
values(@counter,'aaa'+convert(varchar(10),@counter))
SET NOCOUNT ON
SET @counter = @counter + 1
SET NOCOUNT OFF
END
GOselect * from Tselect name1=
isnull((case when id%3=0 then name
end ),''),
name2=
isnull((case when id%3=1 then name
end ),''),
name3=
isnull((case when id%3=2 then name
end ),'')
from tdrop table T--有点缺陷,运行自己看效果
CREATE TABLE [T] (
[id] [int] NULL ,
[name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GODECLARE @counter smallint
SET @counter = 0
WHILE @counter < 100
BEGIN
insert T(id,name)
values(@counter,'aaa'+convert(varchar(10),@counter))
SET NOCOUNT ON
SET @counter = @counter + 1
SET NOCOUNT OFF
END
GOselect * from Tselect name1=
isnull((case when id%3=0 then name
end ),''),
name2=
isnull((case when id%3=1 then name
end ),''),
name3=
isnull((case when id%3=2 then name
end ),'')
from tdrop table T--看结果,有点缺陷
declare @fdname2 varchar(20) --列名变量2
declare @fdname3 varchar(20) --列名变量3
declare @cur_rowcount int --列数
declare @rowcount int --游标行数
declare @sql nvarchar(1000)
set @fdname1 = ''
set @fdname2 = ''
set @fdname3 = ''
set @cur_rowcount = 0
select @rowcount = count(*) from 表1delete from 表2
--定义生成统计数据游标declare col_cursor cursor
for
select fname from 表1
OPEN col_cursor -- 打开游标
while @rowcount > 0
BEGIN
set @cur_rowcount = @cur_rowcount + 1
if @cur_rowcount = 1
begin
FETCH NEXT FROM col_cursor into @fdname1 --将值赋给列名变量1
end if @cur_rowcount = 2
begin
FETCH NEXT FROM col_cursor into @fdname2 --将值赋给列名变量2
end if @cur_rowcount = 3
begin
FETCH NEXT FROM col_cursor into @fdname3 --将值赋给列名变量3
set @sql = 'insert into 表2 values('''+@fdname1+''','''+ @fdname2+''','''+@fdname3+''')'
exec(@sql)
set @cur_rowcount = 0
set @fdname1 = ''
set @fdname2 = ''
set @fdname3 = ''
end set @rowcount = @rowcount - 1 if @rowcount = 0 --处理不够三位的
begin
set @sql = 'insert into 表2 values('''+@fdname1+''','''+ @fdname2+''','''+@fdname3+''')'
exec(@sql)
end END
CLOSE col_cursor --关闭游标
DEALLOCATE col_cursor --释放游标select * from 表2 --输出结果(已测试通过)
我記得以前鄒建大哥寫過一段SQL語句﹐很簡單的﹐一下子就完成任務
現在忘掉了 ﹐呵呵﹐自己苯﹐希望大家再幫幫忙
create table tb( O char(1),X varchar(10),Y varchar(10),Z varchar(10))
insert tb select 'A','x1','y1','z1'
union all select 'B','x2','y2','z2'
union all select 'C','x3','y3','z3'
go
declare @s1 varchar(8000),@s2 varchar(8000)
,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'=''O='''''+name+''''''''
,@s3=@s3+'
,@'+@i+'=@'+@i+'+'',[''+cast([O] as varchar)+'']=''''''+cast(['+name+'] as varchar)+'''''''''
,@s4=@s4+',@'+@i+'=''select ''+@'+@i
,@s5=@s5+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id('tb')=id and colid>1
order by colid
select @s1=stuff(@s1,1,1,'')
,@s2=stuff(@s2,1,1,'')
,@s3=stuff(@s3,1,5,'')
,@s4=stuff(@s4,1,1,'')
,@s5=stuff(@s5,1,15,'')
exec('declare '+@s1+'
select '+@s2+'
select '+@s3+'
from tb
select '+@s4+'
exec('+@s5+')')
go
--删除测试
drop table tb /*--结果
O A B C
---- ---- ---- ----
X x1 x2 x3
Y y1 y2 y3
Z z1 z2 z3(所影响的行数为 3 行)
--*/
cosio()﹐你的方法好復雜啊﹐跟游標實現差不多哦
我覺得aw511(星星點燈)的思路不錯﹐那樣執行速度也會很快的﹐我也做到那一步了
就是不知道問題出在哪里
select gid=0,fname
into #t from 表1
order by fname
declare @i int, @fname varchar(10)
select @i=0
update #t set @i=(case @fname when fname then @i+1 else @i+1 end), gid=@i, @fname=fnameselect
name1=max(case gid%5 when 1 then fname else '' end)
,name2=max(case gid%5 when 2 then fname else '' end)
,name3=max(case gid%5 when 3 then fname else '' end)
from #t
group by gid/3