各位大神，已知表1，表2，怎么才能得到表3的数据

[woobol]

有没有什么执行效率高点的思路
因为表1，表2数据量挺大的

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('t1') IS NOT NULL DROP TABLE t1
IF OBJECT_ID('t2') IS NOT NULL DROP TABLE t2
GO
CREATE TABLE t1(
	gh INT PRIMARY KEY,
	xm NVARCHAR(20)	
)
INSERT INTO t1 VALUES (1,'啊啊啊')
INSERT INTO t1 VALUES (2,'不不不')
INSERT INTO t1 VALUES (3,'错错错')
GO
CREATE TABLE t2(
	a_gh INT,
	b_gh INT,
	c_gh INT	
)
INSERT INTO t2 VALUES (1,3,1)
INSERT INTO t2 VALUES (3,2,2)
INSERT INTO t2 VALUES (3,NULL,NULL)
GO
--提高效率的方法
--t1 中的 gh 应该设置为主键
--t2 中的 3 个字段增加索引
--另外，数据量大了只能分页！
CREATE INDEX ix_t2_a_gh ON t2(a_gh);
CREATE INDEX ix_t2_b_gh ON t2(b_gh);
CREATE INDEX ix_t2_c_gh ON t2(c_gh); 
GO

SELECT t11.xm AS a_gh_xm,
	t12.xm AS b_gh_xm,
	t13.xm AS c_gh_xm 
FROM t2 
	LEFT JOIN t1 AS t11 ON t2.a_gh=t11.gh
	LEFT JOIN t1 AS t12 ON t2.b_gh=t12.gh
	LEFT JOIN t1 AS t13 ON t2.c_gh=t13.gh

[woobol]

引用 1 楼 yenange 的回复:

USE tempdb
GO
IF OBJECT_ID('t1') IS NOT NULL DROP TABLE t1
IF OBJECT_ID('t2') IS NOT NULL DROP TABLE t2
GO
CREATE TABLE t1(
	gh INT PRIMARY KEY,
	xm NVARCHAR(20)	
)
INSERT INTO t1 VALUES (1,'啊啊啊')
INSERT INTO t1 VALUES (2,'不不不')
INSERT INTO t1 VALUES (3,'错错错')
GO
CREATE TABLE t2(
	a_gh INT,
	b_gh INT,
	c_gh INT	
)
INSERT INTO t2 VALUES (1,3,1)
INSERT INTO t2 VALUES (3,2,2)
INSERT INTO t2 VALUES (3,NULL,NULL)
GO
--提高效率的方法
--t1 中的 gh 应该设置为主键
--t2 中的 3 个字段增加索引
--另外，数据量大了只能分页！
CREATE INDEX ix_t2_a_gh ON t2(a_gh);
CREATE INDEX ix_t2_b_gh ON t2(b_gh);
CREATE INDEX ix_t2_c_gh ON t2(c_gh); 
GO

SELECT t11.xm AS a_gh_xm,
	t12.xm AS b_gh_xm,
	t13.xm AS c_gh_xm 
FROM t2 
	LEFT JOIN t1 AS t11 ON t2.a_gh=t11.gh
	LEFT JOIN t1 AS t12 ON t2.b_gh=t12.gh
	LEFT JOIN t1 AS t13 ON t2.c_gh=t13.gh

感谢