SELECT gmrq,
SUM (zysl)
AS zysl,
SUM (tmsl)
AS tmsl,
SUM (qcsl)
AS qcsl,
SUM (qfsl)
AS qfsl,
SUM (xzsl)
AS xzsl,
SUM (jgsl)
AS jgsl,
SUM (wxzsl)
AS wxzsl
FROM (
-- 租用数
SELECT TO_CHAR (rq,
'yyyy')
AS gmrq,
COUNT (
*)
AS zysl,
0 AS tmsl,
0 AS qcsl,
0 AS qfsl,
0 AS xzsl,
0 AS jgsl,
0 AS wxzsl
FROM ywdjb
WHERE zxflag
<> '1' AND azfsdm
= '01'
GROUP BY TO_CHAR (rq,
'yyyy')
UNION ALL
--退墓数
SELECT TO_CHAR (rq,
'yyyy')
AS gmrq,
0 AS zysl,
COUNT (
*)
AS tmsl,
0 AS qcsl,
0 AS qfsl,
0 AS xzsl,
0 AS jgsl,
0 AS wxzsl
FROM ywdjb
WHERE zxflag
<> '1' AND azfsdm
= '01' AND zxflag
= '3'
GROUP BY TO_CHAR (rq,
'yyyy')
UNION ALL
--迁出数
SELECT TO_CHAR (rq,
'yyyy')
AS gmrq,
0 AS zysl,
0 AS tmsl,
COUNT (
*)
AS qcsl,
0 AS qfsl,
0 AS xzsl,
0 AS jgsl,
0 AS wxzsl
FROM ywdjb
WHERE zxflag
<> '1' AND azfsdm
= '01' AND zxflag
= '2'
GROUP BY TO_CHAR (rq,
'yyyy')
UNION ALL
--迁坟数量
SELECT TO_CHAR (rq,
'yyyy')
AS gmrq,
0 AS zysl,
0 AS tmsl,
0 AS qcsl,
COUNT (
*)
AS qfsl,
0 AS xzsl,
0 AS jgsl,
0 AS wxzsl
FROM ywdjb
WHERE zxflag
<> '1' AND azfsdm
= '01' AND qfflag
= '1'
GROUP BY TO_CHAR (rq,
'yyyy')
UNION ALL
--续租数量
SELECT TO_CHAR (t2.rq,
'yyyy')
AS gmrq,
0 AS zysl,
0 AS tmsl,
0 AS qcsl,
0 AS qfsl,
COUNT (t1.djh)
AS xzsl,
0 AS jgsl,
0 AS wxzsl
FROM (
SELECT djh,
ywdjh,
azfsdm,
ywmc,
zxflag
FROM qtywb t3
WHERE zxflag
<> '1'
AND (ywmc
= '续租' OR ywmc
LIKE '%改%')
AND rq
=
(SELECT MAX (rq)
FROM qtywb
WHERE t3.djh
= djh
AND zxflag
<> '1'
AND (ywmc
= '续租'
OR ywmc
LIKE '%改%'))) t1,
ywdjb t2
WHERE t1.djh
= t2.djh
AND t1.zxflag
<> '1'
AND t1.azfsdm
= '01'
AND t2.zxflag
<> '1'
AND t1.ywmc
= '续租'
GROUP BY TO_CHAR (t2.rq,
'yyyy')
UNION ALL
--旧改数量
SELECT TO_CHAR (t2.rq,
'yyyy')
AS gmrq,
0 AS zysl,
0 AS tmsl,
0 AS qcsl,
0 AS qfsl,
0 AS xzsl,
COUNT (t1.djh)
AS jgsl,
0 AS wxzsl
FROM (
SELECT djh,
ywdjh,
azfsdm,
ywmc,
zxflag
FROM qtywb t3
WHERE zxflag
<> '1'
AND (ywmc
= '续租' OR ywmc
LIKE '%改%')
AND rq
=
(SELECT MAX (rq)
FROM qtywb
WHERE t3.djh
= djh
AND zxflag
<> '1'
AND (ywmc
= '续租'
OR ywmc
LIKE '%改%'))) t1,
ywdjb t2
WHERE t1.djh
= t2.djh
AND t1.zxflag
<> '1'
AND t1.azfsdm
= '01'
AND t2.zxflag
<> '1'
AND t1.ywmc
LIKE '%改%'
GROUP BY TO_CHAR (t2.rq,
'yyyy')
UNION ALL
--已到期未续租名单
SELECT TO_CHAR (t1.rq,
'yyyy')
AS gmrq,
0 AS zysl,
0 AS tmsl,
0 AS qcsl,
0 AS qfsl,
0 AS xzsl,
0 AS jgsl,
COUNT (
*)
AS wxzsl
FROM ywdjb t1
WHERE t1.zxflag
= '0'
AND t1.azfsdm
= '01'
AND EXISTS
(SELECT *
FROM V_BS_CURRENTPERIOD
WHERE SYSDATE
> jsrq
AND t1.djh
= djh)
GROUP BY TO_CHAR (t1.rq,
'yyyy'))
GROUP BY gmrq
ORDER BY gmrq;
COMMENT ON TABLE V_NDXSBB
IS '年度销售报表';
转载于:https://www.cnblogs.com/siyunianhua/p/5627126.html
相关资源：万心房屋租赁管理软件(工业园版)
