MSSQL2008R2现在有三个表,分别是:ServerDiskPath,DiskName,MovType三个表的列分别如下表ServerDiskPath
列:MovServerID DiskID TotalSpace FreeSpace ReservedSpace MovType
值: 1 4 500 100 5 1,3,5,7
值: 1 5 230 100 5 1,2,8,9
值: 1 6 320 100 5 1,4,6,7
表DiskName
列:DiskID DiskName
值: 3 C
值: 4 D
值: 5 E
表MovType
列:MovType TypeName
值: 1 动作片
值: 2 喜剧片
值: 3 科幻片请问我如何查询ServerDiskPath才能使DiskID,MovType显示为DiskName,TypeName而其他的显示不变
列:MovServerID DiskID TotalSpace FreeSpace ReservedSpace MovType
值: 1 4 500 100 5 1,3,5,7
值: 1 5 230 100 5 1,2,8,9
值: 1 6 320 100 5 1,4,6,7
表DiskName
列:DiskID DiskName
值: 3 C
值: 4 D
值: 5 E
表MovType
列:MovType TypeName
值: 1 动作片
值: 2 喜剧片
值: 3 科幻片请问我如何查询ServerDiskPath才能使DiskID,MovType显示为DiskName,TypeName而其他的显示不变
select a.ServerDiskPath,b.DiskName,c.TypeName
from ServerDiskPath a,DiskName b,MovType c
where a.DiskID=b.DiskID and a.MovType=c.MovType
select A.MovServerID,B.DiskName,A.TotalSpace,A.FreeSpace,A.ReservedSpace,C.TypeName
from ServerDiskPath as A
inner join DiskName as B on A.DiskID = B.DiskID
inner join MovType as C on A.MovType = .MovType
ServerDiskPatch表的值如果是 1 4 500 100 5 1,2,3
那么查询结果应该是1 D 500 100 5 动作片,喜剧片,科幻片
go
create table [ServerDiskPath]([MovServerID] int,[DiskID] int,[TotalSpace] int,[FreeSpace] int,[ReservedSpace] int,[MovType] varchar(7))
insert [ServerDiskPath]
select 1,4,500,100,5,'1,3,5,7' union all
select 1,5,230,100,5,'1,2,8,9' union all
select 1,6,320,100,5,'1,4,6,7'
go
if object_id('[DiskName]') is not null drop table [DiskName]
go
create table [DiskName]([DiskID] int,[DiskName] varchar(1))
insert [DiskName]
select 3,'C' union all
select 4,'D' union all
select 5,'E'
go
if object_id('[MovType]') is not null drop table [MovType]
go
create table [MovType]([MovType] int,[TypeName] varchar(6))
insert [MovType]
select 1,'动作片' union all
select 2,'喜剧片' union all
select 3,'科幻片'
gocreate function [dbo].[ChangeName]
(@SourceSql varchar(100),
@StrSeprate varchar(10))
returns varchar(200)
as
begin
declare @ch as varchar(100)
declare @StrReturn as varchar(100)
set @SourceSql=@SourceSql+@StrSeprate
set @StrReturn=''
while(@SourceSql<>'')
begin
set @ch=left(@SourceSql,charindex(@StrSeprate,@SourceSql,1)-1)
select @StrReturn=@StrReturn+TypeName+',' from MovType where MovType=@ch
set @SourceSql=stuff(@SourceSql,1,charindex(@StrSeprate,@SourceSql,1),'')
end
set @StrReturn = stuff(@StrReturn,len(@StrReturn),1,'')
return @StrReturn
end
go
select a.MovServerID, b.DiskName,a.TotalSpace, a.FreeSpace, a.ReservedSpace,dbo.ChangeName(MovType,',') as TypeName
from ServerDiskPath a
left join DiskName b on a.DiskID=b.DiskID/**
MovServerID DiskName TotalSpace FreeSpace ReservedSpace TypeName
----------- -------- ----------- ----------- ------------- --------------------------
1 D 500 100 5 动作片,科幻片
1 E 230 100 5 动作片,喜剧片
1 NULL 320 100 5 动作片(3 行受影响)
**/
create table ServerDiskPath
(MovServerID int, DiskID int,TotalSpace int, FreeSpace int,ReservedSpace int,MovType nvarchar(50))
insert ServerDiskPath
select 1, 4 ,500 ,100, 5, '1,3,5,7' union all
select 1 ,5 ,230, 100 ,5 ,'1,2,8,9' union all
select 1, 6, 320, 100 ,5 ,'1,4,6,7'create table DiskName
(DiskID int, DiskName nvarchar(10))
insert DiskName
select 3, 'C' union all
select 4, 'D' union all
select 5, 'E'create table MovType
(MovType int,TypeName nvarchar(20))
insert MovType
select 1 ,'动作片' union all
select 2 ,'喜剧片' union all
select 3 ,'科幻片'create function TH(@MovType nvarchar(50))returns nvarchar(400)
as
begin
select @MovType=replace(@MovType,ltrim(MovType),TypeName) from MovType
return @MovType
endselect MovServerID, isnull(DiskName,'') as DiskName, TotalSpace, FreeSpace,
ReservedSpace, dbo.TH(s.MovType) as TypeName
from ServerDiskPath as s left join DiskName as d on s.DiskID=d.DiskIDMovServerID DiskName TotalSpace FreeSpace ReservedSpace TypeName
----------- ---------- ----------- ----------- ------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 D 500 100 5 动作片,科幻片,5,7
1 E 230 100 5 动作片,喜剧片,8,9
1 320 100 5 动作片,4,6,7(3 row(s) affected)