有两个表T1, T2
T1有字段
MapSNamer
MapSNumber
T2有字段
SNum
ObsTimes
MinT
MaxT
Prec
要查询T2表中的数据，其SNum必须是T1表中MapSNumber中包含的
这个查询语句如何优化一下呢，用join如何修改呢
SELECT T1.MapSName, T2.SNum, T2.ObsTimes, T2.MinT, T2.MaxT, T2.Prec
FROM T2, T1
WHERE (((T2.SNum) In (Select T1.MapSNumber From T1) And (T2.SNum)=[T1].[MapSNumber]));
------解决思路----------------------
SELECT T1.MapSName,T2.SNum,T2.ObsTimes,T2.MinT,T2.MaxT,T2.Prec
FROM T1 JOIN T2 ON T1.MapSNumber=T2.SNum
------解决思路----------------------
直接连接表就行了，效率也比IN要好
SELECT T2.* FROM T1 INNER JOIN T2 ON T1.MapSNumber=T2.SNum
------解决思路----------------------
尽量用join来，虽然不一定性能是最好的，但肯定是比较平均的性能水平。
------解决思路----------------------
那改成exists嘛
select * from t2 where exists(select 1 from t1 where MapSNumber=t2.SNum)
------解决思路----------------------
可以 这样就不会有重复了
------解决思路----------------------
关键是要看你怎么去掉重复～
USE [AdventureWorks2012];
GO
DECLARE @t_TB TABLE
(
[ID] TINYINT NOT NULL IDENTITY(1,1)
, [MapSNamer] VARCHAR(10)
, [MapSNumber] VARCHAR(10)
)
;
DECLARE @t_TB2 TABLE
(
[ID] TINYINT NOT NULL IDENTITY(1,1)
, [SNum] VARCHAR(10)
, [ObsTimes] DATETIME
, [MinT] TINYINT
, [MaxT] TINYINT
, [Prec] TINYINT
)
;
INSERT INTO @t_TB VALUES
('A','A-1')
, ('B','B-1')
, ('C','C-1')
, ('D','D-1')
, ('E','E-1')
, ('F','F-1')
, ('G','G-1')
, ('H','H-1')
;
INSERT INTO @t_TB2 VALUES
('A',GETDATE(),1,1,10)
, ('A',GETDATE(),2,2,20)
, ('B',GETDATE(),1,1,10)
, ('B',GETDATE(),2,2,20)
, ('B',GETDATE(),3,3,30)
, ('C',GETDATE(),5,6,60)
, ('C',GETDATE(),1,2,20)
, ('C',GETDATE(),3,2,20)
, ('C',GETDATE(),4,2,20)
, ('D',GETDATE(),5,2,20)
, ('E',GETDATE(),6,2,20)
, ('F',GETDATE(),7,2,20)
;
SELECT
T2.[SNum]
, T2.[ObsTimes]
, T2.[MinT]
, T2.[MaxT]
, T2.[Prec]
FROM @t_TB2 AS [T2]
INNER JOIN @t_TB AS [T] ON [T].[MapSNamer] = [T2].[SNum]
WHERE NOT EXISTS ( SELECT 1 FROM @t_TB2 AS [TB] WHERE [TB].[SNum] = [T2].[SNum] AND [TB].[ID] > [T2].[ID])
/*
SNum ObsTimes MinT MaxT Prec
---------- ----------------------- ---- ---- ----
A 2015-04-18 12:42:04.760 2 2 20
B 2015-04-18 12:42:04.760 3 3 30
C 2015-04-18 12:42:04.760 4 2 20
D 2015-04-18 12:42:04.760 5 2 20
E 2015-04-18 12:42:04.760 6 2 20
F 2015-04-18 12:42:04.760 7 2 20
*/