需要帮助在sql server 2008中查询 [英] need to help query in sql server 2008
问题描述
我有关于SQL Server的问题
表：病人
I have a question about SQL Server
Table : patient
dos |pn |code |servicecode
2015-09-21 |10 |4 |90
2015-10-06 |10 |4 |91
2015-09-04 |10 |4 |92
2015-04-07 |11 |5 |80
2015-04-07 |11 |5 |94
2015-04-08 |11 |5 |94
2015-02-02 |12 |6 |96
2015-02-02 |12 |6 |97
2015-07-21 |12 |6 |94
2012-11-07 |13 |7 |93
2012-11-07 |13 |7 |94
2012-11-07 |13 |7 |99
2013-10-07 |13 |7 |90
2014-11-09 |13 |7 |98
2014-12-17 |13 |7 |90
表2：Patientrefs
Table 2: Patientrefs
Pn | code | sdate | edate
10 | 4 | 2015-09-04 | 2015-10-05
11 | 5 | 2015-04-01 | 2015-09-21
12 | 6 | 2015-02-02 | 2015-10-12
12 | 6 | 2014-04-20 | 2014-05-23
13 | 7 | 2012-11-05 | 2015-05-20
表3：PatientService
Table 3: PatientService
Servicecode
90
94
这里患者和患者复制常见列Pn +代码如果pn +代码组合匹配则我们在患者表服务代码查看患者服务后查看min（dos）表格服务代码然后那个组合匹配然后我们认为dos是minimumdos
根据上面的表我想要输出如下
Here patient and patientrefts common columns Pn + code if pn+ code combination matches then we look into min(dos) after that patient table servicecode look into patientservice table service code then that combination matches then we consider dos is minimumdos
Based on above tables I want output like below
pn | code | mindos
11 | 5 | 2015-04-07
13 | 7 | 2012-11-07
这里pn = 10和pn = 12没有mindos，因为min（dos）相关的服务代码不满足于患者服务表服务代码
主要是我们需要考虑sdate和edate之间的dos foll。如果满足那么我们在患者表中去min（dos）然后<br />
记录相应的服务代码与患者服务表匹配没有matche那么就没有那个病人的心态。
例子：
Here pn=10 and pn=12 do not have mindos because of min(dos) related service code not satisfy with patientservice table service code
ere mainly we need to consider dos foll between sdate and edate.if satisfy then we go min(dos)in patient table then
that records corresponding servicecode is match with patientservice table if not matche then there is no mindos of that patient.
example:
Patient
dos |pn |code |servicecode
2015-09-21 |10 |4 |90
2015-10-06 |10 |4 |91
2015-09-04 |10 |4 |92
Patientrefs
Pn | code | sdate | edate
10 | 4 | 2015-09-04 | 2015-10-05
这里患者表dos foll介于sdate和patientrefs表的edate之间我们将考虑患者表中的min（dos）时间min（dos）是2015-09-04 | 10 | 4 | 92然后
我们看看patientservice表servicecode匹配patien表servicecodet时间记录不匹配所以pn = 10 donot有类似方式pn：12
我这样试过
here patient table dos foll between sdate and edate of patientrefs table then we will consider min(dos) in patient table that time min(dos) is 2015-09-04 |10 |4 |92 then
we look in patientservice table servicecode match with patien table servicecodethat time records is not match so pn=10 donot have mindos similar way pn:12
I tried like this
select
pn, code, dos as mindos
from
(select
pn, code, servicecode
from
(select
pn, code, servicecode,
row_number() over (partition by pn, code order by dos) as rn
from patient) a
inner join
patientrefs b on a.pn = b.pn and a.code = b.code and b.rn = 1
and a.dos between b.sdate and b.edate) as mindos
inner join
patientservic c on mindos.servicecode = c.servicecode
但上述查询并未准确返回预期结果。 />
请告诉我怎么做ite查询在SQL Server中实现此任务
But above query is not returning the expected result accurately.
Please tell me how to write query to achieve this task in SQL Server
推荐答案
我已经对提供的虚拟数据运行了查询，它会生成一些错误消息：
I have run your query on the dummy data supplied and it generates a few error messages:
Invalid column name 'rn'.
Invalid column name 'dos'.
Invalid column name 'dos'.
Ambiguous column name 'pn'.
Ambiguous column name 'code'.
修复这些错误时，查询返回以下内容：
On fixing these errors the query returned the following:
pn code mindos
11 5 2015-04-07
原因是以下记录未被退回
The reason the following record is not being returned
pn | code | mindos
13 | 7 | 2012-11-07
是由于这两条记录在 row_number（）中的排序方式over 查询的一部分
is due to the way these two records are being sorted in the row_number() over part of the query
dos |pn |code |servicecode
2012-11-07 |13 |7 |94
2012-11-07 |13 |7 |99
servicecode = 99的记录首先出现，而加入PatientService的记录没有服务记录99，所以不会返回预期记录。
要获得所需的结果，可以将查询修改为
1。考虑到订单中的服务代码为min dos
或
2.最初使用PatientService过滤患者表，然后通过
希望能帮到你的路上。
如果你需要更多的帮助，修复错误信息中陈述的初始错误然后再做一个评论。
但是如果你有关于initi的问题al error，我也可以帮你解决这些问题。
---更新---
The record with the servicecode = 99 is coming in first and the join to PatientService does not have service record 99, so the expected record is not returned.
To get the result you desire, the query can be modified to
1. take into consideration of the servicecode in the order by for min dos
or
2. filter the patient table with the PatientService initially and then do the min dos order by
Hope that helps you on your way.
If you need anymore help, fix the initial errors stated in the error messages and then just make a comment.
But if you have having issues with the initial errors, I can help you out with those too.
---update---
--here is your supplied query, modified to get your desired results
select
pn, code, dos as mindos
from
(select
a.pn, a.code, servicecode, a.dos
from
(select
pn, code, servicecode, dos,
row_number() over (partition by pn, code order by dos, servicecode) as rn
from patient) a
inner join
patientrefs b on a.pn = b.pn and a.code = b.code and a.rn = 1
and a.dos between b.sdate and b.edate) as mindos
inner join
PatientService c on mindos.servicecode = c.servicecode
--here is an additional query which will get the required results.
select
*
from(
select
row_number() over (partition by pn, code order by dos, servicecode) as rn,
*
from Patient) p
inner join Patientrefs pr
on p.pn = pr.pn and p.code = pr.code
inner join PatientService ps
on p.servicecode = ps.Servicecode
where p.rn = 1
and p.dos between pr.sdate and pr.edate
;
--update -- one way of putting a type of ordering to the servicecode can be done by modifying the sub-query portion of the query (row_number() over of Patient).
--something like this, the servicecodeOrder to be taken as 999 if not in PatientService table.
--you will need to define your correct ordering of servicecode
(
select
row_number() over (partition by pn, code order by dos, isnull(ps.servicecode, 999)) as rn,
p.*,
isnull(ps.servicecode, 999) servicecodeOrder
from Patient p
left join PatientService ps
on p.servicecode = ps.servicecode
) p
他们都应该工作，但你需要测试它。
They should both work, but you need to test it out.
这篇关于需要帮助在sql server 2008中查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
