SQl为：
select temp.aa as 日期,temp.bb as DC,temp.cc as 品类,temp.ee as 品牌,temp.ff as "收货能力(箱)",temp.gg as "收货能力(体积)",temp.hh as 已预约箱数,temp.jj as 已预约体积 from
(
select (select trunc(sysdate) from users where rownum =1) aa,dc.dc_name bb,dd.category cc,dd.brand ee,dd.average_receive_capacity ff,dd.average_receive_volume gg,
nvl((select sum(o.box_quantity) from log2_appoint_carrier_order o
left join log2_appoint_carrier c on c.id=o.appoint_carrier_id
left join log_base_organization org on org.id=c.receiver_id
inner join log_receive_ndc_order ndc on ndc.purchasecode=o.purchase_code and ndc.confirmcode=o.confirm_code and nvl(ndc.selfordercode,'a')=nvl(o.self_build_code,'a')
where trunc(o.appoint_receive_date)=trunc(sysdate) and org.org_name=dc.dc_name and o.status='APPROVED' and ndc.categorys=dd.category),0) hh,
nvl((select sum(o.volume) from log2_appoint_carrier_order o
left join log2_appoint_carrier c on c.id=o.appoint_carrier_id
left join log_base_organization org on org.id=c.receiver_id
inner join log_receive_ndc_order ndc on ndc.purchasecode=o.purchase_code and ndc.confirmcode=o.confirm_code and nvl(ndc.selfordercode,'a')=nvl(o.self_build_code,'a')
where trunc(o.appoint_receive_date)=trunc(sysdate) and org.org_name=dc.dc_name and o.status='APPROVED' and ndc.categorys=dd.category),0) jj
from log_base_dc_infodetail dd
inner join log_base_dc_info dc on dc.id=dd.dcinfo_id and dc.dc_type='NDC'
where dd.ability_start_time<trunc(sysdate) and dd.ability_end_time>trunc(sysdate) and dd.status='ACTIVE'
union all
select (select trunc(sysdate+1) from users where rownum <=1) aa,dc.dc_name bb,dd.category cc,dd.brand ee,dd.average_receive_capacity ff,dd.average_receive_volume gg,
nvl((select sum(o.box_quantity) from log2_appoint_carrier_order o
left join log2_appoint_carrier c on c.id=o.appoint_carrier_id
left join log_base_organization org on org.id=c.receiver_id
inner join log_receive_ndc_order ndc on ndc.purchasecode=o.purchase_code and ndc.confirmcode=o.confirm_code and nvl(ndc.selfordercode,'a')=nvl(o.self_build_code,'a')
where trunc(o.appoint_receive_date)=trunc(sysdate+1) and org.org_name=dc.dc_name and o.status='APPROVED' and ndc.categorys=dd.category),0) hh,
nvl((select sum(o.volume) from log2_appoint_carrier_order o
left join log2_appoint_carrier c on c.id=o.appoint_carrier_id
left join log_base_organization org on org.id=c.receiver_id
inner join log_receive_ndc_order ndc on ndc.purchasecode=o.purchase_code and ndc.confirmcode=o.confirm_code and nvl(ndc.selfordercode,'a')=nvl(o.self_build_code,'a')
where trunc(o.appoint_receive_date)=trunc(sysdate+1) and org.org_name=dc.dc_name and o.status='APPROVED' and ndc.categorys=dd.category),0) jj
from log_base_dc_infodetail dd
inner join log_base_dc_info dc on dc.id=dd.dcinfo_id and dc.dc_type='NDC'
where dd.ability_start_time<trunc(sysdate+1) and dd.ability_end_time>trunc(sysdate+1) and dd.status='ACTIVE'
)temp
group by temp.aa,temp.bb,temp.cc,temp.ee,temp.ff,temp.gg,temp.hh,temp.jj order by temp.aa asc
下面是转换为HQL的语句：
SELECT temp.aa as businessDate,temp.bb as dcName,temp.cc as category,temp.ee as brand,temp.ff as averageReceiveCapacity,temp.gg as averageReceiveVolume,temp.hh as boxQuantity,temp.jj as volume
FROM (SELECT
(SELECT trunc(sysdate) from User where rownum=1) as aa,dc.name as bb,dd.category as cc,dd.brand as ee,dd.averageReceiveCapacity as ff,dd.averageReceiveVolume as gg,
nvl((SELECT sum(o.boxQuantity) from AppointmentCarrierOrder o,NDCOrder ndc
left join o.appointmentCarrier c
left join c.receiver org where trunc(o.appointReceiveDate)=trunc(sysdate) and org.name=dc.name and o.status='APPROVED'