我目前的查询：
SELECT
E.FILEKEY,
(SELECT MAX(EFFDATE)
FROM
(SELECT B.FILEKEY, B.STATUS AS STATUS, B.EFFDATE
FROM TABLEB B
UNION
SELECT A.FILEKEY, A.STATUS2 AS STATUS, A.EFFDATE2 AS EFFDATE
FROM TABLEA A) C
WHERE C.EFFDATE <= '5/1/2011'),
A.STATUS
FROM
TABLE1 A
INNER JOIN
TABLE2 B ON A.FILEKEY = B.FILEKEY
GROUP BY
A.FILEKEY, A.STATUS
tableA和tableB的并集(表C(产生以下结果：
FILEKEY STATUS EFFDATE
----------------------------------------
1 0 2009-01-01 00:00:00.000
1 1 2010-01-01 00:00:00.000
1 0 2011-01-01 00:00:00.000
1 1 2012-01-01 00:00:00.000
1 0 2013-01-01 00:00:00.000
我知道我的查询是错误的，它提取以下结果：
filekey (No column name) status
------------------------------------------
1 2011-01-01 00:00:00.000 1
我只是不确定如何使用关联的effdate提取正确的状态而不会导致出现各种错误。
感谢您的帮助。
我认为您正在寻找这样的东西。
SELECT TOP 1 *
FROM
(SELECT B.FILEKEY, B.STATUS AS STATUS, B.EFFDATE FROM TABLEB B
UNION
SELECT A.FILEKEY, A.STATUS2 AS STATUS, A.EFFDATE2 AS EFFDATE FROM TABLEA A) C
WHERE C.EFFDATE <= '5/1/2011')
ORDER BY C.EFFDATE DESC