今天的一道面试题？sql语句如何写

Question

表信息
id  words       rank
1   hello       1
1   everybody   2
2   i           1
2   am          2
2   programmer  3结果：
1    hello everybody
2    i am programmersql语句如何写？

Answer 1

http://community.csdn.net/Expert/topic/4951/4951037.xml?temp=.4305231
好象我也问过

Answer 2

--带符号合并行列转换--有表t，其数据如下：
  a b
  1 1
  1 2
  1 3
  2 1
  2 2
  3 1
--如何转换成如下结果:
  a b
  1 1,2,3
  2 1,2
  3 1 create table tb
(
   a int,
   b int
)
insert into tb(a,b) values(1,1)
insert into tb(a,b) values(1,2)
insert into tb(a,b) values(1,3)
insert into tb(a,b) values(2,1)
insert into tb(a,b) values(2,2)
insert into tb(a,b) values(3,1)
goif object_id('pubs..f_hb') is not null
   drop function f_hb
go--创建一个合并的函数
create function f_hb(@a int)
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(b as varchar) from tb where a = @a 
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：
select distinct a ,dbo.f_hb(a) as b from tbdrop table tb--结果
a           b     
----------- ------
1           1,2,3
2           1,2
3           1（所影响的行数为 3 行）
多个前列的合并
数据的原始状态如下：
ID  PR   CON  OP    SC 
001 p    c    差    6
001 p    c    好    2
001 p    c    一般  4
002 w    e    差    8
002 w    e    好    7
002 w    e    一般  1
===========================
用SQL语句实现，变成如下的数据
ID  PR   CON  OPS
001 p    c    差(6),好(2),一般(4)
002 w    e    差(8),好(7),一般(1)if object_id('pubs..tb') is not null
   drop table tb
gocreate table tb
(
id varchar(10),
pr varchar(10),
con varchar(10),
op varchar(10),
sc int
)
 
insert into tb(ID,PR,CON,OP,SC) values('001', 'p',    'c',    '差',    6)
insert into tb(ID,PR,CON,OP,SC) values('001', 'p',    'c',    '好',    2)
insert into tb(ID,PR,CON,OP,SC) values('001', 'p',    'c',    '一般',  4)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w',    'e',    '差',    8)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w',    'e',    '好',    7)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w',    'e',    '一般',  1)
goif object_id('pubs..test') is not null
   drop table test
go
select ID,PR,CON , OPS = op + '(' + cast(sc as varchar(10)) + ')' into test from tb--创建一个合并的函数
if object_id('pubs..f_hb') is not null
   drop function f_hb
