HRZ

查询sql（临时）

查询业主总数：

select count(*) from ygzy_owner where delete_time is null

查询每个省份下的项目数量：

SELECT
        a.id,a.province_code,a.province_name,sum(a.village_num) as village_num
FROM
        (SELECT id,province_code,province_name,(SELECT count(*) from ygzy_village where ygzy_village.area_area_id = ygzy_area.id) as village_num from ygzy_area) AS a
GROUP BY
        a.province_code

查询合计房源：

SELECT count(*) FROM `ygzy_rooms` where delete_time is null

查询已交房数：

SELECT count(*) FROM `ygzy_rooms` where `check` = 1 and delete_time is null

查询交房占比（保留两位小数结果）：

SELECT ROUND(ROUND((SELECT count(*) FROM `ygzy_rooms` where `check` = 1 and delete_time is null)/(SELECT count(*) FROM `ygzy_rooms` where delete_time is null),4)*100,2)

查询每个大区的现金流：

SELECT a.deptname,sum(a.sn_money) FROM (SELECT
    d_a.deptid,
    d_a.deptname,
    v.id AS v_id,
    v.villagename AS v_name,
    (SELECT sum(s.money) FROM ygzy_sn as s where s.vid = v.id and s.`status` <> 0 and s.pstatus = 1 and s.delete_time is null) as sn_money
FROM
    ygzy_department AS d_a
    INNER JOIN ygzy_department d_c ON d_c.pid = d_a.deptid
    INNER JOIN ygzy_department d_p ON d_p.pid = d_c.deptid
    INNER JOIN ygzy_village v ON v.did = d_p.deptid) as a GROUP BY a.deptid
//这里status不等于0是表示已缴费
//这里pstatus 等于1是表示未返销

查询指定时间范围内的每个大区的现金流：

set @ss_starttime = 1648742400;//实收开始时间
set @ss_endtime = 1651334399;//实收结束时间
SELECT a.deptname,sum(a.sn_money) FROM (SELECT
    d_a.deptid,
    d_a.deptname,
    v.id AS v_id,
    v.villagename AS v_name,
    (SELECT sum(s.money) FROM ygzy_sn as s where s.vid = v.id and s.`status` <> 0 and s.pstatus = 1 and s.delete_time is null and s.pay_time >= @ss_starttime and s.pay_time <= @ss_endtime) as sn_money
FROM
    ygzy_department AS d_a
    INNER JOIN ygzy_department d_c ON d_c.pid = d_a.deptid
    INNER JOIN ygzy_department d_p ON d_p.pid = d_c.deptid
    INNER JOIN ygzy_village v ON v.did = d_p.deptid) as a GROUP BY a.deptid
//这里pay_time是实收时间，格式是时间戳

查询指定时间范围内的指定大区的现金流：

set @ss_starttime = 1648742400;//实收开始时间
set @ss_endtime = 1651334399;//实收结束时间
SELECT a.deptname,sum(a.sn_money) FROM (SELECT
    d_a.deptid,
    d_a.deptname,
    v.id AS v_id,
    v.villagename AS v_name,
    (SELECT sum(s.money) FROM ygzy_sn as s where s.vid = v.id and s.`status` <> 0 and s.pstatus = 1 and s.delete_time is null and s.pay_time >= @ss_starttime and s.pay_time <= @ss_endtime) as sn_money
FROM
    ygzy_department AS d_a
    INNER JOIN ygzy_department d_c ON d_c.pid = d_a.deptid
    INNER JOIN ygzy_department d_p ON d_p.pid = d_c.deptid
    INNER JOIN ygzy_village v ON v.did = d_p.deptid WHERE d_a.deptid = 40) as a GROUP BY a.deptid
//这里deptid是大区id

查询大区收缴率：

