一些特殊场景的sql

标签：code goods create sql wh 场景 time 特殊 oid

1.窗口函数排序

mysql/sqlsever

select row_number() over(order by a.fpotUpdateTimes asc) 'index',a.*
   from
( select max(update_time) as fpotUpdateTimes from fc_payment_order_dt where parent_oid =  #{fpoOid} and update_time!= create_time and goods_pay_status = 'D' group by update_time
)a

2.根据近三天等时间段查询

入库时间；1-昨天；2-今天；3-近7天；4-上月；5-本月；6-本年 int

entryTimeStart 开始时间

entryTimeEnd 结束时间

sqlserver版本


<select id="wxShopSettlementList" resultType="com.juxi.system.api.domain.wm.vo.WxSettlementVO">
    select t.* from (select distinct a.oid,a.goods_wh_code,c.goods_name,a.settlement_amt,a.settlement_status,
    a.receipt_time as settlementTime,c.goods_alloy,pb.brand_name,pg.oid AS goodOid,spv.contacts_phone
    from sc_settlement_dt a
    left join wm_tmp_codes wtc on wtc.goods_wh_code = a.goods_wh_code
    left join sc_po_goods spg on wtc.tmp_wh_code = spg.tmp_wh_code
    left join sc_purchase_order spo on spo.oid = spg.po_oid
    left join sc_po_vendor spv on spv.oid=spo.po_ven_oid
    left join sc_settlement b on a.parent_oid = b.oid
    left join wm_wh_goods_info c on c.goods_wh_code = a.goods_wh_code
    left join sc_vendor d on a.ven_code = d.ven_code
    left join ( SELECT goods_wh_code, create_time FROM wm_entry_dt GROUP BY goods_wh_code,
    create_time ) wet ON wet.goods_wh_code = c.goods_wh_code
    left join (select ven_oid, contacts_phone
    from sc_vendor_contacts
    where ven_oid =
    (select ven_oid from sc_vendor_contacts where contacts_phone =#{dto.loginPhone})) svc on svc.ven_oid = d.oid
    left join pd_goods pg on pg.goods_code = c.goods_code
    left join pd_brand pb on pg.goods_brand_code = pb.brand_code
    <where>
        svc.contacts_phone is not null and a.settlement_status != 'O' and a.settlement_status != 'W'
        <if test="dto.settlementStatus!=null">
            <if test="dto.settlementStatus == 1">and a.settlement_status = 'P'
            </if>
            <if test="dto.settlementStatus == 0">
                and a.settlement_status = 'S'
            </if>
        </if>
        <if test="dto.goodsWhCodeOrGoodsName!=null and dto.goodsWhCodeOrGoodsName!=''">
            and  (c.goods_wh_code like concat('%', #{dto.goodsWhCodeOrGoodsName}, '%')
            or c.goods_name like concat('%', #{dto.goodsWhCodeOrGoodsName}, '%') )
        </if>
        <if test="dto.goodsBrandCode != null and dto.goodsBrandCode.size() > 0">
            and pb.brand_code in
            <foreach collection="dto.goodsBrandCode" item="item" index="index" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="dto.contactsPhone != null and dto.contactsPhone.size() > 0">
            and spv.contacts_phone in
            <foreach collection="dto.contactsPhone" item="item" index="index" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="dto.goodsAlloy != null and dto.goodsAlloy.size() > 0 ">
            AND c.goods_alloy IN
            <foreach collection="dto.goodsAlloy" item="item" index="index" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>

        <if test="dto.entryTimeChoose != null">
            <if test="dto.entryTimeChoose == 1">
                AND DATEDIFF(dd, wet.create_time, GETDATE()) = 1
            </if>
            <if test="dto.entryTimeChoose == 2">
                AND DATEDIFF(dd, wet.create_time, GETDATE()) = 0
            </if>
            <if test="dto.entryTimeChoose == 3">
                AND DATEDIFF(dd, wet.create_time, GETDATE()) >= 1
            </if>
            <if test="dto.entryTimeChoose == 4">
                AND DATEDIFF(month, wet.create_time, GETDATE()) = 1
            </if>
            <if test="dto.entryTimeChoose == 5">
                AND DATEDIFF(month, wet.create_time, GETDATE()) = 0
            </if>
            <if test="dto.entryTimeChoose == 6">
                AND DATEDIFF(year, wet.create_time, GETDATE()) = 0
            </if>
        </if>

        <if test="dto.entryTimeStart != null and dto.entryTimeStart != ''">
            AND wet.create_time >= #{dto.entryTimeStart}
        </if>
        <if test="dto.entryTimeEnd != null and dto.entryTimeEnd != ''">
            AND wet.create_time <= #{dto.entryTimeEnd}
        </if>
        <if test="dto.settlementTimeChoose != null">
            <if test="dto.settlementTimeChoose == 1">
                AND DATEDIFF(dd, a.receipt_time, GETDATE()) = 1
            </if>
            <if test="dto.settlementTimeChoose == 2">
                AND DATEDIFF(dd, a.receipt_time, GETDATE()) = 0
            </if>
            <if test="dto.settlementTimeChoose == 3">
                AND DATEDIFF(dd, a.receipt_time, GETDATE()) >= 1
            </if>
            <if test="dto.settlementTimeChoose == 4">
                AND DATEDIFF(month, a.receipt_time, GETDATE()) = 1
            </if>
            <if test="dto.settlementTimeChoose == 5">
                AND DATEDIFF(month, a.receipt_time, GETDATE()) = 0
            </if>
            <if test="dto.settlementTimeChoose == 6">
                AND DATEDIFF(year, a.receipt_time, GETDATE()) = 0
            </if>
        </if>

        <if test="dto.settlementTimeStart != null and dto.settlementTimeStart != ''">
            AND a.receipt_time >= #{dto.settlementTimeStart}
        </if>
        <if test="dto.settlementTimeEnd != null and dto.settlementTimeEnd != ''">
            AND a.receipt_time <= #{dto.settlementTimeEnd}
        </if>
    </where>
    <if test="dto.timeOrder != null and dto.timeOrder != '' and dto.timeOrder == 0 ">
        ORDER BY
        wet.create_time DESC
    </if>
    <if test="dto.timeOrder != null and dto.timeOrder != '' and dto.timeOrder == 1 ">
        ORDER BY
        a.receipt_time DESC
    </if>)t
</select>

mysql版本

<if test="weTime!=null and weTime!=''">
    AND
    <choose>
    	 --昨日
        <when test="weTime== 'yesterday'.toString()">
            to_days(now())-to_days(we.create_time) =1
        </when>
        --今日
        <when test="weTime== 'today'.toString()">
            to_days(we.create_time)=to_days(now())
        </when>
        --本周
        <when test="weTime== 'nearly_seven_day'.toString()">
            date_sub(curdate(),interval 7 day) < date(we.create_time)
        </when>
        --上月
        <when test="weTime== 'last_month'.toString()">
            period_diff(date_format(now(),'%Y%m'),date_format(we.create_time,'%Y%m'))=1
        </when>
        --本月
        <when test="weTime== 'this_month'.toString()">
            date_format(curdate(),'%y%m') = date_format(we.create_time,'%y%m')
        </when>
        --今年
        <when test="weTime== 'this_year'.toString()">
            year(we.create_time) = year(now())
        </when>
    </choose>
</if>

resultMap里嵌套list

<select id="selectWmInventoryList" resultMap="wmInventoryDtIds" parameterType="com.juxi.erp.wm.domain.dto.WmInventoryDTO">
    select DISTINCT a.oid,a.wi_no,a.wi_type,a.wi_status,
    a.wm_total_qty,a.actual_total_qty,a.profit_total_qty,
    a.regist_person,a.remark,a.wi_person,a.goods_type_code,
    a.wi_desc,a.wi_time,a.wi_end_time,a.create_time,a.update_time,a.update_at,a.wh_lock_mode
    from wm_inventory a left join wm_inventory_dt b on a.oid=b.parent_oid
    left join wm_wh_area wwa on wwa.wh_area_code = b.wh_area_code
    <where>
        <if test="whAreaCode!=null and whAreaCode!=''">
            and b.wh_area_code=#{whAreaCode}
        </if>
        <if test="warehouseType!=null and warehouseType!=''">
            and wwa.warehouse_type=#{warehouseType}
        </if>
        <if test="goodsTypeCode!=null and goodsTypeCode!=''">
            and CHARINDEX(#{goodsTypeCode},a.goods_type_code)>0
        </if>
        <if test="wiStatus!=null and wiStatus!=''">
            and a.wi_status=#{wiStatus}
        </if>
        <if test="wiType!=null and wiType!=''">
            and a.wi_type=#{wiType}
        </if>
        <if test="goodsCodeName!=null and goodsCodeName!=''">
            and (b.goods_code like concat('%',#{goodsCodeName},'%')
            or  b.goods_name like concat('%',#{goodsCodeName},'%')
            or b.goods_wh_code like concat('%',#{goodsCodeName},'%'))
        </if>
        <if test="wiNo!=null and wiNo!=''">
            and a.wi_no like concat('%',#{wiNo},'%')
        </if>
        <if test="wiPerson!=null and wiPerson!=''">
            and a.wi_person=#{wiPerson}
        </if>
        <if test="registPerson!=null and registPerson!=''">
            and a.regist_person=#{registPerson}
        </if>
        <if test="updateStartTime!=null and updateStartTime!=''">
            <![CDATA[ and a.update_time >= #{updateStartTime} ]]>
        </if>
        <if test="updateEndTime!=null and updateEndTime!=''">
            <![CDATA[ and a.update_time <= #{updateEndTime} ]]>
        </if>
    </where>
    order by a.wi_no desc
</select>
              

                <!--查询盘点单列表-->
<resultMap id="wmInventoryDtIds" type="com.juxi.erp.wm.domain.vo.WmInventoryVos">
    <id column="oid" property="oid"/>
    <result column="wi_no" property="wiNo"/>
    <result column="wi_type" property="wiType"/>
    <result column="wi_status" property="wiStatus"/>
    <result column="wm_total_qty" property="wmTotalQty"/>
    <result column="actual_total_qty" property="actualTotalQty"/>
    <result column="profit_total_qty" property="profitTotalQty"/>
    <result column="regist_person" property="registPerson"/>
    <result column="remark" property="remark"/>
    <result column="wi_person" property="wiPerson"/>
    <result column="wi_desc" property="wiDesc"/>
    <result column="wi_time" property="wiTime"/>
    <result column="wi_end_time" property="wiEndTime"/>
    <result column="create_time" property="createTime"/>
    <result column="update_time" property="updateTime"/>
    <result column="update_at" property="updateAt"/>
    <result column="wh_lock_mode" property="whLockMode"/>
    <collection property="wmInventoryDts" column="oid=oid" select="getWmInventoryDtIds"/>
</resultMap>
<select id="getWmInventoryDtIds" resultType="com.juxi.erp.wm.domain.entity.WmInventoryDt">
    select wwa.warehouse_type AS warehouseType,b.warehouse_code AS warehouseCode
    from wm_inventory_dt b
             left join wm_wh_area wwa on wwa.wh_area_code = b.wh_area_code
    where parent_oid=#{oid}
</select>

可以返回有list字段的对象

@Data
@Accessors(chain = true)
public class WmInventoryDTO extends WmInventory {

    private String goodsCodeName;//商品编码、商品名称及入库编码
    private String warehouseType;//区域类型
    private String updateStartTime;//更新开始日期
    private String updateEndTime;//更新结束日期
    private List<String> areaAndBins;//库区编码
    private String sqlRule;//权限规则
    private List<String> whAreaCodes;//库区集合
    private List<String> warehouseCodes;//仓库集合
    private List<String> whBinCodes;//库位集合
    private List<String> goodsTypeCodes;//商品类型

}

SQL server：现在时间 getDate()
mysql:now()

要提升SQL的查询效能，一般来说大家会以建立索引(index)为第一考虑。其实除了index的建立之外，当我们在下SQL Command时，在语法中加一段WITH (NOLOCK)可以改善在线大量查询的环境中数据集被LOCK的现象藉此改善查询的效能。不过有一点千万要注意的就是，WITH (NOLOCK)的SQL SELECT有可能会造成Dirty Read。

with(nolock)的介绍

大家在写查询时,为了性能,往往会在表后面加一个nolock,或者是with(nolock),其目的就是查询是不锁定表,从而达到提高查询速度的目的。

当同一时间有多个用户访问同一资源,并发用户中如果有用户对资源做了修改，此时就会对其它用户产生某些不利的影响，例如：

1、脏读

一个用户对一个资源做了修改，此时另外一个用户正好读取了这条被修改的记录，然后，第一个用户放弃修改，数据回到修改之前，这两个不同的结果就是脏读。

2、不可重复读

一个用户的一个操作是一个事务，这个事务分两次读取同一条记录，如果第一次读取后，有另外用户修改了这个数据，然后第二次读取的数据正好是其它用户修改的数据，这样造成两次读取的记录不同，如果事务中锁定这条记录就可以避免。

3、幻读

指用户读取一批记录的情况，用户两次查询同一条件的一批记录，第一次查询后，有其它用户对这批数据做了修改，方法可能是修改，删除，新增，第二次查询时，会发现第一次查询的记录条目有的不在第二次查询结果中，或者是第二次查询的条目不在第一次查询的内容中。

NOLOCK 语句执行时不发出共享锁，允许脏读，等于 READ UNCOMMITTED事务隔离级别。nolock确实在查询时能提高速度，但它并不是没有缺点的，起码它会引起脏读、只适用与select查询语句。在一些不需要考虑脏读的场合会用到，例如当用户在论坛发广告贴时删除其所有发帖，这个查询就不怕脏读，全删，或者漏一个正在发的都不是问题。

二、实例

SELECT  COUNT(UserID)   
FROM    EMPLOYEE WITH (NOLOCK)           
JOIN WORKING_GROUP WITH (NOLOCK)           
ON EMPLOYEE.UserID = WORKING_GROUP.UserID

三、with(nolock)的使用场景

1:数据量特别大的表，牺牲数据安全性来提升性能是可以考虑的；

2:允许出现脏读现象的业务逻辑，反之一些数据完整性要求比较严格的场景就不合适了，像金融方面等。

3:数据不经常修改的表，这样会省于锁定表的时间来大大加快查询速度。

4、当使用NoLock时，它允许阅读那些已经修改但是还没有交易完成的数据。因此如果有需要考虑transaction事务数据的实时完整性时，使用WITH (NOLOCK)就要好好考虑一下。

四、nolock和with(nolock)的几个小区别

1、SQL05中的同义词，只支持with(nolock);

2、with(nolock)的写法非常容易再指定索引。

3、跨服务器查询语句时不能用with (nolock) 只能用nolock，同一个服务器查询时则with(nolock)和nolock都可以用

mysql MySQL本身就不阻塞，nolock也就没有意义了

标签：code,goods,create,sql,wh,场景,time,特殊,oid
From： https://blog.51cto.com/u_16173281/6609531

相关文章

赞助商

阅读排行