求一个VIEW（视图）写法
我有3个表， 关小如下
表1 Grade
GradeID GradeName
1 grade1
2 grade2
3 grade3
4 grade4
表2 Attribute
AttID AttName
1 A
2 C
3 N
4 F
表3 GradeAttribute
GradeAttributeID GradeID AttID AttSize
1 1 1 5
2 1 2 7
3 1 4 0
4 2 1 4
5 2 4 5
6 4 2 5
7 4 3 0
我现在想写一个VIEW, 显示结果如下
GradeID GradeName GradeAttribute
1 grade1 A5 C7 F0
2 grade2 F0 A4
4 grade4 C5 N0
谢谢大家了
[解决办法]
create table Grade
(GradeID int,GradeName varchar(10))
insert into Grade
select 1,'grade1' union all
select 2,'grade2' union all
select 3,'grade3' union all
select 4,'grade4'
create table Attribute
(AttID int,AttName varchar(5))
insert into Attribute
select 1,'A' union all
select 2,'C' union all
select 3,'N' union all
select 4,'F'
create table GradeAttribute
(GradeAttributeID int,GradeID int,AttID int,AttSize int)
insert into GradeAttribute
select 1,1,1,5 union all
select 2,1,2,7 union all
select 3,1,4,0 union all
select 4,2,1,4 union all
select 5,2,4,5 union all
select 6,4,2,5 union all
select 7,4,3,0
-- 创建视图
create view v_Grade_Attribute
as
with t as
(select c.GradeID,
d.AttName+rtrim(c.AttSize) 'GradeAttribute'
from GradeAttribute c
inner join Attribute d on c.AttID=d.AttID
)
select a.GradeID,b.GradeName,a.GradeAttribute
from
(select e.GradeID,
stuff((select ' '+f.GradeAttribute from t f
where f.GradeID=e.GradeID for xml path('')),1,1,'') 'GradeAttribute'
from t e
group by e.GradeID
) a
inner join Grade b on a.GradeID=b.GradeID
-- 查询
select * from v_Grade_Attribute
/*
GradeID GradeName GradeAttribute
----------- ---------- ----------------
1 grade1 A5 C7 F0
2 grade2 A4 F5
4 grade4 C5 N0
(3 row(s) affected)
*/
if object_id('Grade','u')is not null
drop table Grade
go
create table Grade(GradeID int, GradeName varchar(20))
insert into Grade
select 1,'grade1' union all
select 2,'grade2' union all
select 3,'grade3' union all
select 4,'grade4'
go
if object_id('Attribute','u')is not null
drop table Attribute
go
create table Attribute(AttID int, AttName varchar(20))
insert into Attribute
select 1,'A' union all
select 2,'C' union all
select 3,'N' union all
select 4,'F'
go
if object_id('GradeAttribute','u')is not null
drop table GradeAttribute
go
create table GradeAttribute(GradeAttributeID int, GradeID int, AttID int, AttSize int)
insert into GradeAttribute
select 1,1,1,5 union all
select 2,1,2,7 union all
select 3,1,4,0 union all
select 4,2,1,4 union all
select 5,2,4,5 union all
select 6,4,2,5 union all
select 7,4,3,0
go
if object_id('v_test','v')is not null
drop view v_test
go
create view v_test
as
with cte as
(
select b.GradeID,b.GradeName,c.AttName+convert(varchar(20),a.AttSize)GradeAttribute
from GradeAttribute a
left join Grade b on a.GradeID=b.GradeID
left join Attribute c on a.AttID=c.AttID
)
select distinct GradeID,GradeName,
(select stuff((
select ' '+GradeAttribute from cte b where a.GradeID=b.GradeID for xml path('')),1,1,''))GradeAttribute
from cte a
go
select * from v_test
----------------------------------------------
GradeID GradeName GradeAttribute
----------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 grade1 A5 C7 F0
2 grade2 A4 F5
4 grade4 C5 N0
(3 row(s) affected)
create table Grade
(GradeID int,GradeName varchar(10))
insert into Grade
select 1,'grade1' union all
select 2,'grade2' union all
select 3,'grade3' union all
select 4,'grade4'
create table Attribute
(AttID int,AttName varchar(5))
insert into Attribute
select 1,'A' union all
select 2,'C' union all
select 3,'N' union all
select 4,'F'
create table GradeAttribute
(GradeAttributeID int,GradeID int,AttID int,AttSize int)
insert into GradeAttribute
select 1,1,1,5 union all
select 2,1,2,7 union all
select 3,1,4,0 union all
select 4,2,1,4 union all
select 5,2,4,5 union all
select 6,4,2,5 union all
select 7,4,3,0
create view dbo.v_GradeAttribute
as
select GradeID, GradeName,GradeAttribute
from
(
select distinct
g.GradeID,
g.GradeName,
stuff(
(
select ' ' + a.AttName + cast(ga.AttSize as varchar)
from GradeAttribute ga
inner join Attribute a
on ga.AttID = a.AttID
where g.GradeID = ga.GradeID
for xml path('')
),
1,1,''
) as GradeAttribute
from Grade g
)t
where t.GradeAttribute is not null
go
select *from dbo.v_GradeAttribute;
/*
GradeIDGradeNameGradeAttribute
1 grade1 A5 C7 F0
2 grade2 A4 F5
4 grade4 C5 N0
*/
