求一个VIEW（视图）写法

[pennymay]

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

谢谢大家了

[pennymay]

非常谢谢大家

[唐诗三百首]

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)
*/

[Landa_Jimmy]

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)

[LongRui888]

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;
/*
GradeID	GradeName	GradeAttribute
1	    grade1	    A5 C7 F0
2	    grade2	    A4 F5
4	    grade4	    C5 N0
*/

[LongRui888]

对了，还有结果集是不是应该是这样的呢： GradeID GradeName GradeAttribute 1 grade1 A5 C7 F0 2 grade2 A4 F5 4 grade4 C5 N0 而不是你上面说的： GradeID GradeName GradeAttribute 1 grade1 A5 C7 F0 2 grade2 F0 A4 4 grade4 C5 N0

[鱼缸里的小泥鳅]

很不错喔，高手如云