如何使用存储过程连接2个表sql？ [英] how to join 2 tables sql using stored procedure?
本文介绍了如何使用存储过程连接2个表sql？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
更改 过程 Forms9（ @ mdvn int ， @ mphc varchar （ 4 ）， @ mhsc varchar （ 2 ）， @ fdate date ， @ tdate date ）
as *
BEGIN
声明 @ fedddate date
声明 @ tedddate date
声明 @ v 1date date
声明 @ v2date date
声明 @ v3date date
set @ v1date = dateadd（dd，-190，< span class =code-sdkkeyword> @ fdate ）
set @ v2date = dateadd（dd，-92， @ fdate ）
set @ v3date = dateadd（dd，-45， @fdate ）
set @ fedddate = dateadd（dd，-320， @ fdate ）
set @ tedddate = dateadd（dd， 30 ， @ tdate ）
SELECT DVN_CD，PHC_CD，HSC_CD，SUM（REGDT） AS REGDT，SUM（trims1） AS Trim1，sum（trims2） AS Trim2，
sum（ABORT） AS ABORT，sum（MTB） AS MTB，sum（LB） AS LB，sum（SB） AS SB，SUM（ANC1）< span class =code-keyword> as ANC1，SUM（ANC2） as ANC2，SUM（ANC3） as ANC3，
SUM（TT1） as TT1，sum（TTB2） as TT2，sum（IFA1） as IFA，sum（BP） as BP，sum（HB1） as HB FROM
（
SELECT a.DVN_CD，a.PHC_CD，a.HSC_CD，
CASE WHEN AN_Reg_Date BETWEEN @ fdate AND @tdate THEN 1 ELSE 0 END AS REGDT，
CASE WHEN DATEDIFF（DD，lmp，an_reg_date）< = 90 AND AN_Reg_Date BETWEEN @ fdate AND @ tdate 那么 1 ELSE 0 END AS trims1，
C ASE WHEN DATEDIFF（DD，lmp，an_reg_date）> 90 AND AN_Reg_Date BETWEEN @ fdate AND @ tdate 那么 1 ELSE 0 END AS trims2 ，
CASE WHEN Preg_outcome = 1 AND Deli_Date BETWEEN @ fdate AND @ tdate 那么 1 ELSE 0 END AS ABORT，
CASE WHEN Preg_outcome = 2 AND Deli_Date BETWEEN @ fdate AND @ tdate < span class =code-keyword> THEN 1 ELSE 0 END AS MTB，
CASE WHEN Preg_outcome = 3 AND Deli_Date BETWEEN @ fdate AND @tdate 那么 1 ELSE 0 END AS LB，
CASE WHEN Preg_outcome = 4 AND Deli_Date BETWEEN @ fdate AND @ tdate 那么 1 ELSE 0 END AS SB，
case 当 visit_date @ v1date < span class =code-keyword>和 @ tdate 然后 1 else 0 end as ANC1，
case visit_date @ v2date 和 @ tdate 然后 1 else 0 end as ANC2，
case 当 visit_date @ v3date < span class =code-keyword>和 @ tdate 然后 1 else 0 end as ANC3，
case 当 TTB = 1 和 TTDate 介于 @ fdate 和 @ tdate 然后 1 其他 0 end as TT1，
case TTB> = 2 和 TTDate @ fdate 和 @ tdate 然后 1 else 0 end as TTB2，
case 何时 IFA = 100 和 IFADate @ fdate 和 @ tdate 然后 1 else 0 结束 as IFA1，
case when BP> = ' 140/90' 和 ANEDD @ fdate 和 @ tdate 然后 1 else 0 end as BP，
case 当 HB< 11 和 ANEDD @时fdate 和 @ tdate 然后 1 else 0 end as HB1
FROM Preg_cohort3 a join ANVisits3 b on a.DVN_CD = b.DVN_CD 和 a.PHC_CD = b.PHC_CD 和 a.HSC_CD = b.HSC_CD 和 a.DVN_CD=@mdvn 和 a.PHC_CD=@mphc 和 a.HSC_CD=@mhsc
） group by a.dvn_cd ，a.phc_cd，a.hsc_cd
返回
END
致电：
exec Forms9 42 ，' 8301'，' 01'，' 2012/01/01'，' 2012/01/31'
我得到了输出结果......但结果不正确..我认为我在加入线上犯了一个错误..请任何人帮我...
我的原始输出是写的下面..但我得到了一些其他大值得到这个代码..
dvn_cd phc_cd hsc_cd Regdt Trim1 Trim2 Abort MTB LB SB ANC1 ANC2 ANC3 TT1 TT2 IFA BP HB
42 8301 01 0 0 0 0 0 2 0 119 16 0 0 0 0 0 1
解决方案
Iam不确定是什么输出你想要的。
看来你的SP非常复杂。如果你能给我更多的细节，我可以尝试解决更多问题。
但我觉得你需要改变你的查询是这样的。
chk希望这可以帮助您更好地查询您的查询。
更改 过程 Forms9（ @ mdvn int ， @ mphc varchar （ 4 ）， @ mhsc varchar （ 2 ）， @ fdate date ， @ tdate date ）
as *
BEGIN
声明 @fedddate date
声明 @tedddate date
声明 @ v1date 日期
声明 @ v2date date
声明 @ v3date date
set @ v1date = dateadd（dd，-190， @ fdate ）
set @ v2date = dateadd （dd，-92， @ fdate ）
set @ v3date = dateadd（dd， - 45， @ fdate ）
set @ fedddate = dateadd（dd，-320， @ fdate ）
set @ tedddate = dateadd（dd， 30 ， @ tdate ）
SELECT DVN_CD，PHC_CD ，HSC_CD，SUM（REGDT） AS REGDT，SUM（trims1） AS Trim1，sum（trims2） AS Trim2，
sum（ABORT） AS ABORT，sum（MTB） AS MTB，sum（LB） AS LB，sum（SB） AS SB，SUM（ANC1） ANC1，SUM（ANC2） ANC2，SUM（ANC3） ） as ANC3，
SUM（TT1） as TT1，sum（TTB2） as TT2，sum（IFA1） as IFA，sum（BP） as BP，sum（HB1） as HB FROM
（
SELECT a.DVN_CD，a.PHC_CD，a.HSC_CD，
CASE WHEN AN_Reg_Date 不 空 THEN 1 ELSE 0 END AS REGDT，
CASE WHEN DATEDIFF（DD，lmp，an_reg_date）< = 90 AND AN_Reg_Date 不 null < span class =code-keyword> THEN 1 ELSE 0 END AS trims1，
CASE WHEN DATEDIFF（DD，lmp，an_reg_date）> 90 AND AN_Reg_Date 不 null 那么 1 ELSE 0 END AS trims2，
CASE WHEN Preg_outcome = 1 AND Deli_Date 不 null 那么 1 ELSE 0 END AS ABORT，
CASE WHEN Preg_outcome = 2 AND Deli_Date 不 null 那么 1 ELSE 0 END AS MTB，
CASE WHEN Preg_outcome = 3 AND Deli_Date 不 null THEN 1 ELSE 0 END AS LB，
CASE WHEN Preg_outcome = 4 AND Deli_Date 不 null 那么 1 ELSE 0 END AS SB，
案例 何时 visit_date 是 不 null 然后 1 else 0 end as ANC1，
case 当 visit_date nullthen 1 其他 0 end as ANC2，
case < span class =code-keyword>当 visit_date 不 null 1 else 0 end as ANC3，
案例 当 TTB = 1 和 TTDate 不 null 1 else 0 结束 TT1，
案例 TTB> = 2 和 TTDate 不 null then 1 else 0 end as TTB2，
case 当 IFA = 100 和 IFADate 是 不 null 然后 1 else 0 end as IFA1，
case 当 BP> = ' 140/90 ' 和 ANEDDis 不 null 然后 1 else 0 end as BP，
case 何时 HB< 11 和 ANEDD 不 null 然后 1 else 0 end as HB1
FROM Preg_cohort3 a
内部 加入 ANVisits3 b
a.DVN_CD = b.DVN_CD 和 a.PHC_CD = b.PHC_CD 和 a.HSC_CD = b.HSC_CD
其中
a.DVN_CD=@mdvn 和 a.PHC_CD=@mphc 和 a.HSC_CD=@mhsc
和 AN_Reg_Date BETWEEN @ fdate AND @ tdate
） group by a.dvn_cd，a.phc_cd，a.hsc_cd
RETURN
END
Alter Procedure Forms9(@mdvn int,@mphc varchar(4),@mhsc varchar(2), @fdate date, @tdate date)
as*
BEGIN
declare @fedddate date
declare @tedddate date
declare @v1date date
declare @v2date date
declare @v3date date
set @v1date=dateadd(dd,-190,@fdate)
set @v2date=dateadd(dd,-92,@fdate)
set @v3date=dateadd(dd,-45,@fdate)
set @fedddate=dateadd(dd,-320,@fdate)
set @tedddate=dateadd(dd,30,@tdate)
SELECT DVN_CD,PHC_CD,HSC_CD,SUM(REGDT) AS REGDT,SUM(trims1)AS Trim1,sum(trims2) AS Trim2,
sum(ABORT) AS ABORT,sum(MTB) AS MTB,sum(LB)AS LB,sum(SB) AS SB,SUM(ANC1) as ANC1,SUM(ANC2) as ANC2,SUM(ANC3) as ANC3,
SUM(TT1) as TT1,sum(TTB2) as TT2,sum(IFA1) as IFA,sum(BP) as BP,sum(HB1) as HB FROM
(
SELECT a.DVN_CD,a.PHC_CD,a.HSC_CD,
CASE WHEN AN_Reg_Date BETWEEN @fdate AND @tdate THEN 1 ELSE 0 END AS REGDT,
CASE WHEN DATEDIFF(DD,lmp,an_reg_date)<=90 AND AN_Reg_Date BETWEEN @fdate AND @tdate THEN 1 ELSE 0 END AS trims1,
CASE WHEN DATEDIFF(DD,lmp,an_reg_date)>90 AND AN_Reg_Date BETWEEN @fdate AND @tdate THEN 1 ELSE 0 END AS trims2,
CASE WHEN Preg_outcome=1 AND Deli_Date BETWEEN @fdate AND @tdate THEN 1 ELSE 0 END AS ABORT,
CASE WHEN Preg_outcome=2 AND Deli_Date BETWEEN @fdate AND @tdate THEN 1 ELSE 0 END AS MTB,
CASE WHEN Preg_outcome=3 AND Deli_Date BETWEEN @fdate AND @tdate THEN 1 ELSE 0 END AS LB,
CASE WHEN Preg_outcome=4 AND Deli_Date BETWEEN @fdate AND @tdate THEN 1 ELSE 0 END AS SB,
case when visit_date between @v1date and @tdate then 1 else 0 end as ANC1,
case when visit_date between @v2date and @tdate then 1 else 0 end as ANC2,
case when visit_date between @v3date and @tdate then 1 else 0 end as ANC3,
case when TTB=1 and TTDate between @fdate and @tdate then 1 else 0 end as TT1,
case when TTB>=2 and TTDate between @fdate and @tdate then 1 else 0 end as TTB2,
case when IFA=100 and IFADate between @fdate and @tdate then 1 else 0 end as IFA1,
case when BP>='140/90' and ANEDD between @fdate and @tdate then 1 else 0 end as BP,
case when HB<11 and ANEDD between @fdate and @tdate then 1 else 0 end as HB1
FROM Preg_cohort3 a join ANVisits3 b on a.DVN_CD=b.DVN_CD and a.PHC_CD=b.PHC_CD and a.HSC_CD=b.HSC_CD and a.DVN_CD=@mdvn and a.PHC_CD=@mphc and a.HSC_CD=@mhsc
)a group by a.dvn_cd,a.phc_cd,a.hsc_cd
RETURN
END
Call:
exec Forms9 42,'8301','01','2012/01/01','2012/01/31'
I got the output result ... But that result is not correct.. I think i made a mistake on that joining line.. Pls any one help me...
My original output was written below.. but i got some other big values getting this code..
dvn_cd phc_cd hsc_cd Regdt Trim1 Trim2 Abort MTB LB SB ANC1 ANC2 ANC3 TT1 TT2 IFA BP HB
42 8301 01 0 0 0 0 0 2 0 119 16 0 0 0 0 0 1 解决方案
Iam Not Sure what output you want.
it seems your SP is very complex .If you could give me more detail and i can try to solve more.
but i think you need to change your query like this.
chk with this hope this will be help you to work out more on your query.
Alter Procedure Forms9(@mdvn int,@mphc varchar(4),@mhsc varchar(2), @fdate date, @tdate date) as* BEGIN declare @fedddate date declare @tedddate date declare @v1date date declare @v2date date declare @v3date date set @v1date=dateadd(dd,-190,@fdate) set @v2date=dateadd(dd,-92,@fdate) set @v3date=dateadd(dd,-45,@fdate) set @fedddate=dateadd(dd,-320,@fdate) set @tedddate=dateadd(dd,30,@tdate) SELECT DVN_CD,PHC_CD,HSC_CD,SUM(REGDT) AS REGDT,SUM(trims1)AS Trim1,sum(trims2) AS Trim2, sum(ABORT) AS ABORT,sum(MTB) AS MTB,sum(LB)AS LB,sum(SB) AS SB,SUM(ANC1) as ANC1,SUM(ANC2) as ANC2,SUM(ANC3) as ANC3, SUM(TT1) as TT1,sum(TTB2) as TT2,sum(IFA1) as IFA,sum(BP) as BP,sum(HB1) as HB FROM ( SELECT a.DVN_CD,a.PHC_CD,a.HSC_CD, CASE WHEN AN_Reg_Date is not null THEN 1 ELSE 0 END AS REGDT, CASE WHEN DATEDIFF(DD,lmp,an_reg_date)<=90 AND AN_Reg_Date is not null THEN 1 ELSE 0 END AS trims1, CASE WHEN DATEDIFF(DD,lmp,an_reg_date)>90 AND AN_Reg_Date is not null THEN 1 ELSE 0 END AS trims2, CASE WHEN Preg_outcome=1 AND Deli_Date is not null THEN 1 ELSE 0 END AS ABORT, CASE WHEN Preg_outcome=2 AND Deli_Date is not null THEN 1 ELSE 0 END AS MTB, CASE WHEN Preg_outcome=3 AND Deli_Date is not null THEN 1 ELSE 0 END AS LB, CASE WHEN Preg_outcome=4 AND Deli_Date is not null THEN 1 ELSE 0 END AS SB, case when visit_date is not null then 1 else 0 end as ANC1, case when visit_date is not nullthen 1 else 0 end as ANC2, case when visit_date is not null then 1 else 0 end as ANC3, case when TTB=1 and TTDate is not null then 1 else 0 end as TT1, case when TTB>=2 and TTDate is not null then 1 else 0 end as TTB2, case when IFA=100 and IFADate is not null then 1 else 0 end as IFA1, case when BP>='140/90' and ANEDDis not null then 1 else 0 end as BP, case when HB<11 and ANEDD is not null then 1 else 0 end as HB1 FROM Preg_cohort3 a Inner join ANVisits3 b on a.DVN_CD=b.DVN_CD and a.PHC_CD=b.PHC_CD and a.HSC_CD=b.HSC_CD Where a.DVN_CD=@mdvn and a.PHC_CD=@mphc and a.HSC_CD=@mhsc and AN_Reg_Date BETWEEN @fdate AND @tdate )a group by a.dvn_cd,a.phc_cd,a.hsc_cd RETURN END
这篇关于如何使用存储过程连接2个表sql？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
