表a
a1 a2
---------
1 A
2 B
3 C表b
b1 b2 b3 b4
--------------------
1 1 2 3
2 2 3 1
3 3 1 2
b2/b3/b4 对应a1想要这样的结果
c1 c2 c3 c4
------------------
1 A B C
2 B C A
3 C A B其中c1=b1/c2=a2(b2=a1)/c3=a2(b3=a1)/c4=a2(b4=a1)说明白了吗??
a1 a2
---------
1 A
2 B
3 C表b
b1 b2 b3 b4
--------------------
1 1 2 3
2 2 3 1
3 3 1 2
b2/b3/b4 对应a1想要这样的结果
c1 c2 c3 c4
------------------
1 A B C
2 B C A
3 C A B其中c1=b1/c2=a2(b2=a1)/c3=a2(b3=a1)/c4=a2(b4=a1)说明白了吗??
(select a2 from a where a1=b3)as c3,
(select a2 from a where a1=b4) as c4 from b
CREATE TABLE a
(a1 int,
a2 nvarchar(50)
)
CREATE TABLE b
(
b1 int,
b2 int,
b3 int,
b4 int
)
GO--插入数据
INSERT a VALUES(1, N'A')
INSERT a VALUES(2, N'B')
INSERT a VALUES(3, N'C')
INSERT b VALUES(1, 1, 2, 3)
INSERT b VALUES(2, 2, 3, 1)
INSERT b VALUES(3, 3, 1, 2)
GO--测试
SELECT b.b1 as c1,ba1.a2 as c2,ba2.a2 as c3,ba3.a2 as c4 FROM b INNER JOIN a as ba1 on b.b2=ba1.a1 INNER JOIN a as ba2 on b.b3=ba2.a1 INNER JOIN a as ba3 on b.b4=ba3.a1 order by c1--删除测试环境
DROP TABLE a
DROP TABLE b--结果:
c1 c2 c3 c4
1 A B C
2 B C A
3 C A B