select t1.e A , t2.e B , t3.e C from x left join y t1 on x.a = t1.d left join y t2 on x.a = t2.d left join y t3 on x.a = t3.d
1楼写错了,更改为如下: create table x(A int,B int,C int) insert into x values(1, 3 ,5) insert into x values(2, 4 ,6) create table Y(D int,E varchar(10)) insert into y values(1 ,'语文') insert into y values(2 ,'数学') insert into y values(3 ,'英文') insert into y values(4 ,'化学') insert into y values(5 ,'物理') insert into y values(6 ,'生物') goselect t1.e A , t2.e B , t3.e C from x left join y t1 on x.a = t1.d left join y t2 on x.b = t2.d left join y t3 on x.c = t3.d/* A B C ---------- ---------- ---------- 语文 英文 物理 数学 化学 生物(所影响的行数为 2 行) */drop table x , y
select (select E from Y where D=a.A) as A, (select E from Y where D=a.B) as B, (select E from Y where D=a.C) as D from X as a
select (select E from y where y.d=x.a) as A, (select E from y where y.d=x.b) as B, (select E from y where y.d=x.c) as C from x
create table #A(A VARCHAR(10),B VARCHAR(10),C VARCHAR(10))create table #B(D VARCHAR(10),E VARCHAR(10))INSERT #A SELECT '1','3','5' UNION ALL SELECT '2','4','6'INSERT #B SELECT 1,'语文' union all select 2,'数学' union all select 3,'英语' union all select 4,'化学' union all select 5,'物理' union all select 6,'生物'select b.E A,c.E B,d.E C from #A a left join #B b on a.A=b.D left join #B c on a.B=c.D left join #B d on a.C=d.D
create table #X( A varchar(10) null, B varchar(10) null, C varchar(10) null ) select * insert into #X(A,B,C) values('1','3','5') insert into #X(A,B,C) values('2','4','6') create table #Y( D varchar(10) null, E varchar(10) null )insert into #Y values('1','语文') insert into #Y values('2','数学') insert into #Y values('3','英文') insert into #Y values('4','化学') insert into #Y values('5','物理') insert into #Y values('6','生物')select #y.E as A,y1.E as B,y2.E as C from #X inner join #Y on #X.A= #Y.D inner join #Y as y1 on #X.B=y1.D inner join #Y as y2 on #X.C=y2.D
go create table X( A int, B int, C int ) go insert X select 1, 3 ,5 union all select 2, 4 ,6 go create table Y( D int, E varchar(10) ) go insert Y select 1 ,'语文' union all select 2 ,'数学' union all select 3 ,'英文' union all select 4 ,'化学' union all select 5 ,'物理' union all select 6 ,'生物' select b.E A,c.E B,d.E C from X a left join Y b on a.A=b.D left join Y c on a.B=c.D left join Y d on a.C=d.D /* A B C 语文 英文 物理 数学 化学 生物 */
left join y t1 on x.a = t1.d
left join y t2 on x.a = t2.d
left join y t3 on x.a = t3.d
create table x(A int,B int,C int)
insert into x values(1, 3 ,5)
insert into x values(2, 4 ,6)
create table Y(D int,E varchar(10))
insert into y values(1 ,'语文')
insert into y values(2 ,'数学')
insert into y values(3 ,'英文')
insert into y values(4 ,'化学')
insert into y values(5 ,'物理')
insert into y values(6 ,'生物')
goselect t1.e A , t2.e B , t3.e C from x
left join y t1 on x.a = t1.d
left join y t2 on x.b = t2.d
left join y t3 on x.c = t3.d/*
A B C
---------- ---------- ----------
语文 英文 物理
数学 化学 生物(所影响的行数为 2 行)
*/drop table x , y
select
(select E from Y where D=a.A) as A,
(select E from Y where D=a.B) as B,
(select E from Y where D=a.C) as D
from X as a
(select E from y where y.d=x.b) as B,
(select E from y where y.d=x.c) as C
from x
SELECT '1','3','5'
UNION ALL
SELECT '2','4','6'INSERT #B
SELECT 1,'语文'
union all
select 2,'数学'
union all
select 3,'英语'
union all
select 4,'化学'
union all
select 5,'物理'
union all
select 6,'生物'select b.E A,c.E B,d.E C
from #A a left join #B b on a.A=b.D
left join #B c on a.B=c.D
left join #B d on a.C=d.D
A varchar(10) null,
B varchar(10) null,
C varchar(10) null
)
select *
insert into #X(A,B,C) values('1','3','5')
insert into #X(A,B,C) values('2','4','6') create table #Y(
D varchar(10) null,
E varchar(10) null
)insert into #Y values('1','语文')
insert into #Y values('2','数学')
insert into #Y values('3','英文')
insert into #Y values('4','化学')
insert into #Y values('5','物理')
insert into #Y values('6','生物')select #y.E as A,y1.E as B,y2.E as C from #X inner join #Y on #X.A= #Y.D
inner join #Y as y1 on #X.B=y1.D
inner join #Y as y2 on #X.C=y2.D
go
create table X(
A int,
B int,
C int
)
go
insert X
select 1, 3 ,5 union all
select 2, 4 ,6
go
create table Y(
D int,
E varchar(10)
)
go
insert Y
select 1 ,'语文' union all
select 2 ,'数学' union all
select 3 ,'英文' union all
select 4 ,'化学' union all
select 5 ,'物理' union all
select 6 ,'生物' select b.E A,c.E B,d.E C from X a
left join Y b on a.A=b.D
left join Y c on a.B=c.D
left join Y d on a.C=d.D
/*
A B C
语文 英文 物理
数学 化学 生物
*/