请教一个查询语句,从表tb_a,tb_b 中获得需要的查询结果
tb_a
FA1 FA2 FA3
---------------
AAA BBB 1234
BBB CCC 2222
AAA CCC 3333
AAA CCC 2323
tb_b
FB1 FB2
----------
AAA China
BBB Japan
CCC Korea想要的查询结果
AAA China BBB Japan 1234
BBB Japan CCC Korea 2222
AAA China CCC Korea 3333
AAA China CCC Korea 2323
tb_a
FA1 FA2 FA3
---------------
AAA BBB 1234
BBB CCC 2222
AAA CCC 3333
AAA CCC 2323
tb_b
FB1 FB2
----------
AAA China
BBB Japan
CCC Korea想要的查询结果
AAA China BBB Japan 1234
BBB Japan CCC Korea 2222
AAA China CCC Korea 3333
AAA China CCC Korea 2323
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-03-22 14:37:51
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据:[tb_a]
if object_id('[tb_a]') is not null drop table [tb_a]
go
create table [tb_a]([FA1] varchar(3),[FA2] varchar(3),[C3] int)
insert [tb_a]
select 'AAA','BBB',1234 union all
select 'BBB','CCC',2222 union all
select 'AAA','CCC',3333 union all
select 'AAA','CCC',2323
--------------开始查询--------------------------
--> 测试数据:[tb_b]
if object_id('[tb_b]') is not null drop table [tb_b]
go
create table [tb_b]([FB1] varchar(3),[FB2] varchar(5))
insert [tb_b]
select 'AAA','China' union all
select 'BBB','Japan' union all
select 'CCC','Korea'
--------------开始查询--------------------------select a.fa1,b.fb2,A.fa2,c.fb2,a.[C3]
from [tb_a] a LEFT JOIN [tb_b] b ON a.fa1=b.fb1
LEFT JOIN [tb_b] c ON a.fa2=c.fb1
----------------结果----------------------------
/*
fa1 fb2 fa2 fb2 C3
---- ----- ---- ----- -----------
AAA China BBB Japan 1234
BBB Japan CCC Korea 2222
AAA China CCC Korea 3333
AAA China CCC Korea 2323
*/
就是tb_a的FA1与tb_b的FB1关联再用tb_a的FA2与tb_b的FB1关联,之后就是选字段了
if OBJECT_ID('tb_a') is not null
drop table tb_a
if OBJECT_ID('tb_b') is not null
drop table tb_b
go
create table tb_a(FA1 VARCHAR(5),FA2 VARCHAR(5),FA3 INT)
CREATE TABLE TB_B(FB1 VARCHAR(5),FB2 VARCHAR(5))
INSERT INTO tb_a
SELECT 'AAA', 'BBB', 1234 UNION ALL
SELECT 'BBB', 'CCC', 2222 UNION ALL
SELECT 'AAA', 'CCC', 3333 UNION ALL
SELECT 'AAA', 'CCC', 2323
INSERT INTO TB_B
SELECT 'AAA', 'China' UNION ALL
SELECT 'BBB', 'Japan' UNION ALL
SELECT 'CCC', 'Korea'
select a.fa1,b.fb2,a.fa2,c.fb2,a.fa3 from tb_a a join
TB_B b on a.FA1=b.FB1
join TB_B c on a.FA2=c.FB1没办法我也只能做这种简单的。