go
create function f_hb(@id varchar(10),@pr varchar(10),@con varchar(10))
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(OPS as varchar) from test where id = @id and @pr = pr and @con = con
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：
select distinct id ,pr , con , dbo.f_hb(id,pr,con) as OPS from testdrop table tb
drop table test--结果
id         pr         con        OPS                
---------- ---------- ---------- -------------------
001        p          c          差(6),好(2),一般(4)
002        w          e          差(8),好(7),一般(1)（所影响的行数为 2 行）create table b
(col varchar(20))insert b values ('a')
insert b values ('b')
insert b values ('c')
insert b values ('d')
insert b values ('e')
declare @sql varchar(1024)
set @sql=''
select @sql=@sql+b.col+',' from (select col from b) as b
set @sql='select '''+@sql+''''
exec(@sql)

Answer 3

create table tb(id int,words varchar(100),rank int)
insert tb 
select 1,   'hello',       1
union all select 1,   'everybody',   2
union all select 2,   'i',           1
union all select 2,   'am',          2
union all select 2,   'programmer',  3
create function c(@id int)
returns varchar(100)
as
begin
declare @c varchar(100)
set @c=''
select @c=@c+words+' ' from tb where id=@id order by rank
return @c
endgo
select  distinct id,dbo.c(id) from tb/*结果
1 hello everybody 
2 i am programmer 
*/

Answer 4

if object_id('pubs..tb') is not null
   drop table tb
gocreate table tb(id varchar(10),words varchar(20),rank int)
insert into tb(id,words,rank) values('1',   'hello'     ,  1)
insert into tb(id,words,rank) values('1',   'everybody' ,  2)
insert into tb(id,words,rank) values('2',   'i'         ,  1)
insert into tb(id,words,rank) values('2',   'am'        ,  2)
insert into tb(id,words,rank) values('2',   'programmer',  3)
goif object_id('pubs..f_hb') is not null
   drop function f_hb
go--创建一个合并的函数
create function f_hb(@id varchar(10))
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ' ' + cast(words as varchar) from tb where id = @id
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：
select distinct id ,dbo.f_hb(id) as words from tbdrop table tb/*
id         words          
---------- ---------------
1          hello everybody
2          i am programmer（所影响的行数为 2 行）*/

Answer 5

create table  t1(id int ,word varchar(20),rank int )insert into t1 values(1,'hello',1)
insert into t1 values(1,'everybody',2)
insert into t1 values(2,' i',1)
insert into t1 values(2,'am ',2)
insert into t1 values(2,'programmer  ',3)select * from t1drop function ff
create function ff(@fid int )
  returns  varchar(50)as
begin 
declare @ret varchar(20)
set @ret=''  select @ret=@ret+' ' + word from t1 where id=@fid  order by  rank
set  @ret=stuff(@ret,1,1,'')return @ret
endselect  min(id) ,结果=dbo.ff(min(id)) from t1  group by id order by id

Answer 6

无列名         结果
1  hello everybody
2  i am  programmer  
（所影响的行数为 2 行）

Answer 7

create table NewT02(id int, words varchar(50), rank int)
insert into NewT02 values(1, 'hello', 1)
insert into NewT02 values(1, 'everybody', 2)
insert into NewT02 values(2, 'i', 1)
insert into NewT02 values(2, 'am', 2)
insert into NewT02 values(2, 'programmer', 3)declare @id int, @str varchar(50), @words varchar(50)
declare @temp table(id int, str varchar(50))
declare xx cursor 
for select distinct id from NewT02 
open xx
fetch xx into @id
while @@fetch_status=0
begin
select @str='', @words=''
declare yy cursor 
for select words from NewT02 where id=@id order by rank
open yy
fetch yy into @words
while @@fetch_status=0
begin
select @str=@str+' '+@words
fetch yy into @words
end
close yy
deallocate yy
insert into @temp(id, str)
values(@id, @str)
fetch xx into @id 
end
close xx
deallocate xx
select * from @temp

Answer 8

create function c(@id int)
returns varchar(100)
as
begin
declare @c varchar(100)
set @c=''
select @c=@c+words+' ' from one where id=@id order by rank
return @c
end
这是一种方法，而另一种方法是利用游标。如同楼上select distinct id ,dbo.c(id) from one

Answer 9

declare ee cursor for select id,words from aa
declare @sn smallint,@sn1 smallint,@groups varchar(50),@groups1 varchar(50)create table #aa
(
id smallint,
words varchar(20)
)
open ee
fetch  from ee into @sn,@groups
set @sn1=@sn   
set @groups1=''
while (@@fetch_status=0)
begin
if (@sn=@sn1)
begin
set @groups1=@groups1+space(2)+ @groups+ space(2)
endelse     
begin
insert into #aa (id,words) values (@sn1,@groups1)
set @sn1=@sn
set @groups1=@groups
endfetch  from ee into @sn,@groups
end
insert into #aa (id,words) values (@sn1,@groups1) --础aaい程兵snの癸莱才﹃ぇ㎝
select * from #aa
drop table #aa
close eedeallocate ee
drop table aaid     words                
------ -------------------- 
1        hello    everybody
2      i  am    programmer （所影响的行数为 2 行）

Answer 10

create table t1 (id int, words varchar(10), rank int )
insert into t1 values(1,   'hello',       1)
insert into t1 values(1,   'everybody'  , 2)
insert into t1 values(2,   'i'           ,1)
insert into t1 values(2,   'am'          ,2)
insert into t1 values(2,   'programmer'  ,3)create  function tt(@id int)
returns varchar(50)
as
begin
declare @s varchar(50)
set @s=''
select @s=@s+' '+words from t1
where id=@id
order by rank 
set @s=right(@s,len(@s))
return(@s)
endselect id,dbo.tt(id) as words from t1
group by idid          words                                              
----------- -------------------------------------------------- 
1            hello everybody
2            i am programmer（所影响的行数为 2 行）drop table T1
drop function tt

Answer 11

--用游标实现
create table t1 (id int, words varchar(10), rank int )
insert into t1 values(1,   'hello',       1)
insert into t1 values(1,   'everybody'  , 2)
insert into t1 values(2,   'i'           ,1)
insert into t1 values(2,   'am'          ,2)
insert into t1 values(2,   'programmer'  ,3)declare @s varchar(100)
declare @id int
declare @tb table( id int,words varchar(100))declare roy cursor for select id, words from t1
order by rankopen roy
fetch next from roy into @id, @s
while  @@fetch_status=0
begin 
   if not exists(select 1 from @tb where id=@id) 
    begin
        insert into @tb(id,words) select @id,@s    
    end
  else
     begin
        declare @sql varchar(100)
        select @sql=words+' ' from @tb
        where id=@id
        update @tb set words=@sql+@s
        where id=@id
         
    end
  fetch next from roy into @id, @s
end
close roy
deallocate royselect * from  @tb                                                                                   id               words        
----------- ------------------------- 
1           hello everybody
2           i am programmer（所影响的行数为 2 行）drop table t1

Answer 12

用户自定义函数：
ALTER FUNCTION dbo.combine
(@id varchar(100))  
RETURNS varchar (500) AS  
BEGIN 
declare @s varchar(100)
 set @s=''
 select @s=@s+''+words from 表名where id=@id
return @s
ENDT-SQL:
select 表名.id,dbo.combine(tb.id) as title 
from 表名
group by tb.id

Answer 13

--带符号合并行列转换--有表t，其数据如下：
  a b
  1 1
  1 2
  1 3
  2 1
  2 2
  3 1
--如何转换成如下结果:
  a b
  1 1,2,3
  2 1,2
  3 1 
--------------------------------------------
转换成这样的好处是什么啊？？ 
消除冗余、？？其他的呢？

Answer 14

select id,max(f),max(s),isnull(max(t),' ') from(
select id,case rank when 1 then words end as f,
case rank when 2 then words end as s,
case rank when 3 then words end as t
from T6
) a
group by id

Answer 15

create table t1 (id int, words varchar(10), rank int )
insert into t1 values(1,   'hello',       1)
insert into t1 values(1,   'everybody'  , 2)
insert into t1 values(2,   'i'           ,1)
insert into t1 values(2,   'am'          ,2)
insert into t1 values(2,   'programmer'  ,3)create function wsp1(@id int)
returns varchar(1000)
as 
begin
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+words from t1 where id=@id
return @sql
endselect distinct id,dbo.wsp1(id) as words from t1

Answer 16

declare @id int
declare @words varchar(128)
declare @rank int
declare @table table(words varchar(128))
set @id = 1
while exists(select * from t1 where id=@id)
begin
  set @rank=1
  set @words=''
  while exists (select * from t1 where id=@id and rank=@rank)
  begin
    set @words=@words+(select words from t1 where id=@id and rank=@rank)+' '
    set @rank=@rank+1
  end
  insert @table values(@words)
  set @id=@id+1
end
select * from @table

Answer 17

-- 测试数据
create table tb (id int,words varchar(20),rank int)insert into tb(id,words,rank)
select 1,'hello',1
union all select 1,'everybody',2
union all select 2,'i',1
union all select 2,'am',2
union all select 2,'programmer',3-- 创建函数
create function merge_words(@var_id int)
returns varchar(50)
asbegin
declare @string varchar(50)
set @string = ''
select @string = @string + words + ' '  
from tb
where id = @var_id
order by rank return @string
end-- 实现
select distinct dbo.merge_words(id)
from tb
-- 测试结果
1  hello everybody 
2  i am programmer 

Answer 18

declare @id int
declare @words varchar(128)
declare @table table(words varchar(128))
set @id = 1
while exists(select * from t1 where id=@id)
begin
  set @words=''
  select @words=@words+words+' ' from t1 where id=@id order by rank asc
  insert @table values(@words)
  set @id=@id+1
end
select * from @table更新了下

Answer 19

create table tb1 (id int,words varchar(20),rank int)
insert into tb1(id,words,rank)
select 1,'hello',1
union all select 1,'everybody',2
union all select 2,'i',1
union all select 2,'am',2
union all select 2,'programmer',3
declare @id int
declare @rank int
declare @words varchar(128)
declare @table table(id int,words varchar(128))
set @id = 1
set @rank = 1
  set @words=''
while exists(select * from tb1 where id=@id)
begin
while exists(select * from tb1 where id=@id and rank=@rank)
begin
  select @words=@words+words+' ' from tb1 where id=@id and rank=@rank
  set @rank=@rank+1
end
  insert @table values(@id,@words)
  set @words=''
set @rank = 1
set @id=@id+1
end
select * from @table