现有
表A 表B
id name firid secid thiid
1 军1 1 3 5
2 军2 1 3 6
3 师1 2 4 7
4 师2
5 旅1
6 旅2
7 旅3想得到如下结果
fir sec thi
军1 师1 旅1
军1 师1 旅2
军2 师2 旅3怎样实现呢?谢谢!
表A 表B
id name firid secid thiid
1 军1 1 3 5
2 军2 1 3 6
3 师1 2 4 7
4 师2
5 旅1
6 旅2
7 旅3想得到如下结果
fir sec thi
军1 师1 旅1
军1 师1 旅2
军2 师2 旅3怎样实现呢?谢谢!
from 表B,表A t1,表A t2,表A t3
where firid=t1.id and secid=t2.id and thiid=t3.id
试试
FROM B
INNER JOIN A A1 ON B.FIR=A1.ID
INNERJ OIN A A2 ON B.SEC=A2.ID
INNER JOIN A A3 ON B.THI=A3.ID
FROM B
INNER JOIN A A1 ON B.FIR=A1.ID
INNER JOIN A A2 ON B.SEC=A2.ID
INNER JOIN A A3 ON B.THI=A3.ID
insert ta
select 1 ,'军1' union all
select 2 ,'军2' union all
select 3 ,'师1' union all
select 4 ,'师2' union all
select 5 ,'旅1' union all
select 6 ,'旅2' union all
select 7 ,'旅3'
create table tb(firid int, secid int,thiid int)
insert tb
select 1,3,5 union all
select 1,3,6 union all
select 2,4,7
select t1.name fir,t2.name sec,t3.name thi
from tb,ta t1,ta t2,ta t3
where firid=t1.id and secid=t2.id and thiid=t3.id
/*
fir sec thi
---- ---- ----
军1 师1 旅1
军1 师1 旅2
军2 师2 旅3(所影响的行数为 3 行)
*/
--> 生成测试数据表: [A]
IF OBJECT_ID('[A]') IS NOT NULL
DROP TABLE [A]
GO
CREATE TABLE [A] ([id] [int],[name] [nvarchar](10))
INSERT INTO [A]
SELECT '1','军1' UNION ALL
SELECT '2','军2' UNION ALL
SELECT '3','师1' UNION ALL
SELECT '4','师2' UNION ALL
SELECT '5','旅1' UNION ALL
SELECT '6','旅2' UNION ALL
SELECT '7','旅3'--> 生成测试数据表: [B]
IF OBJECT_ID('[B]') IS NOT NULL
DROP TABLE [B]
GO
CREATE TABLE [B] ([firid] [int],[secid] [int],[thiid] [int])
INSERT INTO [B]
SELECT '1','3','5' UNION ALL
SELECT '1','3','6' UNION ALL
SELECT '2','4','7'--SELECT * FROM [A]
-->SQL查询如下:
SELECT X.[name] fir,Y.[name] sec,Z.[name] thi
FROM [B]
JOIN A X ON B.[firid]=X.ID
JOIN A Y ON B.[secid]=Y.ID
JOIN A Z ON B.[thiid]=Z.ID
/*
fir sec thi
---------- ---------- ----------
军1 师1 旅1
军1 师1 旅2
军2 师2 旅3(3 行受影响)
*/
表A 表B
id name firid secid thiid
1 军1 1 3 5
2 军2 1 3 6
3 师1 2 4 7
4 师2
5 旅1
6 旅2
7 旅3
create table ta (id int,name varchar(3))
insert ta
select 1 ,'军1' union all
select 2 ,'军2' union all
select 3 ,'师1' union all
select 4 ,'师2' union all
select 5 ,'旅1' union all
select 6 ,'旅2' union all
select 7 ,'旅3'
go
create table tbfirid(firid int, secid int,thiid int)
go
insert tbfirid(firid,secid,thiid)
select 1,3,5 union all
select 1,3,6 union all
select 2,4,7
go
select * from ta
go
select * from tbfirid
go
select a.name fir,a1.name sec,a2.name thi from ta a,ta a1,ta a2,tbfirid b
where a.id=b.firid and a1.id=b.secid and a2.id=b.thiid
go
想得到如下结果
fir sec thi
军1 师1 旅1
军1 师1 旅2
军2 师2 旅3怎样实现呢?谢谢!
insert ta
select 1 ,'军1' union all
select 2 ,'军2' union all
select 3 ,'师1' union all
select 4 ,'师2' union all
select 5 ,'旅1' union all
select 6 ,'旅2' union all
select 7 ,'旅3'
create table tb(firid int, secid int,thiid int)
insert tb
select 1,3,5 union all
select 1,3,6 union all
select 2,4,7
goselect t1.name,
t2.name,
t3.name
from tb t
left join ta t1 on t.firid = t1.id
left join ta t2 on t.secid = t2.id
left join ta t3 on t.thiid = t3.iddrop table ta , tb/*
name name name
---- ---- ----
军1 师1 旅1
军1 师1 旅2
军2 师2 旅3(所影响的行数为 3 行)*/
insert ta
select 1 ,'军1' union all
select 2 ,'军2' union all
select 3 ,'师1' union all
select 4 ,'师2' union all
select 5 ,'旅1' union all
select 6 ,'旅2' union all
select 7 ,'旅3'
create table tb(firid int, secid int,thiid int)
insert tb
select 1,3,5 union all
select 1,3,6 union all
select 2,4,7
goselect t1.name fir,
t2.name sec,
t3.name thi
from tb t
left join ta t1 on t.firid = t1.id
left join ta t2 on t.secid = t2.id
left join ta t3 on t.thiid = t3.iddrop table ta , tb/*
fir sec thi
---- ---- ----
军1 师1 旅1
军1 师1 旅2
军2 师2 旅3(所影响的行数为 3 行)*/