需要SQL Server查询协助 [英] SQL Server query assistance needed
问题描述
我正在视图中存储一堆数据(将MS Access查询转换为视图).现在,我想做的是编写一个存储过程,以根据添加数据的时间提取数据.这是我正在运行的查询
I'm storing a bunch of data in a view (converted MS Access queries to views). Now what I'm trying to do is write a stored procedure to pull data based on when the data was added. Here is the query that I'm running
Select
Name, PlanID, ApptDate, 1stAppt, rn,
from
(Select
*,
row_number() over (partition by PlanID Order BY AddedonDate desc) as rn
From vClientInfo)
因此,这使我的所有数据都可以了.我要解决的问题是,客户端实际上进入了第一个应用程序和第二个应用程序-基本上,我需要提取最新日期和最早的数据,并在查询中包含"2ndAppt".因此1stAppt和2ndAppt保留了一个字符串值,稍后我将使用...这两个值可能从1stAppt到2ndAppt不同-只有AddedOn日期才能告诉我是否有不同(最早的是1stAppt,最新的是2ndAppt)./p>
So this pulls all my data okay. The issue that I have to address is, the client actually comes in for the 1st appt and 2nd appt - Basically I need to pull the data for LATEST date, as well as EARLIEST, and include a '2ndAppt' in my query. So 1stAppt and 2ndAppt hold a string value that I will later use...The 2 values might differ from 1stAppt to 2ndAppt - and only AddedOn date will tell me if there was a different (earliest for 1stAppt, and latest for 2ndAppt).
Select
Name, PlanID, ApptDate, 1stAppt, 2ndAppt rn,
From
(Select
*,
row_number() over (partition by PlanID Order BY AddedonDate desc) as rn
From vClientInfp)
所以我做到了,我改变了...
So I did this and I changed this...
(Select *, row_number() over (partition by PlanID Order BY AddedonDate *ASC*)
但是,由于我要处理大量数据且难以验证,因此我不确定这是否提取正确的数据.因此,基本上我需要进行合并,以便我的两个查询都能提取正确的数据.最初我获得1920条记录,所以有了工会,我应该获得的记录也一样多,但是,这个数字是两倍,所以3840,为什么?
However I'm not sure if this is pulling correct data as I'm working with a lot of data and hard to verify. So basically I need to do a union so that both my queries pull in the correct data. Originally I was getting 1920 records, so with a union i should be getting just as many too i assume, however, the number is double so 3840, why?
Select
Name, PlanID, ApptDate, 1stAppt, 2ndappt, rn,
From
(Select
*,
row_number() over (partition by PlanID Order BY AddedonDate desc) as rn
From vClientInfo
Union All
Select
Name, PlanID, ApptDate, 1stAppt, 2ndAppt, rn,
From
(Select
*,
row_number() over (partition by PlanID Order BY AddedonDate asc) as rn
From vClientInfo
推荐答案
您正在使用窗口函数,但没有对其进行过滤,所以有什么用呢?如果要最早和最新,请选择rn = 1:
You are using window function, but you are not filtering by it, so whats the point? If you want the earliest and the latest, choose rn = 1 :
Select Name, PlanID, ApptDate, 1stAppt,2ndappt, rn,
from (Select *,
row_number() over (partition by PlanID Order BY AddedonDate desc) as rn
from vClientInfo
Union All
Select *,
row_number() over (partition by PlanID Order BY AddedonDate asc) as rn
from vClientInfo)
WHERE rn = 1
如果您希望最早的应用程序是第一个,最新的应用程序是第二个:
If you want 1st appt from the earliest and 2ndappt from the latest :
Select Name, PlanID, ApptDate,
MAX(CASE WHEN rnk = 1 then 1stAppt end) as 1stAppt,
MAX(CASE WHEN rn = 1 THEN 2ndappt end) as 2ndAppt
from (Select *,
row_number() over (partition by PlanID Order BY AddedonDate desc) as rn,
0 as rnk
from vClientInfo
Union All
Select *,
0 as rn,
row_number() over (partition by PlanID Order BY AddedonDate asc) as rnk
from vClientInfo)
GROUP BY Name,PlanID,ApptDate
这篇关于需要SQL Server查询协助的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!