sql-server - 选择多个连接查询中相同记录 ID 的第一条记录
问题描述
我编写了一个包含多个连接语句的查询,并且只想选择顶部的“PT_FIN”,其中有多个相同的 PT_FIN。我在如何将只选择一个 PT_FIN 的代码嵌入到我已经编写的代码中时遇到问题。
我找到了一些链接,这些链接仅在选择顶行时提供了答案,但就像我说的那样,我在使这些答案在我的代码中起作用时遇到了问题:
USE EMTCQIData
DECLARE @StartDate Date
DECLARE @EndDate Date
Set @StartDate = '03/01/2018'
Set @EndDate = '03/25/2018'
Select ORD.PT_FIN, NOTE.Tracking_GROUP, NOTE.AUTHOR,
FORMAT(ORD.CHECKIN_DT_TM, 'MM/dd/yyyy') as DOV, ORD.Order_Mnemonic,
ORD.order_status,
CASE WHEN ORDER_MNEMONIC LIKE '%ketamine%' THEN 'YES' ELSE 'NO' END
[KETAMINE ORDERED], ORD.DOSE,
CASE
WHEN NOTE.RESULT LIKE '%99143%' THEN 'YES'
ELSE 'NO'
END BILLED_SEDATION,
CASE
WHEN NOTE2.RESULT LIKE '%Sedation%' THEN 'YES' Else 'NO' END
POWERNOTE_SEDATION
FROM [ED_Orders_Import_Master] AS ORD
INNER JOIN
(
Select *
FROM [ED_NOTES_MASTER] AS NOTE
Where RESULT_TITLE_TEXT = 'ED Physician Charges' AND RESULT_DT_TM >
@StartDate and RESULT_DT_TM < @EndDate
)
as NOTE ON NOTE.PT_FIN = ORD.PT_FIN
INNER JOIN
(
Select *
FROM [ED_NOTES_MASTER] AS NOTE2
Where NOTE_TYPE like '%PowerNote ED%' AND RESULT_DT_TM > @StartDate and
RESULT_DT_TM < @EndDate
)
as NOTE2 ON NOTE2.PT_FIN = ORD.PT_FIN
WHERE [Checkin_dt_tm] > @StartDate and [Checkin_dt_tm] < @EndDate AND
ORDER_MNEMONIC LIKE '%ketamine%' and ORIG_ORD_AS like '%Normal%' and
ORDER_STATUS like '%complete%'
ORDER by ORD.PT_FIN
我希望结果如下所示:
PT_FIN Order Billed Sedation Power Note Sedation
1 Ketamine yes Yes
2 Ketamine yes no
3 Ketamine yes Yes
解决方案
问题中缺少数据库架构和示例输入,但它可以是一种方法,因为您只需要验证注释的存在,我认为存在可以解决您的问题。如果您需要从相关子查询中获取多个值,应用也会有所帮助
DECLARE @StartDate Date
DECLARE @EndDate Date
Set @StartDate = '03/01/2018'
Set @EndDate = '03/25/2018'
Select ORD.PT_FIN, ORDER_MNEMONIC,
CASE WHEN EXISTS(SELECT 1 FROM [ED_NOTES_MASTER] WHERE RESULT_TITLE_TEXT = 'ED Physician' AND PT_FIN = ORD.PT_FIN AND RESULT LIKE '%99143%') THEN 'Yes' ELSE 'NO' END AS BILLED_SEDATION,
CASE WHEN EXISTS(SELECT 1 FROM [ED_NOTES_MASTER] WHERE NOTE_TYPE like '%PowerNote ED%' PT_FIN = ORD.PT_FIN AND RESULT LIKE '%Sedation%') THEN 'Yes' ELSE 'NO' END AS POWER_NOTE_SEDATION
FROM [ED_Orders_Import_Master] AS ORD
WHERE [Checkin_dt_tm] > @StartDate and [Checkin_dt_tm] < @EndDate AND
ORDER_MNEMONIC LIKE '%ketamine%' and ORIG_ORD_AS like '%Normal%' and
ORDER_STATUS like '%complete%'
ORDER by ORD.PT_FIN
仅在第一个时才使用 apply 来获取注释,我得到了您当前的方法,并将您的 INNER JOIN 转换为 TOP 1 的 OUTER APPLY 以完成您在标题中要求的内容
Select ORD.PT_FIN, NOTE.Tracking_GROUP, NOTE.AUTHOR,
FORMAT(ORD.CHECKIN_DT_TM, 'MM/dd/yyyy') as DOV, ORD.Order_Mnemonic,
ORD.order_status,
CASE WHEN ORDER_MNEMONIC LIKE '%ketamine%' THEN 'YES' ELSE 'NO' END
[KETAMINE ORDERED], ORD.DOSE,
CASE
WHEN NOTE.RESULT LIKE '%99143%' THEN 'YES'
ELSE 'NO'
END BILLED_SEDATION,
CASE
WHEN NOTE2.RESULT LIKE '%Sedation%' THEN 'YES' Else 'NO' END
POWERNOTE_SEDATION
FROM [ED_Orders_Import_Master] AS ORD
OUTER APPLY
(
Select TOP 1 *
FROM [ED_NOTES_MASTER]
Where PT_FIN = ORD.PT_FIN
ANd RESULT_TITLE_TEXT = 'ED Physician Charges' AND RESULT_DT_TM >
@StartDate and RESULT_DT_TM < @EndDate
ORDER BY RESULT_DT_TM
)
as NOTE
OUTER APPLY
(
Select TOP 1 *
FROM [ED_NOTES_MASTER]
Where PT_FIN = ORD.PT_FIN NOTE_TYPE like '%PowerNote ED%' AND RESULT_DT_TM > @StartDate and
RESULT_DT_TM < @EndDate
ORDER BY RESULT_DT_TM
)
WHERE [Checkin_dt_tm] > @StartDate and [Checkin_dt_tm] < @EndDate AND
ORDER_MNEMONIC LIKE '%ketamine%' and ORIG_ORD_AS like '%Normal%' and
ORDER_STATUS like '%complete%'
ORDER by ORD.PT_FIN
推荐阅读
- windbg - 如何在调用堆栈帧中查找对象/句柄正在等待“WaitForMultipleObjects”?
- r - 提高R(Rcpp)中4个嵌套for循环的速度?
- c# - 这会被认为是对状态机模式的滥用吗?
- sql - Oracle:如果 TABLE_A 存在,则返回 count(*),否则返回 0;
- python - 按属性对对象列表进行排序不会排序?
- firebase - Firebase AuthUI iOS 自定义
- ruby-on-rails - 如何使用 id 进行搜索,但在 React 的 url 链接中使用 slug
- postgresql - 如何在docker中使用flyway初始化postgres db?
- javascript - 如何将函数参数转换为键/值对?
- office365 - 订阅验证请求失败。响应必须与validationToken 查询参数完全匹配