超高难度sql,求各位大虾进来看看！

--如果code,uname重复,在sql server 2005中可以使用row_number()函数来解决,不需要临时表.



create table tb(id int,code varchar(10),uName varchar(10))

insert   into   tb   

select   1,11,'aaa' 

union   all 

select   1,13,'bbb' 

union   all 

select   1,15,'ccc' 

union   all 

select   2,11,'ddd' 

union   all 

select   2,13,'eee' 

union   all 

select   2,15,'fff' 

union   all 

select   3,13,'ggg' 

union   all 

select   3,15,'hhh' 

union   all 

select   3,15,'iii' 

union   all 

select   3,15,'jjj' 

union   all 

select   3,15,'kkk' 

union   all 

select   3,15,'kkk' 



--动态SQL,指一个CODE的值不确定.

declare @sql varchar(8000)

set @sql = 'select code'

select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then uname else '' '' end) [uname' + cast(px as varchar) + ']'

from (select distinct px from (select px=(select count(1) from (select * , xh = ROW_NUMBER() over(order by code , uname) from tb) m where m.code=n.code and m.xh<n.xh)+1 , * from (select * , xh = ROW_NUMBER() over(order by code , uname) from tb) n) t) as m

set @sql = @sql + ' from (select px=(select count(1) from (select * , xh = ROW_NUMBER() over(order by code , uname) from tb) m where m.code=n.code and m.xh<n.xh)+1 , * from (select * , xh = ROW_NUMBER() over(order by code , uname) from tb) n) t group by code order by code'

exec(@sql) 



drop table tb



/*

code       uname1     uname2     uname3     uname4     uname5     uname6     uname7

---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

11         aaa        ddd                                                     

13         bbb        eee        ggg                                          

15         ccc        fff        hhh        iii        jjj        kkk        kkk



(3 行受影响)

*/

--如果code,uname重复,在sql server 2000中只能用临时表来解决.

create table tb(id int,code varchar(10),uName varchar(10))

insert   into   tb   

select   1,11,'aaa' 

union   all 

select   1,13,'bbb' 

union   all 

select   1,15,'ccc' 

union   all 

select   2,11,'ddd' 

union   all 

select   2,13,'eee' 

union   all 

select   2,15,'fff' 

union   all 

select   3,13,'ggg' 

union   all 

select   3,15,'hhh' 

union   all 

select   3,15,'iii' 

union   all 

select   3,15,'jjj' 

union   all 

select   3,15,'kkk' 

union   all 

select   3,15,'kkk' 

--生成一个临时表,其id1替换uname来判断大小的作用

select id1 = identity(int,1,1) , * into tmp from tb



--动态SQL,指一个CODE的值不确定.

declare @sql varchar(8000)

set @sql = 'select code'

select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then uname else '' '' end) [uname' + cast(px as varchar) + ']'

from (select distinct px from (select px=(select count(1) from tmp where code=a.code and id1<a.id1)+1 , * from tmp a) t) as m

set @sql = @sql + ' from (select px=(select count(1) from tmp where code=a.code and id1<a.id1)+1 , * from tmp a) t group by code order by code'

exec(@sql) 

/*

code       uname1     uname2     uname3     uname4     uname5     uname6     uname7     

---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 

11         aaa        ddd                                                     

13         bbb        eee        ggg                                          

15         ccc        fff        hhh        iii        jjj        kkk        mmm



（所影响的行数为 3 行）

*/



drop table tb,tmp

--加多测试数据后,静态SQL不正确,动态SQL正确.

create table tb(id int,code varchar(10),uName varchar(10))

insert   into   tb   

select   1,11,'aaa' 

union   all 

select   1,13,'bbb' 

union   all 

select   1,15,'ccc' 

union   all 

select   2,11,'ddd' 

union   all 

select   2,13,'eee' 

union   all 

select   2,15,'fff' 

union   all 

select   3,13,'ggg' 

union   all 

select   3,15,'hhh' 

union   all 

select   3,15,'iii' 

union   all 

select   3,15,'jjj' 

union   all 

select   3,15,'kkk' 

union   all 

select   3,15,'mmm' 



--静态SQL,指一个code最多三个值

select code , 

  max(case px when 1 then uname else '' end) 'uname1',

  max(case px when 2 then uname else '' end) 'uname2',

  max(case px when 3 then uname else '' end) 'uname3'

from

(

  select px=(select count(1) from tb where code=a.code and uname<a.uname)+1 , * from tb a

) t

group by code 

order by code

/*

code       uname1     uname2     uname3     

---------- ---------- ---------- ---------- 

11         aaa        ddd        

13         bbb        eee        ggg

15         ccc        fff        hhh



（所影响的行数为 3 行）

*/



--动态SQL,指一个CODE的值不确定.

declare @sql varchar(8000)

set @sql = 'select code'

select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then uname else '' '' end) [uname' + cast(px as varchar) + ']'

