MySql部分查询

（1）replace into使用

无数据时，新增数据，有数据时，删除原数据新增本数据
参考文档：https://www.cnblogs.com/c-961900940/p/6197878.html

<insert id="replaceList">
        REPLACE INTO sku_stock (id,product_id, sku_code, price) VALUES
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.id,jdbcType=BIGINT},
            #{item.productId,jdbcType=BIGINT},
            #{item.skuCode,jdbcType=VARCHAR},
            #{item.price,jdbcType=DECIMAL})
        </foreach>
    </insert>

（2）json_extract，json_unquote使用

json_unquote 去除双引号
json_extract 获取某个字段中的json里面数据
UNION All 两表数据都查询

{"carNumber":"京Q12345","phone":"13812341234"}

<select id="selectActivityAndVirtualUser" resultType="com.qike.api.webapp.vo.HomeActivityUserVO">
        select c.*
        from (
            select
                a.car_number as carNumber,
                a.phone
            from  population_users a
            where a.activity_id = #{activityId, jdbcType=INTEGER}
            and a.del_flag = '0'
            UNION All
            select
                json_unquote(json_extract(ao.activity_form,'$[0].carNumber')) as carNumber,
                json_unquote(json_extract(ao.activity_form,'$[0].phone')) as phone
            from  order as ao
            where ao.del_flag = '0'
            and ao.activity_id = #{activityId, jdbcType=INTEGER}
            and ao.order_status != '0'
        ) as c
        order by c.payTime desc
    </select>
    

（3）根据经纬度获取距离（米）

<select id="getStoreByDistance" resultMap="StoreInfoMap">

        select <include refid="Base_Column_List"/>,
        ROUND(6378.138 * 2 * ASIN(
            SQRT(POW(SIN((#{latitude} * PI() / 180 - a.latitude * PI() / 180) / 2),2) +
            COS(#{latitude} * PI() / 180) * COS(latitude * PI() / 180) *
            POW(SIN((#{longitude} * PI() / 180 -   a.longitude * PI() / 180) / 2),2))) * 1000
        ) AS distance
        from  took_store as a
        where a.id in
        <foreach collection="storeIds" item="item" open="(" close=")" separator="," >
            #{item}
        </foreach>
        ORDER BY distance ASC
    </select>
    

（4）FIND_IN_SET用法

FIND_IN_SET()函数接受两个参数：

第一个参数是要查找的字符串。
第二个参数是要搜索的逗号分隔的字符串列表。

FIND_IN_SET("1 ",a.store_ids)

（5）将表中字段数据，更新到另一个表中数据

update took_activity_order o left join took_activity_user u on o.activity_user_id= u.id set o.recommend_id = u.recommend_id, o.recommend_ids= u.recommend_ids where o.activity_user_id= u.id;

（6）更新表字段数据后面加字符串

update took_activity_user set recommend_ids = CONCAT(recommend_ids,',');

（7）查询某张表某个字段重复数据

SELECT talent_id FROM candidate_info  GROUP BY  talent_id   HAVING   count(1) > 1

SELECT * from candidate_info  where talent_id in( talent_id FROM candidate_info  GROUP BY  talent_id   HAVING   count(1) > 1)

HAVING是分组（GROUP BY）后的筛选条件，分组后的数据组内再筛选；WHERE 则是在分组前筛选。
WHERE 子句中不能使用聚集函数，而 HAVING 子句中可以，所以在集合函数中加上了 HAVING 来起到测试查询结果是否符合条件的作用。即 HAVING 子句的适用场景是可以使用聚合函数。

（8）根据日期统计

	SELECT
        DATE_FORMAT(create_date,'%Y-%m-%d') as `date`,
        count(1) as count
    FROM candidate_info
    WHERE del_flag = '0'
        AND DATE_FORMAT(create_date,'%Y-%m-%d') BETWEEN DATE_FORMAT(#{startDate},'%Y-%m-%d')  and DATE_FORMAT(#{endDate},'%Y-%m-%d')
        AND company_id = #{companyId}
    GROUP BY date

（9）日期只对月，日做校验查询（生日）

select  DATE_FORMAT(birthday, '%m-%d')  
from sys_user 
WHERE DATE_FORMAT('2021-08-31', '%m-%d') < DATE_FORMAT(birthday, '%m-%d')  
order by DATE_FORMAT(birthday, '%m-%d') asc 
LIMIT 6;