set @ys_starttime_ym = 202201;//应收开始时间（年月格式）
set @ys_endtime_ym = 202205;//应收结束时间（年月格式）
set @ss_starttime = 1640966400;//实收开始时间（时间戳）
set @ss_endtime = 1650443547;//实收结束时间（时间戳）
set @subject_id = '16,17,18,19,20';//要查询的系统的科目id
SELECT a.deptname,ROUND(ROUND(sum(a.ss_money)/sum(a.ys_money),4)*100,2) as collection_rate FROM (SELECT
    d_a.deptid,
    d_a.deptname,
    v.id AS v_id,
    v.villagename AS v_name,
    (SELECT sum(c.money) FROM ygzy_preview_cost as c where c.vid = v.id and c.delete_time is null and c.ymonth >= @ys_starttime_ym and c.ymonth <= @ys_endtime_ym and c.subject_id in (SELECT sv.id FROM ygzy_subject_village as sv WHERE sv.vid = v.id and sv.subject_id in (@subject_id))) as ys_money,
    (SELECT sum(c.money) FROM ygzy_preview_cost as c where c.vid = v.id and c.delete_time is null and c.ymonth >= @ys_starttime_ym and c.ymonth <= @ys_endtime_ym and c.subject_id in (SELECT sv.id FROM ygzy_subject_village as sv WHERE sv.vid = v.id and sv.subject_id in (@subject_id)) and c.`status` <> 0 and c.pay_time >= @ss_starttime and c.pay_time <= @ss_endtime) as ss_money
FROM
    ygzy_department AS d_a
    INNER JOIN ygzy_department d_c ON d_c.pid = d_a.deptid
    INNER JOIN ygzy_department d_p ON d_p.pid = d_c.deptid
    INNER JOIN ygzy_village v ON v.did = d_p.deptid WHERE d_a.deptid = 40) as a GROUP BY a.deptid
//这里加上了“d_a.deptid = 40”是只查询了一个大区的数据。如果要查询所有大区，就把这个where条件去掉
//ymonth这个字段是费用归属月
//subject_id这个字段是费用所属科目id
//这里subject_id是根据系统的总科目id来查询到每个项目的科目

查询大区的清欠率：

set @ys_endtime = 1640966399;//应收结束时间时间戳（上一年的最后一天）
set @ys_endtime_ym = 202112;//应收结束时间年月格式（上一年的最后一天）
set @ss_starttime = 1640966400;//实收开始时间（本年第一天）
set @ss_endtime = 1650445075;//实收结束时间（当前时间戳）
set @subject_id = '16,17,18,19,20';//要查询的系统的科目id
SELECT a.deptname,ROUND(ROUND(sum(a.ss_money)/sum(a.ys_money),4)*100,2) as collection_rate FROM (SELECT
    d_a.deptid,
    d_a.deptname,
    v.id AS v_id,
    v.villagename AS v_name,
    (SELECT sum(money) FROM ygzy_preview_cost as c WHERE c.vid = v.id and c.delete_time is null and (c.`status` = 0 or (c.`status` <> 0 and c.pay_time > @ys_endtime)) and c.ymonth <= @ys_endtime_ym and c.subject_id in (SELECT sv.id FROM ygzy_subject_village as sv WHERE sv.vid = v.id and sv.subject_id in (@subject_id))) as ys_money,
    (SELECT sum(money) FROM ygzy_preview_cost as c WHERE c.vid = v.id and c.delete_time is null and (c.`status` = 0 or (c.`status` <> 0 and c.pay_time > @ys_endtime)) and c.ymonth <= @ys_endtime_ym and c.subject_id in (SELECT sv.id FROM ygzy_subject_village as sv WHERE sv.vid = v.id and sv.subject_id in (@subject_id)) and c.`status` <> 0 and c.pay_time >= @ss_starttime and c.pay_time <= @ss_endtime) as ss_money
FROM
    ygzy_department AS d_a
    INNER JOIN ygzy_department d_c ON d_c.pid = d_a.deptid
    INNER JOIN ygzy_department d_p ON d_p.pid = d_c.deptid
    INNER JOIN ygzy_village v ON v.did = d_p.deptid WHERE d_a.deptid = 40) as a GROUP BY a.deptid