from (select distinct px from (select px=(select count(1) from tb where code=a.code and uname<a.uname)+1 , * from tb a) t) as m

set @sql = @sql + ' from (select px=(select count(1) from tb where code=a.code and uname<a.uname)+1 , * from tb a) t group by code order by code'

exec(@sql) 

/*

code       uname1     uname2     uname3     uname4     uname5     uname6     uname7     

---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 

11         aaa        ddd                                                     

13         bbb        eee        ggg                                          

15         ccc        fff        hhh        iii        jjj        kkk        mmm



（所影响的行数为 3 行）

*/



drop table tb

create table tb(id int,code varchar(10),uName varchar(10))

insert   into   tb   

select   1,11,'aaa' 

union   all 

select   1,13,'bbb' 

union   all 

select   1,15,'ccc' 

union   all 

select   2,11,'ddd' 

union   all 

select   2,13,'eee' 

union   all 

select   2,15,'fff' 

union   all 

select   3,13,'ggg' 

union   all 

select   3,15,'hhh' 



--静态SQL,指一个code最多三个值

select code , 

  max(case px when 1 then uname else '' end) 'uname1',

  max(case px when 2 then uname else '' end) 'uname2',

  max(case px when 3 then uname else '' end) 'uname3'

from

(

  select px=(select count(1) from tb where code=a.code and uname<a.uname)+1 , * from tb a

) t

group by code 

order by code

/*

code       uname1     uname2     uname3     

---------- ---------- ---------- ---------- 

11         aaa        ddd        

13         bbb        eee        ggg

15         ccc        fff        hhh



（所影响的行数为 3 行）

*/



--动态SQL,指一个CODE的值不确定.

declare @sql varchar(8000)

set @sql = 'select code'

select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then uname else '' '' end) [uname' + cast(px as varchar) + ']'

from (select distinct px from (select px=(select count(1) from tb where code=a.code and uname<a.uname)+1 , * from tb a) t) as m

set @sql = @sql + ' from (select px=(select count(1) from tb where code=a.code and uname<a.uname)+1 , * from tb a) t group by code order by code'

exec(@sql) 

/*

code       uname1     uname2     uname3     

---------- ---------- ---------- ---------- 

11         aaa        ddd        

13         bbb        eee        ggg

15         ccc        fff        hhh



（所影响的行数为 3 行）

*/



drop table tb

declare @tcode_names (

	code int,

	uName1 varchar(32),

	uName2 varchar(32),

	uName3 varchar(32),

	uName4 varchar(32),

	uName5 varchar(32),

	ids int

)



declare @id int, @code int, @uName varchar(32)

declare @ids int

declare cur_list cursor for

select id, code, uName 

from the_Table

order by id

open cur_list

fetch next from cur_list into @id, @code, @uName

while @@fetch_status!=-1 begin

	if not exists (select 1 from @tcode_names where code = @code)

		insert into @tcode_names (code, uName1, ids) values (@code, @uName, 1)

	else begin

		select @ids = ids from @tcode_names where code = @code

		if @ids=1

			update @tcode_names set uName2=@uName, ids=2 from @tcode_names where code = @code

		else if @ids=2

			update @tcode_names set uName3=@uName, ids=3 from @tcode_names where code = @code

		else if @ids=3

			update @tcode_names set uName4=@uName, ids=4 from @tcode_names where code = @code

		else if @ids=4

			update @tcode_names set uName5=@uName, ids=5 from @tcode_names where code = @code

		else 

			raise 'uName Level overflow.'

	end

fetch next from cur_list into @id, @code, @uName

end

close cur_list

deallocate cur_list



-- 输出

select * from @tcode_names

create table #t

(

   Name    varchar(10) ,

   Subject varchar(10) ,

   Result  int

)



insert into #t(Name , Subject , Result) values('张三','语文','73')

insert into #t(Name , Subject , Result) values('张三','数学','83')

insert into #t(Name , Subject , Result) values('张三','物理','93')

insert into #t(Name , Subject , Result) values('李四','语文','74')

insert into #t(Name , Subject , Result) values('李四','数学','83')







declare @sql varchar(8000)

set @sql = 'select Name as ' + '姓名'

