最近在用mybatis写sql时候,遇到了一些问题。在以下sql中:
<select id="selectByCondition" parameterType="map" resultMap="BaseResultMap"> select count(*) purchase_num, b.*, a.create_time AS s_create_time, a.sales_order_no, a.amount_tax_sum, a.sales_order_status_name, a.marker_date, a.checker_date,a.sales_order_id from b_sales_order a, b_sales_order_detail b WHERE a.sales_order_id = b.sales_order_id AND a.delete_flag ='0' <if test="map.statusList !=null and map.statusList.size > 0"> AND a.sales_order_status_code in <foreach item="item" index="index" collection="map.statusList" open="(" separator="," close=")"> #{item} </foreach> </if> <if test="map.flag == true and map.isEqual ==true"> AND b.request_number = b.outstock_number </if> <if test="map.flag == true and map.isEqual ==false"> AND b.request_number > b.outstock_number </if> <if test="map.markerId!=null"> AND a.marker_id=#{map.markerId} </if> /*GROUP BY a.sales_order_id*/ ORDER BY a.marker_date DESC LIMIT #{map.offset},#{map.pageSize} </select>
1. 除开动态sql,即使我用
select count(*) purchase_num, b.*, a.create_time AS s_create_time, a.sales_order_no, a.amount_tax_sum, a.sales_order_status_name, a.marker_date, a.checker_date,a.sales_order_id from b_sales_order a, b_sales_order_detail b WHERE a.sales_order_id = b.sales_order_id AND a.delete_flag ='0'
查询结果也只有1条记录。当我把 count(*)删除时,开始有多条数据。这里count是统计函数,由于使用了它,数据只有1行。
2.我用
select b.*, a.create_time AS s_create_time, a.sales_order_no, a.amount_tax_sum, a.sales_order_status_name, a.marker_date, a.checker_date,a.sales_order_id from b_sales_order a, b_sales_order_detail b WHERE a.sales_order_id = b.sales_order_id AND a.delete_flag ='0' AND marker_id = 11 GROUP BY a.sales_order_id
查询时候,发现数据也只有1条。我这里用到了分组查询。当我把
GROUP BY a.sales_order_id删除后,开始有多条数据。
3.在以下SQL中
<select id="countByCondition" parameterType="map" resultType="java.lang.Integer"> select COUNT(*) from (select COUNT(*) from b_sales_order a, b_sales_order_detail b WHERE a.sales_order_id = b.sales_order_id AND a.delete_flag ='0' <if test="map.statusList !=null and map.statusList.size > 0"> AND a.sales_order_status_code in <foreach item="item" index="index" collection="map.statusList" open="(" separator="," close=")"> #{item} </foreach> </if> <if test="map.flag == true and map.isEqual ==true"> AND b.request_number = b.outstock_number </if> <if test="map.flag == true and map.isEqual ==false"> AND b.request_number > b.outstock_number </if> <if test="map.markerId!=null"> AND a.marker_id=#{map.markerId} </if> GROUP BY a.sales_order_id) c
可以统计出订单个数。
4.
select b.*, a.create_time AS s_create_time, a.sales_order_no, a.amount_tax_sum, a.sales_order_status_name, a.marker_date, a.checker_date,a.sales_order_id from b_sales_order a LEFT JOIN b_sales_order_detail b on a.sales_order_id = b.sales_order_id AND a.delete_flag ='0' AND marker_id = 11
当我用左连接时候,发现订单表的数据全都在,当详情表的数据匹配不上时候,详情表所有字段为空。
极端情况,订单表一条纪录,详情表对应13条,那么进行左连接时候,发现有13条记录!在我们需要按详情表的字段作为搜索条件,查询结果为订单表时,
订单结果就重复了12条!因此,左连接不能乱用。
5.
SELECT * from m_material_sales_price where material_id = 406000003 and package_unit_id = null SELECT * from m_material_sales_price where material_id = 406000003 and package_unit_id is null
上一条SQL查询结果为0条,下一条SQL查询结果为1。注意用关键字is。
综上,以上问题都是在实际项目中遇到的,也反映出对数据库知识的欠缺。要对 左连接、where、分组、统计、笛卡尔积这些数据库概念有清晰认识!