需要帮助在sql server 2008中查询

本文介绍了需要帮助在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）然后 记录相应的服务代码与患者服务表匹配没有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.