本文介绍了一种复杂的SQL查询方法，该方法用于从合同和租赁信息表中选择特定字段，并通过条件筛选来获取高价值的合同记录。具体包括物业管理单位、物业名称、地址等详细信息，同时考虑了租金收入及合同的有效期。
#1
select json_value(b.data,'$.manageUnit') as manageUnit," +
"json_value(b.data,'$.propertyName') as propertyName," +
"json_value(c.data,'$.address') as address," +
"json_value(b.data,'$.customerName') as customerName," +
"json_value(b.data,'$.customerApplication') as customerApplication," +
"json_value(b.data,'$.managementArea') as managementArea," +
"nvl((select a.data.rent from rent a where a.data.contract=json_value(b.data,'$.contractNum') and a.data.datetimes like '%'||to_char(sysdate,'yyyy-mm')||'%'),0) as monthlyRent," +
"json_value(b.data,'$.annualIncrementRate') as annualIncrementRate," +
"json_value(b.data,'$.beginDate') as beginDate," +
"json_value(b.data,'$.endDate') as endDate" +
" from contract b left join rentalInfo c on json_value(b.data,'$.propertyNumber')=json_value(c.data,'$.num')" +
" where json_value(b.data,'$.state')='0' and (json_value(b.data,'$.managementArea')>1000 or json_value(b.data,'$.monthlyRent')*12>1000000)";
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
