mybatis 使用DISTINCT 获取需要列符合条件的列 做动态拼接
考虑到表信息量大 需要对一些列加入索引 数据量大 不建议使用 默认返回1024个字节长度,超过长度会被截取掉。
解决方法:
在mysql的配置文件,my.ini中mysqld下增加group_concat_max_len = 102400
mysql中执行:set global group_concat_max_len=102400;
或者set session group_concat_max_len=102400;
global对全局有效,session只对当前会话有效。需要管理员才能操作global!
重启mysql服务器生效
linux下的mysql一样的设置,只不过linux下的mysql配置文件是/etc/my.cnf设置!
<select id="selectAggrColumnValues" parameterType="org.ggj.nms.db.condition.WiErpStockCondition" resultType="string"> select <choose> <when test="field == 'orginal_value_id'"> group_concat(DISTINCT orginal_value_id) </when> <when test="field == 'aging_degree'"> group_concat(DISTINCT aging_degree) </when> <when test="field == 'province_id'"> group_concat(DISTINCT province_id) </when> <when test="field == 'city_id'"> group_concat(DISTINCT city_id) </when> <when test="field == 'district_id'"> group_concat(DISTINCT district_id) </when> <otherwise></otherwise> </choose> as lst from wi_erp_stock <where> state = 20 /*省id*/ <choose> <when test="condition.provinceId != null || 0 == condition.provinceId"> and province_id = #{condition.provinceId,jdbcType=INTEGER} </when> <otherwise> and province_id !=-1 </otherwise> </choose> /*市id*/ <if test="condition.cityId != null"> and city_id=#{condition.cityId,jdbcType=INTEGER} </if> /*区id*/ <if test="condition.districtId != null"> and district_id=#{condition.districtId,jdbcType=INTEGER} </if> /*新旧程度*/ <choose> <when test="condition.agingDegree == null || 0 == condition.agingDegree "> and aging_degree !=0 </when> <otherwise> AND aging_degree = #{condition.agingDegree,jdbcType=INTEGER} </otherwise> </choose> /*原值下标*/ <if test="condition.originalValueRange != null"> and orginal_value_id=#{condition.originalValueRange,jdbcType=INTEGER} </if> <if test="condition.categoryLevel1 != null"> AND category_level1 = #{condition.categoryLevel1,jdbcType=BIGINT} </if> <if test="condition.categoryLevel2 != null"> AND category_level2 = #{condition.categoryLevel2,jdbcType=BIGINT} </if> <if test="condition.categoryLevel3 != null"> AND category_level3 = #{condition.categoryLevel3,jdbcType=BIGINT} </if> <if test="condition.categoryLevel4 != null"> AND category_level4 = #{condition.categoryLevel4,jdbcType=BIGINT} </if> <if test="condition.inputAssetNo != null and condition.inputAssetNo != ''"> and asset_no = #{condition.inputAssetNo,jdbcType=VARCHAR} </if> <if test="condition.inputSkuName != null and condition.inputSkuName != ''"> <![CDATA[ and sku_name like CONCAT('%',#{condition.inputSkuName,jdbcType=VARCHAR},'%') ]]> </if> <if test="condition.inputDeptIds != null and condition.inputDeptIds.size() > 0"> and dept_id incondition. <foreach item="item" collection="condition.inputDeptIds" index="index" open="(" separator="," close=")"> #{item,jdbcType=BIGINT} </foreach> </if> </where> </select>
结果;
lst 1,23,34,45,35,45,,4,34,546,,344
可以使用 group_concat 函数,将不重复的其他字段拼接起来,通过分组获取到ID列符合条件的id
select distinct name,group_concat(id) from table group by name
结果:
name id a 1 b 2,5 c 3,4