mysql 列转行案例1

select t1.id,t1.dept_name ,count(*) from sys_tenant_dept as t1
join (
	SELECT
  SUBSTRING_INDEX( SUBSTRING_INDEX( a.dept_id, ',', b.help_id + 1 ), ',',- 1 ) AS user_dept_id,
   a.id as staff_id
FROM
  (
  SELECT 
    dept_id , id
  FROM
    by_staff 
  ) a
LEFT JOIN (
  SELECT
    0 AS help_id UNION ALL
  SELECT
    1 UNION ALL
  SELECT
    2 UNION ALL
  SELECT
    3 UNION ALL
  SELECT
    4 UNION ALL
  SELECT
    5  UNION ALL
  SELECT
    6  UNION ALL
  SELECT
    7  UNION ALL
  SELECT
    8  UNION ALL
  SELECT
    9  UNION ALL
  SELECT
    10 
    UNION ALL
  SELECT
    11
    UNION ALL
  SELECT
    12 
    UNION ALL
  SELECT
    13 
    UNION ALL
  SELECT
    14 
    UNION ALL
  SELECT
    15 
    UNION ALL
  SELECT
    16 
    UNION ALL
  SELECT
    17 
    UNION ALL
  SELECT
    18 
    UNION ALL
  SELECT
    19 
    UNION ALL
  SELECT
    20 
    UNION ALL
  SELECT
    21 
    UNION ALL
  SELECT
    22 
  ) b
-- 序号小于分隔符,的个数  
ON b.help_id < ( LENGTH( a.dept_id ) - LENGTH( REPLACE ( a.dept_id, ',', '' ) ) + 1 ) 
) as t2 on t1.id = t2.user_dept_id
where t1.tenant_id  = 1
group by t1.id;