求 sql 语句

Question

三个表 t1,t2,t3
t1:
t1id,pid,mc
1,0,a
2,0,b
3,1,ct2:
t2id,mc
0,x
1,y
2,zt3:
t3id,t1id,t2id
0,1,0
1,1,1
2,2,2
3,3,0
4,3,1
5,3,2得到结果：
t1id,pid,mc,t2id
1,0,a,0/1
2,0,b,2
3,1,c,0/1/2

Answer 1

http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html

Answer 2

表，T1
T1ID PID T1MC
1 0 A
2 0 B
3 1 C表2，T2
T2ID T2MC
0 X
1 Y
2 Z 表3，T3
T3ID T1ID T2ID
0 1 0
1 1 1
2 2 2
3 3 0
4 3 1
5 3 2 得到结果：
T1ID PID T1MC T2ID T2MC
1 0 A 0/1 X/Y
2 0 B 2 Y
3 1 C 0/1/2 X/Y/Z

Answer 3

if object_id('[T1]') is not null drop table [T1]
go
if object_id('[T2]') is not null drop table [T2]
go
if object_id('[T3]') is not null drop table [T3]
go
create table [T1]([T1ID] int,[PID] int,[T1MC] varchar(5))
create table [T2]([T2ID] int,[T2MC] varchar(5))
create table [T3]([T3ID] int,[T1ID] int,[T2ID] int)
go
insert into [T1]
select 1, 0, 'A' union all
select 2, 0, 'B' union all
select 3, 1, 'C'
go
insert into [T2]
select 0, 'X' union all 
select 1, 'Y' union all
select 2, 'Z' 
go
insert into [T3]
select 0, 1, 0 union all 
select 1, 1, 1 union all 
select 2, 2, 2 union all 
select 3, 3, 0 union all  
select 4, 3, 1 union all  
select 5, 3, 2 
go;with cte as
(
select a.[T1ID],c.[PID],c.[T1MC],a.[T2ID],b.[T2MC] from [T3] a
inner join [T2] b
on a.[T2ID]=b.[T2ID]
inner join [T1] c
on a.[T1ID]=c.[T1ID]
)select a.[T1ID],a.[PID],a.[T1MC],
a.[T1ID],T2ID=stuff(b.[T2ID].value('/R[1]','nvarchar(max)'),1,1,''),T2MC=stuff(c.[T2MC].value('/R[1]','nvarchar(max)'),1,1,'')  
from  
(select distinct [T1ID],[PID],[T1MC] from cte) a 
Cross apply 
(select T2ID=(select N'/'+convert(varchar,[T2ID]) from cte where [T1ID]=a.[T1ID] For XML PATH(''), ROOT('R'), TYPE))b 
cross apply
(select T2MC=(select N'/'+convert(varchar,[T2MC]) from cte where [T1ID]=a.[T1ID] For XML PATH(''), ROOT('R'), TYPE))c /*(3 row(s) affected)(3 row(s) affected)(6 row(s) affected)
T1ID        PID         T1MC  T1ID        T2ID                                                                                                                                                                                                                                                             T2MC
----------- ----------- ----- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           0           A     1           0/1                                                                                                                                                                                                                                                              X/Y
2           0           B     2           2                                                                                                                                                                                                                                                                Z
3           1           C     3           0/1/2                                                                                                                                                                                                                                                            X/Y/Z(3 row(s) affected)
*/