请教一个sql

Question

有一张表TRegion
fseq       fregionname
1      华东
2      华北有一张表TRegionCity
fseq     fRCity
1           12010
1           12011
1           12012
2           12013
2           12014有一张表TCITY
fcitycode    fdesc
12010         上海
12011         杭州
12012       苏州
12013         北京
12014         天津要得到下面一个视图，如何写这个视图
fseq       fregionname           city
1              华东   上海,杭州,苏州
2        华北   北京,天津

Answer 1

2005以上用下面这个OK
select a.fseq,a.fregionname,c.fdesc
into #temp from TRegion a
left join TRegionCity b on a.fseq=b.fseq
left join TCITY c on b.fRCity=c.fcitycodeselect fseq,fregionname,
city=(stuff((select ','+fdesc from #temp where fseq=a.fseq and fregionname=a.fregionname for xml path('')),1,1,''))
from #temp a

Answer 2

--> 测试数据：[TRegion]
if object_id('[TRegion]') is not null drop table [TRegion]
create table [TRegion]([fseq] int,[fregionname] varchar(4))
insert [TRegion]
select 1,'华东' union all
select 2,'华北'--> 测试数据：[TRegionCity]
if object_id('[TRegionCity]') is not null drop table [TRegionCity]
create table [TRegionCity]([fseq] int,[fRCity] int)
insert [TRegionCity]
select 1,12010 union all
select 1,12011 union all
select 1,12012 union all
select 2,12013 union all
select 2,12014
--> 测试数据：[TCITY]
if object_id('[TCITY]') is not null drop table [TCITY]
create table [TCITY]([fcitycode] int,[fdesc] varchar(4))
insert [TCITY]
select 12010,'上海' union all
select 12011,'杭州' union all
select 12012,'苏州' union all
select 12013,'北京' union all
select 12014,'天津'
--2000
--创建函数
if object_id('f_str') is not null drop function f_str
go
create function f_str(@fseq int)
returns varchar(1000)
as 
begin 
declare @s varchar(1000)
  select @s=isnull(@s+',','')+b.[fdesc] from [TRegionCity] a,[TCITY] b 
  where a.fRCity=b.fcitycode and  fseq=@fseq
return @s
end
--查询
select a.*,b.fRCity from [TRegion] a join 
(
select fseq,fRCity=dbo.f_str(fseq) from [TRegionCity] group by fseq
) b on a.fseq=b.fseq

Answer 3

fseq        fregionname fRCity                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1           华东          上海,杭州,苏州
2           华北          北京,天津（所影响的行数为 2 行）

Answer 4

IF OBJECT_ID('TRegion') IS NOT NULL DROP TABLE TRegion
IF OBJECT_ID('TRegionCity') IS NOT NULL DROP TABLE TRegionCity
IF OBJECT_ID('TCITY') IS NOT NULL DROP TABLE TCITY
GO
CREATE TABLE TREGION(fseq INT, fregionname VARCHAR(10))
INSERT INTO TREGION
SELECT 1 ,'华东' UNION ALL
SELECT 2 ,'华北'
CREATE TABLE TREGIONCITY(fseq INT, fRCity INT)
INSERT INTO TREGIONCITY
SELECT 1, 12010 UNION ALL
SELECT 1, 12011 UNION ALL
SELECT 1, 12012 UNION ALL
SELECT 2, 12013 UNION ALL
SELECT 2, 12014
CREATE TABLE TCITY(fcitycode INT, fdesc VARCHAR(10))
INSERT INTO TCITY
SELECT 12010 ,'上海' UNION ALL
SELECT 12011 ,'杭州' UNION ALL
SELECT 12012 ,'苏州' UNION ALL
SELECT 12013 ,'北京' UNION ALL
SELECT 12014 ,'天津'
SELECT FSEQ,fregionname
,CITY=STUFF((
SELECT ','+T2.FDESC
FROM TREGIONCITY T1
INNER JOIN TCITY T2 ON T1.fRCity=T2.fcitycode
WHERE T1.fseq=T0.FSEQ
FOR XML PATH(''),ROOT('R'),TYPE
).value('/R[1]','VARCHAR(MAX)'),1,1,'')
FROM TREGION T0
/*
FSEQ        fregionname CITY
----------- ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           华东          上海,杭州,苏州      
2           华北          北京,天津    
*/

Answer 5

--> 测试数据：[TRegion]
if object_id('[TRegion]') is not null drop table [TRegion]
create table [TRegion]([fseq] int,[fregionname] varchar(4))
insert [TRegion]
select 1,'华东' union all
select 2,'华北'--> 测试数据：[TRegionCity]
if object_id('[TRegionCity]') is not null drop table [TRegionCity]
create table [TRegionCity]([fseq] int,[fRCity] int)
insert [TRegionCity]
select 1,12010 union all
select 1,12011 union all
select 1,12012 union all
select 2,12013 union all
select 2,12014
--> 测试数据：[TCITY]
if object_id('[TCITY]') is not null drop table [TCITY]
create table [TCITY]([fcitycode] int,[fdesc] varchar(4))
insert [TCITY]
select 12010,'上海' union all
select 12011,'杭州' union all
select 12012,'苏州' union all
select 12013,'北京' union all
select 12014,'天津'
go
select b.fseq,b.[fregionname],
city = stuff((select ',' + Convert(varchar(36),a.fdesc)
from(select t.[fseq],t.[fregionname],r.[fRCity],c.[fdesc] 
from [TRegion] t,[TRegionCity] r,[TCITY] c
where t.[fseq]=r.[fseq] and
r.[fRCity]=c.[fcitycode]) a where a.fseq=b.fseq
for xml path('')),1,1,'')
from(select t.[fseq],t.[fregionname],r.[fRCity],c.[fdesc] 
from [TRegion] t,[TRegionCity] r,[TCITY] c
where t.[fseq]=r.[fseq] and
r.[fRCity]=c.[fcitycode]) b group by b.fseq,b.[fregionname]
go

Answer 6

SELECT FSEQ,fregionname
,CITY=replace((
SELECT T2.FDESC as 'data()'
FROM TREGIONCITY T1
INNER JOIN TCITY T2 ON T1.fRCity=T2.fcitycode
WHERE T1.fseq=T0.FSEQ
FOR XML PATH('')),' ',',')
FROM TREGION T0
(2 行受影响)(5 行受影响)(5 行受影响)
       FSEQ fregionname CITY
----------- ----------- -----------------------
          1 华东          上海,杭州,苏州
          2 华北          北京,天津(2 行受影响)