我有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