搜索到
2
篇与
的结果
-
MySQL笔记 1.group_concat函数支持order by内部排序，例如：# 按照id倒序拼接img SELECT GROUP_CONCAT(img ORDER BY id DESC) FROM `thumb_table`;2.Mysql 给查询出的结果集添加自增序号SELECT (@rownum:=@rownum+1) 自增序号别名, 结果集字段 FROM 结果集, (SELECT @rownum:=0) AS 任意别名 SELECT @rownum:=@rownum+1 AS rownum, A.* FROM ( SELECT U.* FROM `users` U ORDER BY U.`id` DESC ) A, (SELECT @rownum:=0) B补充： 如果是在MyBatis中使用上述查询时，变量i的的初始值0，可以使用传参的方式（${}）进行设置。3.mysql日期加一天select DATE_ADD('2022-02-24 09:03:36',INTERVAL 1 DAY);4.距离计算函数<select id="selectBtFirmList" parameterType="com.risesun.business.domain.BtFirm" resultMap="BtFirmResult"> select id, dept_id, firm_name, firm_photo, firm_address, credit_code, principal, firm_tel, firm_phone,firm_intro, firm_info, firm_lng, firm_lat, release_status, release_time, order_num, deleted, create_by,create_time, update_by, update_time <if test="firmLng != null and firmLng != '' and firmLat != null and firmLat != ''"> ,round(st_distance_sphere(point(#{firmLng},#{firmLat}), point (firm_lng,firm_lat))/1000,2) distance </if> from bt_firm <where> <if test="firmName != null and firmName != ''"> and firm_name like concat('%', #{firmName}, '%')</if> <if test="releaseStatus != null and releaseStatus != ''"> and release_status = #{releaseStatus}</if> <if test="firmIds != null and firmIds.size > 0"> <foreach collection="firmIds" item="id" open="and id in (" separator="," close=")"> #{id} </foreach> </if> </where> order by order_num ASC, <if test="firmLng != null and firmLng != '' and firmLat != null and firmLat != ''"> distance ASC, </if> update_time DESC </select>5.mysql中查询使用 != 不等于会过滤掉null的情况及其原因分析和解决在写 SQL 条件语句时经常用到 不等于 != 的筛选条件。此时要注意此条件会将字段为 Null 的数据也当做满足不等于的条件而将数据筛选掉。（也就是说会忽略过滤掉为 null 的数据，导致数据不准确）。二、解决方案 要查出第三列只需将 SQL 改为如下语句 即可。SELECT * FROM A WHERE B1 != 1 OR B1 is Null 上面这种方法最通俗，网上也最多，但是我总是感觉效率太低。目前我使用的方法是：SELECT * FROM A WHERE IFNULL(B1,'') != 1<if test="voluntaryType != null and voluntaryType == '0'.toString()"> and IFNULL(voluntary_type,'') != '1'</if> <if test="voluntaryType != null and voluntaryType == '1'.toString()"> and voluntary_type = #{voluntaryType}</if> -
