Mysql查询自定义定制字段的内容

需求：

1、经营团队下未固定任何职位，需按实际添加职位字段及相关职位下的相关人员
2、职位为定制字段 “执行董事/董事长” 、“副董事长”、“董事成员”、“监事”

 # 查询 所有企业的企业信息相关的经营团队的自定义定制职位的人员
   SELECT ep.id AS '企业ID',ep.enterprise_name AS '企业名称',
	      mt1.job_title AS '执行董事/董事长', mt1.`name` AS '董事长名字',
          mt2.job_title AS '副董事长', mt2.`name`  AS '副董名字',
          mt3.job_title AS '董事成员', mt3.`name`  AS '董事名字',
          mt4.job_title AS '监事', mt4.`name`  AS '监事名字'
	 FROM enterprise   AS ep                                                                # 企业信息表
LEFT JOIN management_team AS mt1 ON (mt1.eid = ep.id AND mt1.job_title = '执行董事/董事长')  # 经营团队表 自定义定制字段
LEFT JOIN management_team AS mt2 ON (mt2.eid = ep.id AND mt2.job_title = '副董事长' )       # 经营团队表 自定义定制字段
LEFT JOIN management_team AS mt3 ON (mt3.eid = ep.id AND mt3.job_title = '董事成员')        # 经营团队表 自定义定制字段
LEFT JOIN management_team AS mt4 ON (mt4.eid = ep.id AND mt4.job_title = '监事'     )       # 经营团队表 自定义定制字段
    WHERE ep.is_del = 0                                                                     # 未逻辑删除
	  AND ep.fictitious = 0                                                                 # 虚拟公司
	  AND ep.business_status NOT IN ('注销','转让','迁出')                                  # 剔除的经营状态

需求

查询 含"董事长"或“执行董事”但不含"副董事长"职位的人员

使用 locate(substring, string, [start]) 函数：模糊搜索

1. substring 检索其位置的字符串
2. string 要检索所需字符串的字段
3. [start] 可选参数，搜索的起始位置，默认位置 1

查询 含"董事长"或“执行董事”但不含副董事长职位的人员（名字去重）
(条件中 ！为不含 )
 SELECT DISTINCT(name),job_title FROM management_team 
  WHERE (locate('董事长',job_title) OR locate('执行董事',job_title)) 
    AND ! locate('副董事长',job_title) 

(条件中NOT 为不含 )
 SELECT DISTINCT(name),job_title FROM management_team 
  WHERE (locate('董事长',job_title) 
     OR locate('执行董事',job_title))
    AND NOT locate('副董事长',job_title)  

需求

查询“执行董事/董事长”职位的某个人员所关联的企业

   SELECT ep.enterprise_name AS '企业', emt.job_title AS '职位',emt.`name` AS '名称'
     FROM enterprise AS ep
LEFT JOIN (         
			  SELECT id,eid,job_title,GROUP_CONCAT(`name`) AS `name` 
				FROM management_team 
			   WHERE (locate('董事长',job_title) OR locate('执行董事',job_title))
			     AND NOT locate('副董事长',job_title)
			GROUP BY eid                                  # 按企业eid分组，把名字合并成一行 
          ) AS emt ON emt.eid = ep.id 
    WHERE emt.eid IN (
						SELECT eid FROM management_team 
						 WHERE `name` LIKE Concat('%','雷军','%') 
						   AND (job_title LIKE '%执行董事%' OR job_title LIKE '%董事长%')
                     )
      AND emt.id IS NOT NULL
      AND ep.is_del = 0                                    # 未逻辑删除
	  AND ep.fictitious = 0                                # 虚拟公司
	  AND ep.business_status NOT IN ('注销','转让','迁出') # 剔除的经营状态