SQL - Where Select by Select Select语句?
我有以下查询:SQL - Where Select by Select Select语句?
select r.people_code_id [People Code ID], r.resident_commuter [Campus6],
c1.udormcom [AG], aR.RESIDENT_COMMUTER [AG Bridge], ar.ACADEMIC_SESSION,
ar.ACADEMIC_TERM, ar.academic_year, ar.revision_date
from RESIDENCY r
left join AG_Common..CONTACT1 c1 on r.PEOPLE_CODE_ID=c1.key4
left join AG_Common..CONTACT2 c2 on c1.ACCOUNTNO=c2.accountno
left join AGPCBridge..ArchiveRESIDENCY aR on r.PEOPLE_CODE_ID=aR.PEOPLE_CODE_ID
where r.ACADEMIC_YEAR='2010'
and r.ACADEMIC_TERM='Fall'
and SUBSTRING(c1.udormcom,1,1)<>r.resident_commuter
and r.ACADEMIC_SESSION='Und 01'
and aR.ACADEMIC_SESSION='Und 01'
and aR.ACADEMIC_TERM='Fall'
and aR.ACADEMIC_YEAR='2010'
and SUBSTRING(c1.udormcom,1,1)=aR.RESIDENT_COMMUTER
我需要一个条款添加到哪里段。我有这个查询:
select DISTINCT * from RESIDENCY where ACADEMIC_YEAR='2010' and
ACADEMIC_TERM='Fall' and ACADEMIC_SESSION='Und 01' ORDER BY revision_date DESC
这只获得每个人的最新行。我想做类似(伪代码):
WHERE r.people_code_id and r.revision_date are in (select DISTINCT * from
RESIDENCY where ACADEMIC_YEAR='2010' and ACADEMIC_TERM='Fall' and
ACADEMIC_SESSION='Und 01' ORDER BY revision_date DESC)
我在SQL 2000兼容模式下运行(尽管它实际上运行SQL 2008)。
我重新写你的查询的基础上,你想添加什么:
WITH residency_cte AS (
SELECT TOP (1)
r.people_code_id,
r.resident_commuter,
r.academic_year,
r.academic_term,
r.academic_session
FROM RESIDENCY r
WHERE r.academic_year = '2010'
AND r.academic_term = 'Fall'
AND r.academic_session = 'Und 01'
ORDER BY revision_date DESC)
SELECT r.people_code_id,
r.resident_commuter [Campus6],
c1.udormcom [AG],
aR.RESIDENT_COMMUTER,
ar.ACADEMIC_SESSION,
ar.ACADEMIC_TERM,
ar.academic_year,
ar.revision_date
FROM residency_cte r
LEFT JOIN AG_Common..CONTACT1 c1 ON c1.key4 = r.PEOPLE_CODE_ID
AND SUBSTRING(c1.udormcom, 1, 1) != r.resident_commuter
LEFT JOIN AG_Common..CONTACT2 c2 ON c2.accountno = c1.ACCOUNTNO
LEFT JOIN AGPCBridge..ArchiveRESIDENCY aR ON aR.PEOPLE_CODE_ID = r.PEOPLE_CODE_ID
AND aR.ACADEMIC_SESSION = r.academic_session
AND aR.ACADEMIC_TERM = r.academic_term
AND aR.ACADEMIC_YEAR = r.academic_year
AND SUBSTRING(c1.udormcom, 1, 1) = aR.RESIDENT_COMMUTER
唯一的事情是udormcom
列位置 - 一旦我知道它是什么表,我想移动条款成连接。我也更新了ArchiveRESIDENCY
表的连接,所以你只需要在一个地方调整日期。
但请注意,使用子字符串在另一列上匹配将永远不会执行良好 - 直到数据模型更改为正确,永远不会真正优化。
你可以使用一个子查询的EXISTS
select
r.people_code_id [People Code ID],
r.resident_commuter [Campus6],
udormcom [AG],
aR.RESIDENT_COMMUTER [AG Bridge],
ar.ACADEMIC_SESSION,
ar.ACADEMIC_TERM,
ar.academic_year,
ar.revision_date
from RESIDENCY r
left join AG_Common..CONTACT1 c1
on r.PEOPLE_CODE_ID=c1.key4
left join AG_Common..CONTACT2 c2
on c1.ACCOUNTNO=c2.accountno
left join AGPCBridge..ArchiveRESIDENCY aR
on r.PEOPLE_CODE_ID=aR.PEOPLE_CODE_ID
where r.ACADEMIC_YEAR='2010'
and r.ACADEMIC_TERM='Fall'
and SUBSTRING(udormcom,1,1)<>r.resident_commuter
and r.ACADEMIC_SESSION='Und 01'
and aR.ACADEMIC_SESSION='Und 01'
and aR.ACADEMIC_TERM='Fall'
and aR.ACADEMIC_YEAR='2010'
and SUBSTRING(udormcom,1,1)=aR.RESIDENT_COMMUTER
and EXISTS
(
select 1
FROM RESIDENCY r2
where 1=1
and r2.revision_date = ar.revision_date /* note the join here */
and ACADEMIC_YEAR='2010'
and ACADEMIC_TERM='Fall'
and ACADEMIC_SESSION='Und 01'
/* the order by has been removed */
)
我不需要订单吗?例如,对于同一个人,我可能会有三个居留记录 - 同年,任期和会话。告诉哪一个是最新版本的唯一方法是revision_date--这就是为什么我有ORDER BY DESC(将记录从最新到最旧)以及为什么我需要DISTINCT(仅用于最新版本)。 – davemackey 2010-06-25 14:30:08
WHERE r.people_code_id in (select DISTINCT people_code_id
from RESIDENCY
where ACADEMIC_YEAR='2010'
and ACADEMIC_TERM='Fall'
and ACADEMIC_SESSION='Und 01'
and revision_date = r.revision_date
ORDER BY revision_date DESC)
我不认为你需要的“不同”或“按顺序”。去除这些应该会提高性能。
我需要在people_code_id和revision_date上进行匹配,因此需要使用ORDER BY DESC和DISTINCT。它确保我只有每个人的最新记录。 – davemackey 2010-06-25 14:28:27
+1:对于表别名,并实际使用它们 – 2010-06-25 14:14:10
我不明白你的第二条语句只能得到最新的行,因为where子句与第一条语句中的相同。 (RESIDENCY明智)。 – Loxley 2010-06-25 14:19:51
@OMG Ponies - udormcom来自AG_Common..CONTACT1 – davemackey 2010-06-25 14:26:30