如果确定值是 英文逗号拼接则可以直接使用
find_in_set 函数
WHERE FIND_IN_SET(authorId, #{authorIds})
- 弊端:
- find_in_set会让索引失效(因为大部分情况用的主键或int类型查询,而这个函数只会以str查询所以出现了隐式类型转换)
应用场景
组合字段查询如,字段是部门存储了 父、子级等关系的时候使用
select id,channel from sel_topic_info where FIND_IN_SET('3',channel)
union 连表的正巧使用方式
核心思想:
- 需要相同字段,需要不同类型区分时使用,或者需要根据类型分页查询等操作(如果需要分页则可以使用( sql limit 10)) 如果业务需要可以采用from 子查询结果
(SELECT
IFNULL(p.`name`, '兴宾区')`label`,
count( c.id ) `value`
FROM
cb_news c
INNER JOIN xt_selected_topic_task t ON c.task_id = t.id
INNER JOIN sys_user_department d ON c.create_by = d.user_id
INNER JOIN sys_department p ON d.department_id = p.id
WHERE
FIND_IN_SET ( c.create_by,( SELECT GROUP_CONCAT( DISTINCT user_ids ) userIds FROM xt_selected_topic_task WHERE
user_ids != '' ) ) limit 10)
AND p.NAME IN ( '兴宾区' )
<if test="dateType != 0">
AND UNIX_TIMESTAMP(c.create_date) >= UNIX_TIMESTAMP(#{beforeDate})
AND UNIX_TIMESTAMP(c.create_date) <= UNIX_TIMESTAMP(#{nowDate})
</if>
UNION
SELECT
IFNULL(p.`name`, '武宣县')`label`,
count( c.id ) `value`
FROM
cb_news c
INNER JOIN xt_selected_topic_task t ON c.task_id = t.id
INNER JOIN sys_user_department d ON c.create_by = d.user_id
INNER JOIN sys_department p ON d.department_id = p.id
WHERE
FIND_IN_SET ( c.create_by,( SELECT GROUP_CONCAT( DISTINCT user_ids ) userIds FROM xt_selected_topic_task WHERE
user_ids != '' ) )
AND p.NAME IN ( '武宣县' )
<if test="dateType != 0">
AND UNIX_TIMESTAMP(c.create_date) >= UNIX_TIMESTAMP(#{beforeDate})
AND UNIX_TIMESTAMP(c.create_date) <= UNIX_TIMESTAMP(#{nowDate})
</if>
UNION
SELECT
IFNULL(p.`name`, '象州县')`label`,
count( c.id ) `value`
FROM
cb_news c
INNER JOIN xt_selected_topic_task t ON c.task_id = t.id
INNER JOIN sys_user_department d ON c.create_by = d.user_id
INNER JOIN sys_department p ON d.department_id = p.id
WHERE
FIND_IN_SET ( c.create_by,( SELECT GROUP_CONCAT( DISTINCT user_ids ) userIds FROM xt_selected_topic_task WHERE
user_ids != '' ) )
AND p.NAME IN ( '象州县' )
<if test="dateType != 0">
AND UNIX_TIMESTAMP(c.create_date) >= UNIX_TIMESTAMP(#{beforeDate})
AND UNIX_TIMESTAMP(c.create_date) <= UNIX_TIMESTAMP(#{nowDate})
</if>
UNION
SELECT
IFNULL(p.`name`, '金秀县')`label`,
count( c.id ) `value`
FROM
cb_news c
INNER JOIN xt_selected_topic_task t ON c.task_id = t.id
INNER JOIN sys_user_department d ON c.create_by = d.user_id
INNER JOIN sys_department p ON d.department_id = p.id
WHERE
FIND_IN_SET ( c.create_by,( SELECT GROUP_CONCAT( DISTINCT user_ids ) userIds FROM xt_selected_topic_task WHERE
user_ids != '' ) )
AND p.NAME IN ( '金秀县' )
<if test="dateType != 0">
AND UNIX_TIMESTAMP(c.create_date) >= UNIX_TIMESTAMP(#{beforeDate})
AND UNIX_TIMESTAMP(c.create_date) <= UNIX_TIMESTAMP(#{nowDate})
</if>
UNION
SELECT
IFNULL(p.`name`, '忻城县')`label`,
count( c.id ) `value`
FROM
cb_news c
INNER JOIN xt_selected_topic_task t ON c.task_id = t.id
INNER JOIN sys_user_department d ON c.create_by = d.user_id
INNER JOIN sys_department p ON d.department_id = p.id
WHERE
FIND_IN_SET ( c.create_by,( SELECT GROUP_CONCAT( DISTINCT user_ids ) userIds FROM xt_selected_topic_task WHERE
user_ids != '' ) )
AND p.NAME IN ( '忻城县' )
<if test="dateType != 0">
AND UNIX_TIMESTAMP(c.create_date) >= UNIX_TIMESTAMP(#{beforeDate})
AND UNIX_TIMESTAMP(c.create_date) <= UNIX_TIMESTAMP(#{nowDate})
</if>
UNION
SELECT
IFNULL(p.`name`, '合山市')`label`,
count( c.id ) `value`
FROM
cb_news c
INNER JOIN xt_selected_topic_task t ON c.task_id = t.id
INNER JOIN sys_user_department d ON c.create_by = d.user_id
INNER JOIN sys_department p ON d.department_id = p.id
WHERE
FIND_IN_SET ( c.create_by,( SELECT GROUP_CONCAT( DISTINCT user_ids ) userIds FROM xt_selected_topic_task WHERE
user_ids != '' ) )
AND p.NAME IN ( '合山市' )
<if test="dateType != 0">
AND UNIX_TIMESTAMP(c.create_date) >= UNIX_TIMESTAMP(#{beforeDate})
AND UNIX_TIMESTAMP(c.create_date) <= UNIX_TIMESTAMP(#{nowDate})
</if>
order by `value` desc