关联查询传参简单示例

select * from b_stationregiontree s LEFT JOIN b_station_dict b ON s.station_code = b.s_station_telecode where b.s_id = :stationId

 

 例1、（JPA）

 @Query(value = "select s.id,s.rela_tree_id,s.parent_id,s.inf_value,s.inf_code,s.inf_type,s.inf_name,s.station_name,s.inf_mark,s.rela_tree_state,s.station_code from b_stationregiontree s LEFT JOIN b_station_dict b ON s.station_code = b.s_station_telecode where b.s_id = :stationId and s.parent_id = \"1\";", nativeQuery = true)
List<StationRegionTree> findStationByIds(@Param("stationId") String stationId);

 

 例2、（原生）

select sr.* from b_stationregiontree sr LEFT JOIN b_station_dict b ON sr.station_code = b.s_station_telecode where (sr.inf_name) = '西安' and b.s_id = '8a83092878c8f7fd0178c9313e360000'

 

 

 例3、

@Query(value="select d.s_id as id, d.s_name as name, d.s_device_sn as device_sn, d.s_no as no, d.s_specification as specification, d.s_type as type, d.d_enable_date as enable_date, d.d_disable_date as disable_date, d.i_period as period,\n" +
        "d.i_status as status, d.s_address as address, dsr.inf_name as region, dsr.id as region_id, dsrp.inf_name as region_type, dsrp.id as region_type_id, dmf.s_name as manufacture, dmf.s_id as manufacture_id, ddc.s_name as device_sub_category, ddc.s_id as device_sub_category_id,\n" +
        "ddcp.s_name as device_category, ddcp.s_id as device_category_id, d.s_device_code as device_code, d.s_device_params as specialParas, d.s_station_id as stationId, dsrs.s_station_name as stationName, d.s_technical_remarks as technicalremarks\n" +
        "from rec_device d\n" +
        "left join b_stationregiontree dsr on d.s_station_region_id = dsr.id\n" +
        "left join b_station_dict dsrs on d.s_station_id = dsrs.s_id\n" +
        "left join rec_manufacturer dmf on d.s_manufacturer_id = dmf.s_id\n" +
        "left join rec_device_category ddc on d.s_device_category_id = ddc.s_id\n" +
        "left join rec_device_category ddcp on ddc.s_pid = ddcp.s_id\n" +
        "left join b_stationregiontree dsrp on dsr.parent_id = dsrp.id\n" +
        "where d.s_id = :id",nativeQuery = true)
DeviceInfoVO detailVOInfo(@Param("id") String id);