- 论坛徽章:
- 0
|
0039--复杂行例转换
一条看似简单，但是复杂的SQL题目？
Consider the following account and customer tables:
表1：cust_tbl
cust_id title e_first_name e_last_name address1 .
0 MR Martin Ma .
1 MR Kirs Cheung .
2 MR Ricky Chan .
3 MR Tom Kwan .
4 MR Corporate Default Corporate Default .
5 MRS Mary Mok .
表2：acc_grp_cust_tbl
acc_group Cust_id1 Cust_id2 Cust_id3 Cust_id4
1400 0 1 2 .
1500 3 4 . .
1600 5 . . .
The acc_grp_cust_tbl table is responsible to store the customer ID, and the cust_tbl table is responsible to store customer personal information such as name, address, etc… Please write a SQL query in order to provide following result.
ACC_GROUP PAYEENAMES
1400 Ma Martin/Cheung Kris/Chan Ricky
1500 Kwan Tom/Corporate Default Corporate Default
1600 Mok Mary
要注意PAYEENAMES显示的结果方式
请问Answer： 是什么？
if object_id(\'tempdb..#cust_tbl\') is not null
drop table #cust_tbl
GO
create table #cust_tbl([cust_id] [char](1) , [e_first_name] [varchar](100) , [e_last_name] [varchar](100))
insert into #cust_tbl(cust_id,e_first_name,e_last_name) values(\'0\',\'Martin\',\'Ma\')
insert into #cust_tbl(cust_id,e_first_name,e_last_name) values(\'1\',\'Kirs\',\'Cheung\')
insert into #cust_tbl(cust_id,e_first_name,e_last_name) values(\'2\',\'Ricky\',\'Chan\')
insert into #cust_tbl(cust_id,e_first_name,e_last_name) values(\'3\',\'Tom\',\'Kwan\')
insert into #cust_tbl(cust_id,e_first_name,e_last_name) values(\'4\',\'Corporate Default Corporate Default \',\'\')
insert into #cust_tbl(cust_id,e_first_name,e_last_name) values(\'5\',\'Mary\',\'Mok\')
if object_id(\'tempdb..#a\') is not null
drop table #a
GO
select cust_id , e_last_name + \' \' + e_first_name as PAYEENAMES into #a from #cust_tbl
if object_id(\'tempdb..#acc_grp_cust_tbl\') is not null
drop table #acc_grp_cust_tbl
GO
create table #acc_grp_cust_tbl([acc_group] [char](4) , [Cust_id1] [char](1) , [Cust_id2] [char](1) , [Cust_id3] [char](1))
insert into #acc_grp_cust_tbl(acc_group,Cust_id1,Cust_id2,Cust_id3) values(\'1400\',\'0\',\'1\',\'2\')
insert into #acc_grp_cust_tbl(acc_group,Cust_id1,Cust_id2,Cust_id3) values(\'1500\',\'3\',\'4\',\'\')
insert into #acc_grp_cust_tbl(acc_group,Cust_id1,Cust_id2,Cust_id3) values(\'1600\',\'5\',\'\',\'\')
if object_id(\'tempdb..#b\') is not null
drop table #b
GO
select * into #b from
(
select acc_group,Cust_id1 as Cust_id from #acc_grp_cust_tbl where Cust_id1 <> \'\'
union all
select acc_group,Cust_id2 as Cust_id from #acc_grp_cust_tbl where Cust_id2 <> \'\'
union all
select acc_group,Cust_id3 as Cust_id from #acc_grp_cust_tbl where Cust_id3 <> \'\'
)t
if object_id(\'tempdb..tb\') is not null
drop table tb
GO
select #b.acc_group ,#a.PAYEENAMES into tb from #a,#b where #a.cust_id = #b.cust_id
if object_id(\'tempdb..fn_合并\') is not null drop function fn_合并
go
create function fn_合并(
@acc_group varchar(20)
)
returns varchar(300)
as
begin
declare @r varchar(300)
set @r=\'\'
select top 3 @r=@r+\'/\'+PAYEENAMES from tb where acc_group=@acc_group
if @r<>\'\'
set @r=stuff(@r,1,1,\'\')
return @r
end
go
--调用
select acc_group,dbo.fn_合并(acc_group) as PAYEENAMES from tb group by acc_group
acc_group PAYEENAMES
--------- -------------
1400 Ma Martin/Cheung Kirs/Chan Ricky
1500 Kwan Tom/ Corporate Default Corporate Default
1600 Mok Mary
（所影响的行数为 3 行） |
|
免费注册
楼主: tigerfish
发表于 2006-10-21 22:55
,