如何在sql中为列字段获得两行差异 [英] how to get difference in two rows for a column field in sql
本文介绍了如何在sql中为列字段获得两行差异的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
Hello
我有一个表
创建表#bp_patient65(id int identity(1,1),patient_id int,bp_type varchar(50),encounter_id int,BP varchar(50))
插入#bp_patient65值(64874,'Systolic',146019,150),(64874,'Systolic',146020,130) ,
(64875,'收缩',146022,150),(64876,'收缩',146025,150),
(64876,'收缩',146026,130),(64877,'收缩压',146028,150)
select * from#bp_patient65
drop table #bp_patient65
i想要我的输出就像
patient_id- --- bp_type ------ encounter_id ----- BP -------- diff
64874 ----------收缩期----- 146019-- ------ 150 ----- 0
64874 ----------收缩期----- 146020 -------- 130 ----- 20
64875 ----------收缩期----- 146022 -------- 150 ----- 0
64876 -------- - 收缩期----- 146025 -------- 150 ----- 0
64876 ----------收缩期----- 146026 ---- ---- 130 ----- 20
64877 ----------收缩期----- 146028 -------- 150 ----- 0
我试过这个但是没有做对
SELECT patient_id,bp_type,encounter_id,BP,
COALESCE (
(
SELECT TOP 1 BP
FROM #bp_patient65 mi
WHERE mi.patient_id> m.patient_id
group by BP,mi.patient_id
ORDER BY patient_id
), 0 ) - BP AS diff
FROM #bp_patient65 m
组 按 patient_id,bp_type,encounter_id,BP
ORDER < span class =code-keyword> BY
patient_id
解决方案
查看此
选择 a.patient_id,a.bp_type,a.encounter_id,a.BP, DIFF = iSNULL(SUM(B.BP-A.BP), 0 )来自
#bp_patient65 a
lEFT 加入
#bp_patient65 b On a.patient_id = b.patient_id AND B.ID
组 按 a.patient_id,a.bp_type,a.encounter_id,a.BP
SELECT patient_id,bp_type,encounter_id,BP,
COALESCE (( SELECT TOP 1 BP FROM #bp_patient65 mi
WHERE mi.patient_id = m.patient_id ORDER BY patient_id), 0 ) - BP AS diffrence
FROM #bp_patient65 m group 按 patient_id,bp_type,encounter_id,BP ORDER BY patient_id
Hello
I have a table
create table #bp_patient65(id int identity(1,1),patient_id int,bp_type varchar(50),encounter_id int,BP varchar(50))
insert into #bp_patient65 values(64874,'Systolic',146019,150),(64874,'Systolic',146020,130),
(64875,'Systolic',146022,150),(64876,'Systolic',146025,150),
(64876,'Systolic',146026,130),(64877,'Systolic',146028,150)
select * from #bp_patient65
drop table #bp_patient65
i want my output like that
patient_id---- bp_type------ encounter_id----- BP--------diff
64874---------- Systolic----- 146019-------- 150----- 0
64874---------- Systolic----- 146020-------- 130----- 20
64875---------- Systolic----- 146022-------- 150----- 0
64876---------- Systolic----- 146025-------- 150----- 0
64876---------- Systolic----- 146026-------- 130----- 20
64877---------- Systolic----- 146028-------- 150----- 0
I tried this but not getting right
SELECT patient_id, bp_type,encounter_id,BP,
COALESCE(
(
SELECT TOP 1 BP
FROM #bp_patient65 mi
WHERE mi.patient_id > m.patient_id
group by BP,mi.patient_id
ORDER BY patient_id
), 0) - BP AS diff
FROM #bp_patient65 m
group by patient_id,bp_type,encounter_id,BP
ORDER BY
patient_id
解决方案
check this
select a.patient_id,a.bp_type,a.encounter_id,a.BP,DIFF=iSNULL(SUM(B.BP-A.BP),0) from #bp_patient65 a lEFT Join #bp_patient65 b On a.patient_id=b.patient_id AND B.ID Group by a.patient_id,a.bp_type,a.encounter_id,a.BP
SELECT patient_id, bp_type,encounter_id,BP, COALESCE((SELECT TOP 1 BP FROM #bp_patient65 mi WHERE mi.patient_id = m.patient_id ORDER BY patient_id ), 0) - BP AS diffrence FROM #bp_patient65 m group by patient_id,bp_type,encounter_id,BP ORDER BY patient_id
这篇关于如何在sql中为列字段获得两行差异的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文