<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xjs.copywriting.mapper.CopyWritingMapper">


    <select id="getOneToNew" resultType="com.xjs.copywriting.domain.CopyWriting">
        SELECT id,
               content,
               source,
               mt create_time
        FROM (SELECT max(create_time) mt FROM api_copywriting) time,
             api_copywriting al
        WHERE time.mt = al.create_time
    </select>

    <select id="getOneToRandom" resultType="com.xjs.copywriting.domain.CopyWriting">
        SELECT *
        FROM api_copywriting
        ORDER BY RAND()
        LIMIT 1
    </select>

    <!--////////////////////////代码自动生成/////////////////////////////-->
    <resultMap type="com.xjs.copywriting.domain.CopyWriting" id="CopyWritingResult">
        <result property="id" column="id"/>
        <result property="content" column="content"/>
        <result property="source" column="source"/>
        <result property="createTime" column="create_time"/>
        <result property="type" column="type"/>
    </resultMap>

    <sql id="selectCopyWritingVo">
        select id, content, source, create_time, type
        from api_copywriting
    </sql>

    <select id="selectCopyWritingList" parameterType="com.xjs.copywriting.domain.CopyWriting"
            resultMap="CopyWritingResult">
        <include refid="selectCopyWritingVo"/>
        <where>
            <if test="content != null  and content != ''">and content like concat('%', #{content}, '%') </if>
            <if test="source != null  and source != ''">and source like concat('%', #{source}, '%') </if>
            <if test="type != null ">and type = #{type}</if>
            <if test="createTime != null and endCreateTime != null"> and create_time between #{createTime} and #{endCreateTime}</if>
        </where>
        order by create_time desc
    </select>

    <select id="selectCopyWritingById" parameterType="Long" resultMap="CopyWritingResult">
        <include refid="selectCopyWritingVo"/>
        where id = #{id}
    </select>

    <delete id="deleteCopyWritingById" parameterType="Long">
        delete
        from api_copywriting
        where id = #{id}
    </delete>

    <delete id="deleteCopyWritingByIds" parameterType="String">
        delete from api_copywriting where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <delete id="deleteRepeatData_copy">/*弃用--效率不行*/
        DELETE
        FROM
            api_copywriting
        WHERE
                content IN ( SELECT t.content FROM ( SELECT content FROM api_copywriting GROUP BY content HAVING count( content ) > 1 ) t )
          AND id NOT IN ( SELECT c.id FROM ( SELECT min( id ) id FROM api_copywriting GROUP BY content HAVING count( content )> 1 ) c )
    </delete>

    <delete id="deleteRepeatData">
        delete from api_copywriting where id not in	(
            SELECT
                t.min_id
            FROM
                ( SELECT min( id ) AS min_id FROM api_copywriting GROUP BY content ) AS t
        )
    </delete>


</mapper>