mysql没有东西的商品_mysql常用但易忘东西记录

1、find_in_set(str, strList)(查询字段(strlist)中包含(str)的结果，返回结果为null或记录)

select dict_name from sys_dict where find_in_set(dict_id, (select parent_ids from sys_dict where state_num=1 and dict_id = 100))

2、group_concat(将查询出的同一字段多条结果拼成一个字符串，各结果中间使用英文逗号隔开)

select group_concat(dict_name) from sys_dict where dict_id in (select dict_id from sys_dict where state_num=1 and dict_id < 10)

3、collate 解决两个表连接查询不一致导致报错的问题(如报  Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '=' 的错误时，强转成utf8mb4_general_ci字符集)

select su.user_name, su.age, sd.dict_name from sys_user su left join sys_dict sd on (su.user_name = sd.dict_value collate utf8mb4_general_ci) and sd.state_num=1 where su.state_num=1 and user_id = 1

4、union all 解决两个集合合并的问题，(注意：两select 必选加limit，否则会按顺序排列)

select p4.project_id from(

(

select p.project_idfrom(

select co.project_id, p1.create_time, count(co.order_id) as sales_numfromcrm_order co

inner join policy_project p1 on p1.project_id= co.project_id and p1.state_num = 1where co.state_num= 1group by co.project_id

) as p order by p.sales_num desc, p.create_time desc limit99999999 // 注意，如果不加上limit的话两个结果集会按主键顺序排序) union all

(

select p2.project_idfrom(

select p3.project_idfrom policy_project p3 where p3.project_id not in(

select co1.project_idfrom crm_order co1 where co1.state_num = 1)and p3.state_num = 1 order by p3.create_time limit 99999999 // 必选加limit，否则会按主键排序) as p2

)

) as p4

4、order by field(字段， 值1，值2，值3...) 根据给定的数值来排序。注意(如果给定的数值不是根据条件查询出来的所有结果，则会先查出没有给定值的结果，才会查给定值的结果)

select order_id from crm_order where state_num =1 order by field(order_id, 3,5,6,8,10)