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

问题描述：

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

Mysql表：我的工具表是这个 p>

   facility_name mbid date 
瑜伽，健美操，乒乓球，太极拳，OM1111 2016-06-12 
 aerobics，tai chi，OM1111 2016-06-12 
  code>  pre> 
 
 如何在mysql中使用mbid逐行拆分行值： p> 
 
 
  Facility_name mbid计数数量
瑜伽OM1111 1 
有氧运动OM1111 2 
乒乓球OM1111 1 \  n tai chi OM1111 2 
  code>  pre> 
  div>

答

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

答

You have a very poor database structure. You should have one row per mbid and facility_name. In fact, I have no idea why mbid and date are the same in the two rows.

If I assume that you have facility names in another table, then you can use:

select fn.facility_name, f.mbid, count(*)
from facility f join
     facility_names fn
     on find_in_set(fn.facility_name, f.facility) > 0
group by fn.facility_name, f.mbid;

I should emphasize, though, that although you can do a query like this, you really need to fix your data structure. Storing lists of values in a string is the wrong way to store data in a SQL database.

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

相关推荐