java - 不在子查询中更改为 HQL 中的左外连接

问题描述

我有以下 HQL

select distinct(ordr) 
from OfOrder ordr, 
SalesChannel sc  
where  
   ordr.status in (:statusList) and 
   ordr.ofOrderId not in (
          select ordrLabel2.ofOrder.ofOrderId 
          from OrderLabel ordrLabel2 
          where 
             ordrLabel2.ofLabel.labelId in ( :ofLabels )
          ) and 
  ordr.orderMain.salesChannel.channelId in (:channelIds)  and 
  ordr.deliveryChannel.deliveryChannelId in (:deliveryChannels) 
order by ordr.deliveryDate asc,   
CASE   
  when ordr.status = 'SU' then 1 
  when ordr.status = 'SU' then 1 
  when ordr.status = 'SU' then 1 
  when ordr.status = 'AC' then 2 
  when ordr.status = 'AC' then 2 
  when ordr.status = 'AC' then 2 
  when ordr.status = 'AC' then 3 
  when ordr.status = 'AC' then 4 
  when ordr.status = 'AC' then 4 
  when ordr.status = 'SH' then 5 
  when ordr.status = 'SH' then 5 
  when ordr.status = 'DD' then 6 
  when ordr.status = 'R' then 7 
  when ordr.status = 'C' then 8 
  ELSE 9999    
END

Hibernate 为相同的生成了以下查询

select distinct oforder0_.`of_order_id`
as of_order1_27_, oforder0_.`assigned_to`
as assigned2_27_, oforder0_.`box_size`
as box_size3_27_, oforder0_.`boxes`
as boxes4_27_, oforder0_.`created_date`
as created_5_27_, oforder0_.`delivery_channel_id`
as deliver25_27_, oforder0_.`delivery_channel_order_id`
as delivery6_27_, oforder0_.`delivery_date`
as delivery7_27_, oforder0_.`delivery_labelled`
as delivery8_27_, oforder0_.`delivery_status`
as delivery9_27_, oforder0_.`description`
as descrip10_27_, oforder0_.`display_name`
as display11_27_, oforder0_.`fulfillment_center_id`
as fulfill26_27_, oforder0_.`kitchen_labelled`
as kitchen12_27_, oforder0_.`message`
as message13_27_, oforder0_.`group_id`
as group_i27_27_, oforder0_.`shipping_address_id`
as shippin28_27_, oforder0_.`order_main_id`
as order_m29_27_, oforder0_.`order_type`
as order_t14_27_, oforder0_.`packing_priority`
as packing15_27_, oforder0_.`quantity`
as quantit16_27_, oforder0_.`requiresShipping`
as require17_27_, oforder0_.`shipping_line_id`
as shippin30_27_, oforder0_.`split_number`
as split_n18_27_, oforder0_.`status`
as status19_27_, oforder0_.`total_price`
as total_p20_27_, oforder0_.`tracking_id`
as trackin21_27_, oforder0_.`updated_date`
as updated22_27_, oforder0_.`vendor_id`
as vendor_23_27_, oforder0_.`vendor_name`
as vendor_24_27_
from `of_order` oforder0_
cross join `sales_channel` saleschann1_
    cross join `order_main` ordermain3_
where oforder0_.`order_main_id`=ordermain3_.`order_main_id`
        and (oforder0_.`status` in ('SU' , 'AC'))
        and (oforder0_.`of_order_id`
        not in  (
select orderlabel2_.`of_order_id`
            from `order_label` orderlabel2_
            where orderlabel2_.`label_id`
            in (29 , 37)
            )
)
and (ordermain3_.`channel_id`
in (1 , 4)
            )
        and (oforder0_.`delivery_channel_id`
in (2 , 5)
            )
order by oforder0_.`delivery_date` asc,
    case
when oforder0_.`status`='SU' then 1
        when oforder0_.`status`='SU' then 1
        when oforder0_.`status`='SU' then 1
        when oforder0_.`status`='AC' then 2
        when oforder0_.`status`='AC' then 2
        when oforder0_.`status`='AC' then 2
        when oforder0_.`status`='AC' then 3
        when oforder0_.`status`='AC' then 4
        when oforder0_.`status`='AC' then 4
        when oforder0_.`status`='SH' then 5
        when oforder0_.`status`='SH' then 5
        when oforder0_.`status`='DD' then 6
        when oforder0_.`status`='R' then 7
        when oforder0_.`status`='C' then 8
        else 9999
end
limit 50, 50;

上面的查询给了我我需要的确切结果。但我认为连接查询会提高性能。如果没有，请纠正我。如何做到这一点我需要将子查询部分更改为左外连接

（从 OrderLabel ordrLabel2 中选择 ordrLabel2.ofOrder.ofOrderId where ordrLabel2.ofLabel.labelId in ( :ofLabels )

如何通过外连接达到相同的结果。我需要一个带有左外连接的上述结果的 HQL 有人请帮我实现这个

标签： javamysqlleft-joinhql

java - 不在子查询中更改为 HQL 中的左外连接

问题描述

解决方案

推荐阅读