产品分三级类别,但有的只有二级类别,现在想用一个视图,将所有的产品和类别关联起来,有三级类的就显示,没有三级类的只显示一二级类名称,三级类别处显示为null
A表
oneid onefolder
50 testa
53 testa1
B表
twoid oneid twofolder
87 50 testb
88 50 testb1
89 53 testb2
C表
threeid twoid threefolder
12 87 testc
13 88 testc1
product表
id oneid twoid threeid proname
1 50 87 12 产品1
2 53 89 0 产品2
想要如下的结果
id proname onefolder twofolder threefolder
1 产品1 testa testb testc
2 产品2 testa1 testb2 此处是null
注意一下product表的数据,因为产品2只能二级类别,没有三级,所以第三级类别值为0
SQL code
create table A(oneid int,onefolder varchar(20))
insert into A select 50 ,’testa’
insert into A select 53 ,’testa1’
create table B(twoid int,oneid int,twofolder varchar(20))
insert into B select 87 ,50,’testb’
insert into B select 88 ,50,’testb1’
insert into B select 89 ,53,’testb2’
create table C(threeid int,twoid int,threefolder varchar(20))
insert into C select 12 ,87 ,’testc’
insert into C select 13 ,88 ,’testc1’
create table product(id int,oneid int,twoid int,threeid int,proname varchar(20))
insert into product select 1,50,87,12,’产品1’
insert into product select 2,53 ,89 ,0 ,’产品2’
go
create view v_test
as
select x.id, x.proname,onefolder,twofolder,threefolder
from product x left join A on x.oneid=A.oneid
left join B on x.twoid=B.twoid
left join C on x.threeid=C.threeid
go
select * from v_test
drop view v_test
drop table A,B,C,product
/*
id proname onefolder twofolder threefolder
----------- -------------------- -------------------- -------------------- --------------------
1 产品1 testa testb testc
2 产品2 testa1 testb2 NULL
(所影响的行数为 2 行)
*/