在sql server中帮助查询 [英] Help query in sql server
问题描述
嗨我在sql server中有疑问
表：病人
Hi I have doubt in sql server
Table :patient
pn | code | date | doctorcode
1 | 10 |2015-02-19 | 100
1 | 10 |2015-02-19 | 101
1 | 10 |2015-02-19 | 102
2 | 10 |2015-02-12 | 101
2 | 10 |2015-02-13 | 102
2 | 10 |2015-02-14 | 103
3 | 10 |2015-02-15 | 103
3 | 10 |2015-02-18 | 104
3 | 10 |2015-02-26 | 105
表：Patientref：
Table : Patientref:
pn | code | sdate | edate | Status
1 | 10 |2015-02-13 | 2015-02-19 | 1
1 | 10 |2015-02-19 | 2015-03-24 | 2
1 | 10 |2015-04-28 | 2015-05-08 | 4
2 | 10 |2015-02-08 | 2015-02-19 | 4
2 | 10 |2015-02-09 | 2015-02-19 | 2
2 | 10 |2015-02-10 | 2015-02-19 | 2
2 | 10 |2015-02-11 | 2015-02-18 | 1
3 | 10 |2015-02-10 | 2015-02-17 | 4
3 | 10 |2015-02-10 | 2015-02-17 | 3
3 | 10 |2015-02-11 | 2015-02-18 | 3
2 | 10 |2015-04-10 | 2015-05-19 | 2
3 | 10 |2015-02-11 | 2015-02-18 | 1
3 | 10 |2015-02-26 | 2015-03-18 | 1
这里我们需要考虑患者日期是在患者参数表的sdate和edate之间进行的，然后是条件满足我们需要考虑状态
最高价值订单（例如我们给出最高价值订单2的状态值是第一个4是第二高，3是thiredhight，1是第四个高价值）
如果日期下降b / w多个不同的sdate和edate具有相同的状态值，那么我们需要考虑最新的sdate值和整个reocrd我们需要提取
该值。
示例：患者
Here we need consider patient date is foll between sdate and edate of patientrefs table and then condition satisfy we need to consider status
highest values order(example in status values we give highest values order 2 is firsthight 4 is secondhighest and 3 is thiredhight and 1 is fourth hight value)
if date fall b/w multiple differenct sdate and edate with same status values then we need to consider latest sdate value and that entire reocrd we need to extract
that value.
examples: patient
pn | code | date | doctorcode
2 | 10 |2015-02-12 | 101
2 | 10 |2015-02-13 | 102
2 | 10 |2015-02-14 | 103
表：Patientref：
pn |代码| sdate | edate |状态
2 | 10 | 2015-02-08 | 2015-02-19 | 4
2 | 10 | 2015-02-09 | 2015-02-19 | 2
2 | 10 | 2015-02-10 | 2015-02-19 | 2
2 | 10 | 2015-02-11 | 2015-02-18 | 1
这里日期pn：2个值日期foll b / w sdate和patientref table的edate.then我们给出最高值状态再次为2状态2值有两个reocrds然后
我们选择max sdate（最新sdate）然后这个pn：2最晚的sdates是2015-02-10并且相应的edate和状态值我们需要重温
基于此我想要的输出如下
Table : Patientref:
pn | code | sdate | edate | Status
2 | 10 |2015-02-08 | 2015-02-19 | 4
2 | 10 |2015-02-09 | 2015-02-19 | 2
2 | 10 |2015-02-10 | 2015-02-19 | 2
2 | 10 |2015-02-11 | 2015-02-18 | 1
Here date pn: 2 values date foll b/w sdate and edate of patientref table.then we give highest values status is 2 again status 2 values have two reocrds then
we go for max sdate(latest sdate) then this pn:2 latesest sdates is 2015-02-10 and corresopnding edate and status value we need to retrive
based on this I want output like below
pn | code | date | doctorcode | sdate |edate |status
1 | 10 |2015-02-19 | 100 |2015-02-19 |2015-03-24 | 2
1 | 10 |2015-02-19 | 101 |2015-02-19 |2015-03-24 | 2
1 | 10 |2015-02-19 | 102 |2015-02-19 |2015-03-24 | 2
2 | 10 |2015-02-12 | 101 |2015-02-10 |2015-02-19 | 2
2 | 10 |2015-02-13 | 102 |2015-02-10 |2015-02-19 | 2
2 | 10 |2015-02-14 | 103 |2015-02-10 |2015-02-19 | 2
3 | 10 |2015-02-15 | 103 |2015-02-10 |2015-02-17 | 4
3 | 10 |2015-02-18 | 104 |2015-02-11 |2015-02-18 | 3
3 | 10 |2015-02-26 | 105 |2015-02-26 |2015-03-18 | 1
我试过如下：
I tried like below :
select a.pn,a.code,a.doctorcode,a.date,b.sdate,b.edate,b.status
from patient a
left join ( select b.pn,b.code,b.sdate,b.edate,row_number()over(partition by pn,org
order by case when status=2 then 1 when status=4 then 2 when status=3 then 3 when status=1 then 4 end desc,sdate desc)as rn
from patientref)b
on a.pn=b.pn and a.code=b.code and a.rn=1
and a.date between b.sdate and b.edate
但是以上查询未给出预期结果。请告诉我怎么写查询在sql server中执行此任务
but above query not given expected result .please tell me how to write query to achive this task in sql server
推荐答案
根据我的理解你想要获取符合此条件的数据：a.pn = b.pn AND a.code = b.code AND a.date b.sdate和b.edate
如果是这样，我最好的猜测是：
As per my understanding you want to fetch data which meet this criteria:a.pn = b.pn AND a.code = b.code AND a.date between b.sdate and b.edate
If so, my best guess is:
SELECT a.pn, a.code, a.doctorcode, a.date, b.sdate, b.edate, b.status
FROM patient AS a
INNER JOIN patientref AS b a.pn = b.pn AND a.code = b.code AND a.date between b.sdate and b.edate
ORDER BY CASE WHEN b.status=2 then 1
WHEN b.status=4 then 2
WHEN b.status=3 then 3
WHEN b.status=1 then 4
END desc, b.sdate desc
详情请见： SQL连接的可视化表示 [ ^ ]
这篇关于在sql server中帮助查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
