select
temp.serialNumber AS "bzDeceased.serialNumber",
temp.actualName AS "bzDeceased.actualName",
temp.deceId AS "bzDeceased.id",--这个是我现在需要获取的值,所以在这个页面执行sql的时候我查出来了
temp.no as "createBy.no",
temp.totalPrice as "allTotalPrice",
temp.beginFuneralTime as "bzYsgl.beginFuneralTime"
from
(
SELECT
dece.serial_number AS "serialNumber",
dece.actual_name AS "actualName",
dece.id as "deceId",
ysgl.begin_funeral_time AS "beginFuneralTime",
sysUser.no AS "no",SUM(rela.unit_price*rela.prod_count) AS "totalPrice"
FROM
bz_consumelist_project_relation rela
left join bz_negotiation_consumelist nego on nego.id = rela.consumelist_id and nego.del_flag ='0'
left join bz_deceased dece on dece.id = nego.deceased_id and dece.del_flag ='0'
left join sys_user sysUser on sysUser.id = dece.create_by and sysUser.del_flag ='0'
join bz_ysgl ysgl on ysgl.bz_deceased_id = dece.id and ysgl.del_flag ='0'
where
rela.del_flag ='0'
AND rela.`status` !='5'
AND rela.`status`!='1'
GROUP BY dece.serial_number,dece.actual_name,sysUser.no
) temp
where temp.totalPrice >20000<if test="bzYsgl != null"><choose><when test="bzYsgl.beginBzYsglDate != null and bzYsgl.beginBzYsglDate != '' and bzYsgl.endBzYsglDate != null and bzYsgl.endBzYsglDate != ''">
and (temp.beginFuneralTime between #{bzYsgl.beginBzYsglDate} and #{bzYsgl.endBzYsglDate})</when><otherwise><if test="bzYsgl.beginBzYsglDate != null and bzYsgl.beginBzYsglDate != ''">
AND temp.beginFuneralTime = #{bzYsgl.beginBzYsglDate}</if><if test="bzYsgl.endBzYsglDate != null and bzYsgl.endBzYsglDate != ''">
AND temp.beginFuneralTime = #{bzYsgl.endBzYsglDate}</if></otherwise></choose></if><if test="bzDeceased != null"><if test="bzDeceased.serialNumber != null and bzDeceased.serialNumber != ''">
and temp.serialNumber like
concat('%',#{bzDeceased.serialNumber},'%')</if></if>
GROUP BY temp.serialNumber,temp.actualName
ORDER BY temp.no