如何避免sql中出现重复的子查询连接?

CTE:

;WITH YourQuery AS
(
 SELECT code, date1, date2 from Table
)
SELECT 
    T1.stuff, T2.morestuff 
    from YourQuery           T1
        INNER JOIN YourQuery T2 ON T1.code = T2.code and  T1.date1 = T2.date2

供参考

在这个问题中,代码使用的是派生表,也称为内联视图。子查询是返回单个值并嵌套在select、insert、update或delete语句或其他子查询中的select查询。子查询可以在任何允许使用表达式的地方使用。见: http://msdn.microsoft.com/en-us/library/aa213252(SQL.80).aspx

您可以使用视图。

CREATE VIEW myView AS 
SELECT code, date1, date2 
FROM Table

然后你的问题是这样的:

SELECT T1.stuff, T2.morestuff 
FROM myView as T1
INNER JOIN myView as T2 ON T1.code = T2.code and  T1.date1 = T2.date2

为什么它们是子查询?

SELECT T1.stuff, T2.morestuff
FROM Table T1
INNER JOIN Table T2
ON T1.code = T2.code and T1.date1 = T2.date2

为什么不在表上加两次别名呢?

SELECT T1.stuff, T2.stuff FROM Table as T1 INNER JOIN Table as T2
ON T1.code = T2.code and  T1.date1 = T2.date2