INSERT INTO BUSINESS_EX_INFO
SELECT SEQ_BUSINESSEXINFO_ID.NEXTVAL,ID,
16,
'是' FROM qtywb
where (ywdm
='12' OR YWDM
='14')
and zxflag
<>'1';
--用碑数据--
SELECT ssrq, mx,
sum(zys)
as zys,
sum(jgs)
as jgs,
sum(BQTWYBS)
as BQTWYBS,
sum(BQTYYBS)
as BQTYYBS,
sum(BQTMS)
as BQTMS,
sum(FBQTWYBS)
as FBQTWYBS,
sum(FBQTYYBS)
as FBQTYYBS,
sum(FBQTMS)
as FBQTMS
FROM (
--租用--
SELECT TO_CHAR(rq,
'yyyymm')
as ssrq,mxmc
as mx,
count(
*)
AS ZYS,
0 AS JGS,
0 AS BQTWYBS,
0 AS BQTYYBS,
0 AS BQTMS,
0 AS FBQTWYBS,
0 AS FBQTYYBS,
0 AS FBQTMS
FROM ywdjb
WHERE zxflag
<>'1' AND to_char(rq,
'yyyy-mm-dd')
>='1900-05-26' AND to_char(rq,
'yyyy-mm-dd')
<='2016-06-30' GROUP BY mxmc,TO_CHAR(rq,
'yyyymm')
UNION ALL
--旧墓改造--
SELECT TO_CHAR(rq,
'yyyymm')
as ssrq,mxmc
as mx,
0,
count(
*),
0,
0,
0,
0,
0,
0 FROM qtywb
WHERE zxflag
<>'1' AND ywdm
='14' AND to_char(rq,
'yyyy-mm-dd')
>='1900-05-26' AND to_char(rq,
'yyyy-mm-dd')
<='2016-06-30' GROUP BY mxmc,TO_CHAR(rq,
'yyyymm')
UNION ALL
--本期退--
SELECT TO_CHAR(rq,
'yyyymm')
as ssrq,mxmc
as mx,
0,
0,
0,
0,
count(
*),
0,
0,
0 FROM qtywb t1
WHERE zxflag
<>'1' AND ywdm
='12' AND djh
IN(
SELECT djh
FROM ywdjb
WHERE t1.djh
=djh
and to_char(rq,
'yyyy-mm-dd')
>='1900-05-26' AND to_char(rq,
'yyyy-mm-dd')
<='2016-06-30')
GROUP BY mxmc,TO_CHAR(rq,
'yyyymm')
UNION ALL
--非本期退--
SELECT TO_CHAR(rq,
'yyyymm')
as ssrq,mxmc
as mx,
0,
0,
0,
0,
0,
0,
0,
count(
*)
FROM qtywb t1
WHERE zxflag
<>'1' AND ywdm
='12' AND djh
NOT IN(
SELECT djh
FROM ywdjb
WHERE t1.djh
=djh
and to_char(rq,
'yyyy-mm-dd')
>='1900-05-26' AND to_char(rq,
'yyyy-mm-dd')
<='2016-06-30')
GROUP BY mxmc,TO_CHAR(rq,
'yyyymm')
UNION ALL
--本期退未用碑数--
SELECT TO_CHAR(rq,
'yyyymm')
as ssrq,mxmc
as mx,
0,
0,
count(
*),
0,
0,
0,
0,
0 FROM qtywb t1,BUSINESS_EX_INFO t2
WHERE t1.zxflag
<>'1' AND t1.ywdm
='12' and t2.BUSINESS_ID
=t1.id
and PROPERTY_ID
=16 and PROPERTY_VALUE
='否' AND DJH
IN(
SELECT djh
FROM ywdjb
WHERE t1.djh
=djh
and to_char(rq,
'yyyy-mm-dd')
>='1900-05-26' AND to_char(rq,
'yyyy-mm-dd')
<='2016-06-30')
GROUP BY mxmc,TO_CHAR(rq,
'yyyymm')
UNION ALL
--本期退已用碑数--
SELECT TO_CHAR(rq,
'yyyymm')
as ssrq,mxmc
as mx,
0,
count(
*),
0,
0,
0,
0,
0,
0 FROM qtywb t1,BUSINESS_EX_INFO t2
WHERE t1.zxflag
<>'1' AND t1.ywdm
='12' and t2.BUSINESS_ID
=t1.id
and PROPERTY_ID
=16 and PROPERTY_VALUE
='是' AND DJH
IN(
SELECT djh
FROM ywdjb
WHERE t1.djh
=djh
and to_char(rq,
'yyyy-mm-dd')
>='1900-05-26' AND to_char(rq,
'yyyy-mm-dd')
<='2016-06-30')
GROUP BY mxmc,TO_CHAR(rq,
'yyyymm')
UNION ALL
--非本期退未用碑数--
SELECT TO_CHAR(rq,
'yyyymm')
as ssrq,mxmc
as mx,
0,
0,
0,
0,
0,
count(
*),
0,
0 FROM qtywb t1,BUSINESS_EX_INFO t2
WHERE t1.zxflag
<>'1' AND t1.ywdm
='12' and t2.BUSINESS_ID
=t1.id
and PROPERTY_ID
=16 and PROPERTY_VALUE
='否' AND DJH
NOT IN(
SELECT djh
FROM ywdjb
WHERE t1.djh
=djh
and to_char(rq,
'yyyy-mm-dd')
>='1900-05-26' AND to_char(rq,
'yyyy-mm-dd')
<='2016-06-30')
GROUP BY mxmc,TO_CHAR(rq,
'yyyymm')
UNION ALL
--非本期退已用碑数--
SELECT TO_CHAR(rq,
'yyyymm')
as ssrq,mxmc
as mx,
0,
0,
0,
0,
0,
0,
count(
*),
0 FROM qtywb t1,BUSINESS_EX_INFO t2
WHERE t1.zxflag
<>'1' AND t1.ywdm
='12' and t2.BUSINESS_ID
=t1.id
and PROPERTY_ID
=16 and PROPERTY_VALUE
='是' AND DJH
NOT IN(
SELECT djh
FROM ywdjb
WHERE t1.djh
=djh
and to_char(rq,
'yyyy-mm-dd')
>='1900-05-26' AND to_char(rq,
'yyyy-mm-dd')
<='2016-06-30')
GROUP BY mxmc,TO_CHAR(rq,
'yyyymm')
) group by mx,ssrq;
转载于:https://www.cnblogs.com/siyunianhua/p/5627131.html
相关资源：JAVA上百实例源码以及开源项目
