34,838
社区成员
发帖
与我相关
我的任务
分享超高难度sql,求各位大虾进来看看！
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'
------------------------
id code uName
1 11 aaa
1 13 bbb
1 15 ccc
2 11 ddd
2 13 eee
2 15 fff
3 13 ggg
3 15 hhh
--------------------------
我想把上面的表的数据转化为如下形式的：
code uName uName uName
11 aaa ddd 无
13 bbb eee ggg
15 ccc fff hhh
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'
------------------------
id code uName
1 11 aaa
1 13 bbb
1 15 ccc
2 11 ddd
2 13 eee
2 15 fff
3 13 ggg
3 15 hhh
--------------------------
我想把上面的表的数据转化为如下形式的：
code uName uName uName
11 aaa ddd 无
13 bbb eee ggg
15 ccc fff hhh
...全文
请发表友善的回复…
发表回复
-狙击手- 2007-12-20
- 打赏
- 举报
死龟
-狙击手- 2007-12-20
- 打赏
- 举报
死龟
orlando1108 2007-12-20
- 打赏
- 举报
潇洒老乌龟,I 服了You,你太强了！！
dawugui 2007-12-16
- 打赏
- 举报
--如果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 行受影响)
*/dawugui 2007-12-16
- 打赏
- 举报
--如果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,tmpdawugui 2007-12-16
- 打赏
- 举报
--加多测试数据后,静态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 tbdawugui 2007-12-16
- 打赏
- 举报
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 tbtim_spac 2007-12-16
- 打赏
- 举报
上面的例子仅限输出5个以内的uName
tim_spac 2007-12-16
- 打赏
- 举报
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
wzy_love_sly 2007-12-16
- 打赏
- 举报
不好做
wzy_love_sly 2007-12-16
- 打赏
- 举报
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) 李四 83 NULL 74
张三 83 93 73
和这个一样吧
orlando1108 2007-12-16
- 打赏
- 举报
谢谢各位的解答，不过我的那个id不是固定的，所以像楼上的那种在where条件中用id=1或id=2等固定的查询方式是不行的。
bqb 2007-12-16
- 打赏
- 举报
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
*/
liangCK 2007-12-16
- 打赏
- 举报
--测试数据
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 邓三
--*/liangCK 2007-12-16
- 打赏
- 举报
转转邹老大的.
查询处理的存储过程
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 邓三 tomyuansir 2007-12-16
- 打赏
- 举报
declare @a table(a1 int,a2 int,a3 varchar(20))
insert into @a
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 * from @a order by a2,a1
select a2,uname1=(select a3 from @a where a2=a.a2 and a1=1),uname2=(select a3 from @a where a2=a.a2 and a1=2),uname3=(select isnull(a3,'无') from @a where a2=a.a2 and a1=3) from @a a group by a2
/*
（所影响的行数为 8 行）
a1 a2 a3
----------- ----------- --------------------
1 11 aaa
2 11 ddd
1 13 bbb
2 13 eee
3 13 ggg
1 15 ccc
2 15 fff
3 15 hhh
（所影响的行数为 8 行）
a2 uname1 uname2 uname3
----------- -------------------- -------------------- --------------------
11 aaa ddd NULL
13 bbb eee ggg
15 ccc fff hhh
（所影响的行数为 3 行）
*/
insert into @a
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 * from @a order by a2,a1
select a2,uname1=(select a3 from @a where a2=a.a2 and a1=1),uname2=(select a3 from @a where a2=a.a2 and a1=2),uname3=(select isnull(a3,'无') from @a where a2=a.a2 and a1=3) from @a a group by a2
/*
（所影响的行数为 8 行）
a1 a2 a3
----------- ----------- --------------------
1 11 aaa
2 11 ddd
1 13 bbb
2 13 eee
3 13 ggg
1 15 ccc
2 15 fff
3 15 hhh
（所影响的行数为 8 行）
a2 uname1 uname2 uname3
----------- -------------------- -------------------- --------------------
11 aaa ddd NULL
13 bbb eee ggg
15 ccc fff hhh
（所影响的行数为 3 行）
*/
tim_spac 2007-12-16
- 打赏
- 举报
Q: uName 允许多少级？如果无级数限制且要表现为table类型的输出则答案为:
:( 无解
:( 无解
