17,377
社区成员
发帖
与我相关
我的任务
分享
select l.busi_sid,t.hfbh,l.subtype, subtype1,l.logtype,t.jbdwmc,t.hfnr,t.hfsj,t.hfr,t.ywbh,t.sid$
from t_jc_ms_busi_log l, t_jc_ms_hfb t where l.busi_sid = t.sid$
group by l.busi_sid, substr(l.subtype, 0, 2),l.logtype,t.hfbh,t.jbdwmc,t.hfnr,t.hfsj,t.hfr,t.ywbh,t.sid$
SELECT L.BUSI_SID,
T.HFBH,
L.SUBTYPE,
SUBSTR(L.SUBTYPE, 0, 2),
SUBTYPE,
L.LOGTYPE,
T.JBDWMC,
T.HFNR,
T.HFSJ,
T.HFR,
T.YWBH,
T.SID$,
SUM(DEOCDE(CURSTATUSTYPE, '1', 1, 0)) YJ,
SUM(DEOCDE(CURSTATUSTYPE, '2', 1, 0)) YYJ,
SUM(DEOCDE(CURSTATUSTYPE, '5', 1, 0)) RYJ,
SUM(DEOCDE(CURSTATUSTYPE, '8', 1, 0)) YP,
SUM(DEOCDE(CURSTATUSTYPE, '11', 1, 0)) RP,
MAX(CURSTATUSTYPE) AS CURSTATUSTYPE
FROM T_JC_MS_BUSI_LOG L, T_JC_MS_HFB T
WHERE L.BUSI_SID = T.SID$
AND L.SUBTYPE LIKE '21%%'
AND T.HFBH LIKE '%%'
AND T.HFR LIKE '%%'
GROUP BY L.BUSI_SID,
T.HFBH,
L.SUBTYPE,
SUBSTR(L.SUBTYPE, 0, 2),
SUBTYPE,
L.LOGTYPE,
T.JBDWMC,
T.HFNR,
T.HFSJ,
T.HFR,
T.YWBH,
T.SID$
select distinct l.busi_sid,t.hfbh, l.subtype, subtype,l.logtype,t.jbdwmc,t.hfnr,t.hfsj,t.hfr,t.ywbh,t.sid$,
sum(case when curstatustype='1' then 1 else 0 end ) yj,
sum(case when curstatustype='2' then 1 else 0 end ) yyj,
sum(case when curstatustype='5' then 1 else 0 end ) ryj,sum(case when curstatustype='8' then 1 else 0 end ) yp,
sum(case when curstatustype='11' then 1 else 0 end ) rp,max(curstatustype) as curstatustype
from t_jc_ms_busi_log l, t_jc_ms_hfb t where l.busi_sid = t.sid$ and l.subtype like '21%%'
and t.hfbh like '%%'
and t.hfr like '%%'
group by l.busi_sid,substr(l.subtype, 0, 2),l.logtype,t.hfbh,t.jbdwmc,t.hfnr,t.hfsj,t.hfr,t.ywbh,t.sid$