mysql map 关联查询_Mybatis关联查询

1. mysql中的LEFT JOIN

使用left join是以左表为主，即使右表没有数据，也会出来左表的所有数据

若不用left join，直接 select * from order a, order_detail b where a.id = b.order_id，若表中没有订单详情，则查询结果中没有这个订单

使用left join注意 on 后面的 and 和 where 后面的 and 控制的范围

2. A表多个字段都和B表关联

场景：A表业务表，B表字典表，A中多个字段和字典表关联，查询A表时需要将多个字段替换为B字典表中的值

解决方案：多个LEFT JOIN连续使用，且右表都为同一个表(字典表)，只不过关联字段换了

SELECT

r.*,

d1.`name` carPrivateFkString,

d2.`name` cardColorFkString,

d3.`name` cardTypeFkString,

d4.`name` userLevelFkString,

d5.`name` carCardTypeFkString

FROM

t_car_registration AS r

LEFT JOIN (

SELECT

d.*,

t.`name` typeName,

t.`value` typeValue

FROM

d_dict AS d

LEFT JOIN d_dict_type AS t ON d.fk_d_dict_type_id = t.pk_id

) AS d1 ON r.car_private_fk = d1.`value`

AND d1.typeValue = 'car_private'

LEFT JOIN (

SELECT

d.*,

t.`name` typeName,

t.`value` typeValue

FROM

d_dict AS d

LEFT JOIN d_dict_type AS t ON d.fk_d_dict_type_id = t.pk_id

) AS d2 ON r.card_color_fk = d2.`value`

AND d2.typeValue = 'card_color'

LEFT JOIN (

SELECT

d.*,

t.`name` typeName,

t.`value` typeValue

FROM

d_dict AS d

LEFT JOIN d_dict_type AS t ON d.fk_d_dict_type_id = t.pk_id

) AS d3 ON r.card_type_fk = d3.`value`

AND d3.typeValue = 'card_type'

LEFT JOIN (

SELECT

d.*,

t.`name` typeName,

t.`value` typeValue

FROM

d_dict AS d

LEFT JOIN d_dict_type AS t ON d.fk_d_dict_type_id = t.pk_id

) AS d4 ON r.user_level_fk = d4.`value`

AND d4.typeValue = 'user_level'

LEFT JOIN (

SELECT

d.*,

t.`name` typeName,

t.`value` typeValue

FROM

d_dict AS d

LEFT JOIN d_dict_type AS t ON d.fk_d_dict_type_id = t.pk_id

) AS d5 ON r.car_card_type_fk = d5.`value`

AND d5.typeValue = 'car_card_type'

WHERE

r.is_delete = 0

3. mybatis中使用结果集ResultMap关联查询

与hibernate 中用one to one和one to many不同，mybatis 中使用association和collection。

association: 一对一关联(has one)

collection:一对多关联(has many)

4. association标签

以 员工 和 部门 为例，

4.1 association一步查询

SELECT d.did did,d.dname dname,e.id eid,e.last_name lastname,e.email email,e.gender gender

FROM t_dept d LEFT JOIN t_employee e

ON d.id=e.d_id

WHERE d.id=#{id}

4.2 association分步查询

SELECT e.id eid,e.last_name lastname,e.email email,e.gender gender

FROM t_employee e

WHERE e.id=#{id}

SELECT d.did did,d.dname dname

FROM t_dept d

WHERE d.did=#{did}

5. 一对多关联查询，B表结果集作为A表的一个List字段

5.1 ResultMap中嵌套Collection直接查询

select

a.*,

b.visitor_name,

b.visitor_card_no,

b.visitor_company,

b.visitor_health_state,

b.visitor_mobile,

b.visitor_portrait,

b.visitor_type,

b.photo_on,

b.photo_down,

b.check_time,

b.check_result

From

t_temporary_report_car_registration a

left join

t_temporary_registration_person_info b

on

a.pk_id = b.temporary_registration_id_fk

where

a.is_delete = 0

5.2 ResultMap中嵌套Collection分步查询

select="mou.boot.app.czxt.dao.TemporaryRegistrationPersonInfoDao.getPersonInfoByRegistrationId"

column="pk_id">

select

a.*

From

t_temporary_report_car_registration a

where

a.is_delete = 0

** TemporaryRegistrationPersonInfoMapper **

select *

from

t_temporary_registration_person_info

where temporary_registration_id_fk=#{registrationId}