今天写了一个一对多的查询用到了collection,首先是这样的
这样的
select
u.id as u_id, u.company_id as companyId, u.name,u.sex, u.mobile, u.email, u.id_card as id_card, u.work_code as work_code, u.position, u.if_delete as if_delete, u.update_time as update_time,
ur.supplier_user_id as supplier_user_id,ur.supplier_id as supplier_id,orgr.supplier_organization_id as orgrm_supplier_organization_id
from user u
left join user_relation ur on u.id = ur.user_id
left join user_organization_relation uor on u.id = uor.user_id
left join organization_relation orgr on uor.organization_id = orgr.organization_id
where orgr.supplier_id = #{supplierId}
and u.company_id = #{companyId} and orgr.company_id=#{companyId} and uor.company_id = #{companyId}
and u.name like '%#{name}%'
and u.update_time > #{time}
查询结果是这样的
仔细看会发现,我的id和supplierOrganzationIdList里面的值一样,WTFK,我各种修改我的collection,各种查资料,感觉我的collection写的没毛病啊,但是结果始终是这样,最后机缘巧合将我的sql改了一下,把我的list查询条件调到了第一位,也就是这样
select
orgr.supplier_organization_id as orgrm_supplier_organization_id,
u.id as u_id, u.company_id as companyId, u.name,u.sex, u.mobile, u.email, u.id_card as id_card, u.work_code as work_code, u.position, u.if_delete as if_delete, u.update_time as update_time,
ur.supplier_user_id as supplier_user_id,ur.supplier_id as supplier_id
from user u
left join user_relation ur on u.id = ur.user_id
left join user_organization_relation uor on u.id = uor.user_id
left join organization_relation orgr on uor.organization_id = orgr.organization_id
where orgr.supplier_id = #{supplierId}
and u.company_id = #{companyId} and orgr.company_id=#{companyId} and uor.company_id = #{companyId}
and u.name like '%#{name}%'
and u.update_time > #{time}
惊喜的发现查询的结果对了,坑爹啊!
一个有梦想的混子
原创文章 64获赞 25访问量 4万+
关注
私信
展开阅读全文
作者:一个有梦想的混子