我有一个 UNION ALL 查询,我得到的结果不正确。我应该得到大约 1100 条记录。请参阅查询...
select
Pop, planID, PopFull, ApptDate, intake1,FollowUP2,FollowupCode, rn, '5133'
from
(Select *, row_number() over (partition by planID order BY AddedDate asc) as rn from Vinfo) t
where
rn = 1 and ApptDate >='12/1/2014' and ApptDate <='12/31/2015'
Union All
select
Pop, planID, PopFull, ApptDate, intake1, FollowUP2, FollowupCode, rn, '5133'
from
(Select *,row_number() over (partition by PlanID order BY AddedDate DESC) as rn from Vinfo) t
where
rn = 1 and ApptDate >='12/1/2014' and ApptDate <='12/31/2015'
所以我要做的是选择所有信息,在第一个 SELECT 语句中,我试图在 ADDDATE 最低(最早)时获取摄入量值。
我正在做 UNION,因为在第二个 SELECT 语句中,当 ADDDATE 是最旧(最新)时,我试图为 FOLLOWup 获取一个值。
"摄入"和"随访"的值可能不同,但并非必须不同。我正在尝试跟踪差异。
但是,当我运行此查询时,我得到双倍的记录。有没有办法让我运行此查询,以便我可以获得正确的记录数 (1100) 并获取 INTAKE 的值,如果 FOLLOWUP 的值发生变化,我将在同一行中看到它?
而不是看到一切的双重。基本上它现在的运行方式是,如果 PLANID 是 1023 并且最早日期的摄入量是 B,那么它也会显示 B 用于后续。此外,如果最新日期的跟进是 C,那么它将在其下方的行中向我显示 C 表示摄入和跟进。
编辑:
select Pop,planID,PopFull,ApptDate, intake1,FollowUP2,FollowupCode, '5133'
from (select *,
row_number() over (partition by planID order BY AddedDate asc) as rn_first,
row_number() over (partition by PlanID order BY AddedDate DESC) as rn_last
from VInfo
) t
where t.rn_first = 1 or rn_last = 1
and ApptDate >='12/1/2014' and ApptDate <='12/31/2015'
运行了这个但没有给出正确的结果
显然,对于那些只有一个组合实例的行Pop, planID, PopFull, ApptDate, intake1, FollowUP2, FollowupCode
你会得到重复 - 即ASC和DESC都返回一行,而ASC和DESC排序的rn=1。
UNION ALL
允许顶部和底部行集中的重复项。您可以改为尝试UNION
。
此外,如前所述,您可以在单选中同时计算ROW_NUMBERS:
select *
from
(
select v.*,
row_number() over (partition by PlanID order BY AddedDate asc) as rn_first,
row_number() over (partition by PlanID order BY AddedDate DESC) as rn_last
from Vinfo v
) t
where t.rn_first = 1 or rn_last = 1