mysql 根据多个条件的list查询

原创已于 2023-04-03 16:37:15 修改 · 1.3k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #list #数据库

于 2023-04-03 16:33:24 首次发布

当需要根据list中的多个条件查询MySQL数据库时，简单的IN操作无法满足需求。例如，要根据name、materialCategoryBrandId、model和unitId四个条件筛选数据，可以采用中间表连接查询的方式。具体的SQL查询语句能够返回满足所有条件的数据记录。

一个条件list mysql中可以直接使用in，list中多个条件同时满足查询list时就不能直接用in了

比如我想根据list中的四个条件（name、materialCategoryBrandId、model、unitId）查询出表中这两条数据

这时使用mysql中间表连接查询sql如下：

select b.*
        from
        (
--         <if test="query != null and query.size > 0">
--             <foreach collection="query" item="item" separator=" union all " >
--                 select #{item.name} name,  #{item.materialCategoryBrandId} materialCategoryBrandId, #{item.model} model, #{item.unitId} unitId from dual
--             </foreach>
--         </if>
					select '六类跳线' name,  658250366200262656 materialCategoryBrandId, '2米' model, 18 unitId from dual
					
					union all
					
					select '六类4对UTP电缆' name,  658250278019215360 materialCategoryBrandId, 'ZC-6-01' model, 6 unitId from dual
				
        ) a
        join
        (select
            m.id,
            m.name ,
            m.model ,
            m.material_category_id as materialCategoryId,
            mc.name as materialCategoryName,
            m.material_category_brand_id as materialCategoryBrandId,
            mcb.name as materialCategoryBrandName,
            m.unit_id as unitId,
            u.name as unitName,
            m.cost_price as costPrice ,
            m.inventory_quantity as inventoryQuantity,
            m.sales_price as salesPrice
        from material m
        left join material_category mc on m.material_category_id = mc.id and mc.mark = 1
        left join material_category_brand mcb on m.material_category_brand_id = mcb.id and mcb.mark = 1
        left join unit u on m.unit_id = u.id and u.mark = 1
        where  m.mark = 1 ) b on a.name = b.name and a.materialCategoryBrandId = b.materialCategoryBrandId and a.model = b.model and b.unitId = a.unitId
        GROUP BY b.id

查询结果如下：