需求: 需要4 层 树形结构数据
List<List<List<List<>>>>
对应mapper 映射
sql 语句:
SELECT
si.id,
si.NAME,
ss.id sid,
ss.NAME sname,
ad.id AS deid,
ad.DeviceName AS dename,
ad.Platform AS detype,
d1.id AS deid,
d1.`name` AS dename,
d1.Platform AS detype,
s5.id AS aid,
s5.parameter_name AS aname
FROM
sys_item si
LEFT JOIN sys_station ss ON si.id = ss.item_id
LEFT JOIN sys_station_aliyun s1 ON ss.id = s1.station_id
LEFT JOIN aliyun_device ad ON ad.id = s1.aliyun_device_id
LEFT JOIN sys_argument_aliyun s2 ON s2.aliyun_device_id = s1.aliyun_device_id
LEFT JOIN sys_station_dianxin s6 ON ss.id = s1.station_id
LEFT JOIN dianxin_device d1 ON d1.id = s6.dianxin_device_id
LEFT JOIN sys_argument_dianxin s7 ON s7.dainxin_device_id = s6.dianxin_device_id
LEFT JOIN sys_argument s5 ON ( s5.id = s7.argument_id OR s5.id = s2.argument_id )
返回结果:
问题:
在查询第三层 集合的时候,是通过不同表 查询出来,用同一个实体类接收,但是不知道 在查询的时候就合并在一起
修改 :
在查询的时候 通过 UNION ALL 将第三层集合合并在一起
sql:
SELECT
si.id,
si.NAME,
ss.id sid,
ss.NAME sname,
mm.did AS deid,
mm.dename AS dename,
mm.detype AS detype,
s5.id AS aid,
s5.parameter_name AS aname
FROM
sys_item si
LEFT JOIN sys_station ss ON si.id = ss.item_id
LEFT JOIN (
(
SELECT
s1.station_id,
ad.id AS did,
ad.DeviceName AS dename,
ad.Platform AS detype,
s2.argument_id AS argument_id
FROM
sys_station_aliyun s1
LEFT JOIN aliyun_device ad ON ad.id = s1.aliyun_device_id
LEFT JOIN sys_argument_aliyun s2 ON s2.aliyun_device_id = s1.aliyun_device_id
) UNION ALL
(
SELECT
s6.station_id,
d1.id AS did,
d1.`name` AS dename,
d1.Platform AS detype,
s7.argument_id AS argument_id
FROM
sys_station_dianxin s6
LEFT JOIN dianxin_device d1 ON d1.id = s6.dianxin_device_id
LEFT JOIN sys_argument_dianxin s7 ON s7.dainxin_device_id = s6.dianxin_device_id
)
) mm ON mm.station_id = ss.id
LEFT JOIN sys_argument s5 ON s5.id = mm.argument_id
WHERE
si.del_flag = 0
AND ss.del_flag =0
查询结果就是 我所需要的