22,210
社区成员
发帖
与我相关
我的任务
分享
declare @table1 table(ID int,ClassName varchar(10),Type int)
insert @table1 select
1 ,'分类1', 1 union select
2 ,'分类2', 2 union select
3 ,'分类3', 3 union select
4 ,'分类4', 1 union select
5 ,'分类5', 3
declare @table2 table(ID int,Name varchar(10), Class1 int, Class2 int, Class3 int)
insert @table2 select
1 ,'产品1', 1 , 2 , 3 union select
2 ,'产品2', 4 , 2 , 5 union select
3 ,'产品3', 4 , 2 , 3
select b.id,b.name,a.classname class1,c.classname class2,d.classname class3
from @table2 b left join @table1 a on b.Class1=a.id
left join @table1 c on b.Class2=c.id
left join @table1 d on b.Class3=d.id
select ID,Name,
Class1=(select ClassName from T1 where type=T2.Class1),
Class2=(select ClassName from T1 where type=T2.Class2),
Class3=(select ClassName from T1 where type=T2.Class3)
from T2
select
t2.ID,t2.Name,t1.ClassName as Class1 ,t3.ClassName as Class2 ,t4.ClassName as Class3
from
t1
join
t2 on t1.ID=t2.class1
join
t1 as t3 on t2.class2=t3.ID
join
t1 as t4 on t2.class3=t4.ID
ID Name Class1 Class2 Class3
----------- ---------- ---------- ---------- ----------
1 产品1 分类1 分类2 分类3
3 产品3 分类4 分类2 分类3
2 产品2 分类4 分类2 分类5
(所影响的行数为 3 行)
select p.ID,p.Name,a1.ClassName as class1,a2.ClassName as class2,a3.ClassName as class3
from 表2 p,表1 a1,表1 a2,表1 a3
where p.Class1=a1.id
and p.Class2=a2.id
and p.Class3=a3.id
create table T1
(
ID int,
ClassName nvarchar(10),
[Type] int
)
insert T1 select 1, '分类1', 1
insert T1 select 2, '分类2', 2
insert T1 select 3, '分类3', 3
insert T1 select 4, '分类4', 1
insert T1 select 5, '分类5', 3
create table T2(
ID int,
[Name] nvarchar(10),
Class1 int,
Class2 int,
Class3 int
)
insert T2 select 1, '产品1', 1, 2, 3
insert T2 select 2, '产品2', 4, 2, 5
insert T2 select 3, '产品3', 4, 2, 3
go
select
t2.ID,t2.Name,t1.ClassName ,t3.ClassName ,t4.ClassName
from
t1
join
t2 on t1.ID=t2.class1
join
t1 as t3 on t2.class2=t3.ID
join
t1 as t4 on t2.class3=t4.ID
ID Name ClassName ClassName ClassName
----------- ---------- ---------- ---------- ----------
1 产品1 分类1 分类2 分类3
3 产品3 分类4 分类2 分类3
2 产品2 分类4 分类2 分类5
(所影响的行数为 3 行)
create table T1
(
ID int,
ClassName nvarchar(10),
[Type] int
)
insert T1 select 1, '分类1', 1
insert T1 select 2, '分类2', 2
insert T1 select 3, '分类3', 3
insert T1 select 4, '分类4', 1
insert T1 select 5, '分类5', 3
create table T2(
ID int,
[Name] nvarchar(10),
Class1 int,
Class2 int,
Class3 int
)
insert T2 select 1, '产品1', 1, 2, 3
insert T2 select 2, '产品2', 4, 2, 5
insert T2 select 3, '产品3', 4, 2, 3
select ID,Name,
Class1=(select ClassName from T1 where type=1 and ID=T2.Class1),
Class2=(select ClassName from T1 where type=2 and ID=T2.Class2),
Class3=(select ClassName from T1 where type=3 and ID=T2.Class3)
from T2
--result
ID Name Class1 Class2 Class3
----------- ---------- ---------- ---------- ----------
1 产品1 分类1 分类2 分类3
2 产品2 分类4 分类2 分类5
3 产品3 分类4 分类2 分类3
(3 行受影响)
--错了,改一下
select ID,Name,
Class1=(select ClassName from T1 where type=1 and ID=T2.Class1),
Class2=(select ClassName from T1 where type=2 and ID=T2.Class2),
Class3=(select ClassName from T1 where type=3 and ID=T2.Class3)
from T2
declare @table1 table(ID int,ClassName varchar(10),Type int)
insert @table1 select
1 ,'分类1', 1 union select
2 ,'分类2', 2 union select
3 ,'分类3', 3 union select
4 ,'分类4', 1 union select
5 ,'分类5', 3
declare @table2 table(ID int,Name varchar(10), Class1 int, Class2 int, Class3 int)
insert @table2 select
1 ,'产品1', 1 , 2 , 3 union select
2 ,'产品2', 4 , 2 , 5 union select
3 ,'产品3', 4 , 2 , 3
select a.id,a.name,
class1 = b.ClassName,
class2 = c.ClassName,
class3 = d.ClassName
from @table2 a
left join @table1 b on b.id = a.class1
left join @table1 c on c.id = a.class2
left join @table1 d on d.id = a.class3
/*
id name class1 class2 class3
----------- ---------- ---------- ---------- ----------
1 产品1 分类1 分类2 分类3
2 产品2 分类4 分类2 分类5
3 产品3 分类4 分类2 分类3
*/
select ID,Name,
Class1=(select ClassName from T1 where type=T2.Class1),
Class2=(select ClassName from T1 where type=T2.Class2),
Class3=(select ClassName from T1 where type=T2.Class3)
from T2
select a.id,a.name,
class1 = b.ClassName,
class2 = c.ClassName,
class3 = d.ClassName
from table1 a
left join table2 b on a.id = b.class1
left join table2 c on a.id = c.class2
left join table2 d on a.id = d.class3