如何使用mysql中的count函数选项逐行分隔行值

Question

Mysql Table: In My facility table is this

      facility_name                                  mbid           date         
     yoga,aerobics,table tennis,tai chi,            OM1111         2016-06-12
     aerobics,tai chi,                              OM1111         2016-06-12

How to split row value one by one with mbid in mysql:

     Facility_name              mbid        Number of count
        yoga                    OM1111         1
      aerobics                  OM1111         2
      table tennis              OM1111         1
      tai chi                   OM1111         2

Answer 1

CREATE TABLE facility 
    (facility_name varchar(35), mbid varchar(6), date varchar(10))
;

INSERT INTO facility 
    (facility_name, mbid, date)
VALUES
    ('yoga,aerobics,table tennis,tai chi,', 'OM1111', '2016-06-12'),
    ('aerobics,tai chi,', 'OM1111', '2016-06-12')
;

Script :

Select T.VALUE,T.mbid,COUNT(T.VALUE)Cnt FROM (
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.facility_name, ',', n.n), ',', -1) value,mbid
  FROM facility  t CROSS JOIN 
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N 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) a
   ,(SELECT 0 AS N 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) b
    ORDER BY n
) n
 WHERE n.n <= 1 + (LENGTH(t.facility_name) - LENGTH(REPLACE(t.facility_name, ',', ''))))T
 WHERE T.VALUE <> ''
 GROUP BY T.VALUE,T.mbid
 ORDER BY T.value

How to pass the date function in where condition to get count of activity :

 Select    facility.mbid,membership.name,membership.organization,
 membership.designation,membership.division, facility.VALUE `Facility Name`,
 COUNT(facility.VALUE)`Number of Activite` FROM ( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(facility.facility_name, ',', n.n), ',', -1) value,mbid FROM facility  CROSS JOIN 
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N 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) a
   ,(SELECT 0 AS N 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) b
    ORDER BY n
) n
 WHERE n.n <= 1 + (LENGTH(facility.facility_name) -LENGTH(REPLACE(facility.facility_name, ',', ''))))T
 facility Inner Join  membership ON facility.mbid=membership.mbid 
where facility.date Between '2016-06-04' and '2016-06-07' &&  
   facility.VALUE <> '' 
   GROUP BY facility.VALUE,facility.mbid ORDER BY facility.value