select @sql = @sql + ' , sum(case Subject when ''' + Subject + ''' then Result end) [' + Subject + ']'

from (select distinct Subject from #t) as a

set @sql = @sql + ' from #t group by name'

print @sql

exec(@sql) 

declare @t table (id  int,   code   int,  uNameinsert  varchar(20))

insert into @t   

select   1 ,11,'aaa' 

union   all 

select   1,13,'bbb' 

union   all 

select   1,15,'ccc' 

union   all 

select   2,11,'ddd' 

union   all 

select   2,13,'eee' 

union   all 

select   2,15,'fff' 

union   all 

select   3,13,'ggg' 

union   all 

select   3,15,'hhh' 



 



select t0.code,isnull(t1.uNameinsert,'无') as   uName ,isnull(t2.uNameinsert,'无')  as   uName,isnull(t3.uNameinsert,'无')  as   uName

from      (select distinct code from @t) t0

left join (select * from @t where id=1) t1 on t0.code=t1.code

left join (select * from @t where id=2) t2 on t0.code=t2.code

left join (select * from @t where id=3) t3 on t0.code=t3.code

 



--結果：

/*

code     uName     uName     uName 

-------------------------------------------------

11	aaa	ddd	无

13	bbb	eee	ggg

15	ccc	fff	hhh

*/

 

--测试数据

create table test (TEST_NO varchar(10),TEST_NAME varchar(10))

insert into test select 'A0001','张三'

insert into test select 'A0003','李４'

insert into test select 'A0004','李五'

insert into test select 'B0002','王五'

insert into test select 'B0003','将李'

insert into test select 'B0004','五二'

insert into test select 'C0001','王二'

insert into test select 'C0003','邓三'

insert into test select 'C0004','刘４'

go



--查询处理的存储过程

CREATE PROC p_PageView

@tbname sysname,   --查询的表名

@KeyField sysname, --关键字段名

@SortField sysname,--排序字段名

@Columns int=10,   --每页最多显示的列数

@Rows int=10       --每页最多显示的行数

as

set nocount on

declare @s nvarchar(4000),@gid1 varchar(20),@gid2 varchar(20),@pageno varchar(20)

select @s='',

	@pageno='1+gid/'+rtrim(@Columns*@rows),

	@gid1='(gid%'+rtrim(@Columns*@rows)+')/'+rtrim(@rows),

	@gid2='(gid%'+rtrim(@Columns*@rows)+')%'+rtrim(@rows)

while @Columns>0

	select @Columns=@Columns-1,

		@s='

,isnull(max(case '+@gid1+' when '+rtrim(@Columns)+' then TEST_NO end),'''')'

		+'

,isnull(max(case '+@gid1+' when '+rtrim(@Columns)+' then TEST_NAME end),'''')'

		+@s

exec('select PageNo='+@pageno+@s+' from(

	select TEST_NO,TEST_NAME,

		gid=(select count(*) from test where TEST_NO<a.TEST_NO)

	from test a)aa

	group by '+@pageno+','+@gid2+'

	order by '+@pageno+','+@gid2)

go



--调用

exec p_PageView 'test','TEST_NO','TEST_NAME',3,2

go



--删除测试

drop table test

drop proc p_PageView



/*--结果

                                                                  

---------- ---------- ---------- ---------- ---------- ---------- 

A0001      张三         B0002      王五         C0001      王二

A0003      李４         B0003      将李         C0003      邓三

A0004      李五         B0004      五二         C0004      刘４





                                                                  

---------- ---------- ---------- ---------- ---------- ---------- 

A0001      张三         B0003      将李         C0004      刘４

A0003      李４         B0004      五二                    

A0004      李五         C0001      王二                    

B0002      王五         C0003      邓三    

--*/

查询处理的存储过程

CREATE PROC p_PageView

@tbname sysname,   --查询的表名

@KeyField sysname, --关键字段名

@SortField sysname,--排序字段名

@Columns int=10,   --每页最多显示的列数

@Rows int=10       --每页最多显示的行数

as

set nocount on

declare @s nvarchar(4000),@gid1 varchar(20),@gid2 varchar(20),@pageno varchar(20)

select @s='',

	@pageno='1+gid/'+rtrim(@Columns*@rows),

	@gid1='(gid%'+rtrim(@Columns*@rows)+')/'+rtrim(@rows),

	@gid2='(gid%'+rtrim(@Columns*@rows)+')%'+rtrim(@rows)

while @Columns>0

	select @Columns=@Columns-1,

		@s='

,isnull(max(case '+@gid1+' when '+rtrim(@Columns)+' then TEST_NO end),'''')'

		+'

,isnull(max(case '+@gid1+' when '+rtrim(@Columns)+' then TEST_NAME end),'''')'

		+@s

exec('select PageNo='+@pageno+@s+' from(

	select TEST_NO,TEST_NAME,

		gid=(select count(*) from test where TEST_NO<a.TEST_NO)

	from test a)aa

	group by '+@pageno+','+@gid2+'

	order by '+@pageno+','+@gid2)

go



--调用

exec p_PageView 'test','TEST_NO','TEST_NAME',3,2

go



--删除测试

drop table test

drop proc p_PageView



/*--结果

                                                                  

---------- ---------- ---------- ---------- ---------- ---------- 

A0001      张三         B0002      王五         C0001      王二

A0003      李４         B0003      将李         C0003      邓三

A0004      李五         B0004      五二         C0004      刘４





                                                                  

---------- ---------- ---------- ---------- ---------- ---------- 

A0001      张三         B0003      将李         C0004      刘４

A0003      李４         B0004      五二                    

A0004      李五         C0001      王二                    

B0002      王五         C0003      邓三