• 数据库遇到的问题


      最近在用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、分组、统计、笛卡尔积这些数据库概念有清晰认识!

  • 相关阅读:
    第5次系统综合实践
    第4次系统综合实践
    第3次系统综合实践
    第2次实践作业
    第1次实践作业
    第03组 Beta版本演示
    第03组 Beta冲刺(4/4)
    OO第四单元总结
    OO第三单元总结
    OO第二单元总结
  • 原文地址:https://www.cnblogs.com/parkdifferent/p/7382630.html
Copyright © 2020-2023  润新知