mysql - Sql查询要统计 "different way of stored data"

Question

这是我的表格数据

门票

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ id | tic_files                                                      |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ 1  | 56456sasd.jpg,asd4455asd.jpg,asd564asd5.txt,asd564asd.css      |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ 2  | 56a4sdasd.txt,jkasd5ass.jpg                                    |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ 3  |                                                                |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ 4  | asdjhagsd.gif,spsnd65asd.php,56a4sd54asd.txt                   |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

评论

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ id | tic_id |  com_files                                                     |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ 1  | 1      |  56456sasd.jpg,asd4455asd.jpg,asd564asd5.txt                   |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ 2  | 3      |  56a4sdasd.txt,jkasd5ass.jpg                                   |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ 3  | 1      |  sdf54sdf.gif,swrsdf54sdf.rar                                  |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ 4  | 2      |  asdjhagsd.gif,spsnd65asd.php,56a4sd54asd.txt                  |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 

这是我的问题和我的重试

我希望结果是这样的

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+  tic_id |  count(tic_files)        | count(com_files)        |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+  1      |         4                |      5                  |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+  2      |         2                |      3                  |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+  3      |         0                |      2                  |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+  4      |         3                |      0                  |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

我试过这样做

使用 group_concat( comments ) 加入票证并像这样获取数据

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| tic_id | tic_files                                                 |  com_files                                                                |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| 1      | 56456sasd.jpg,asd4455asd.jpg,asd564asd5.txt,asd564asd.css | 56456sasd.jpg,asd4455asd.jpg,asd564asd5.txt,sdf54sdf.gif,swrsdf54sdf.rar  |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| 2      | 56a4sdasd.txt,jkasd5ass.jpg                               | asdjhagsd.gif,spsnd65asd.php,56a4sd54asd.txt                              |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| 3      |                                                           | 56a4sdasd.txt,jkasd5ass.jpg                                               |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| 4      | asdjhagsd.gif,spsnd65asd.php,56a4sd54asd.txt              |                                                                           |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

但我会尝试使用超过 group_concat(在所有服务器中限制为 1024 kb)所以我想计算文件之间使用分隔符“，”

已解决

这是我的查询

SELECT
                  cat_id as cat__id ,
                  cat_name,(
                    SELECT count(tickets.tic_id) from tickets INNER JOIN cats on(tickets.tic_cat = cats.cat_id) where tickets.tic_cat = cat__id  group by tickets.tic_cat limit 1
                  ) as "count(tickets)",(
                     SELECT  ( LENGTH( GROUP_CONCAT(tickets.tic_files)) - LENGTH( REPLACE( GROUP_CONCAT(tickets.tic_files ) , "," , "" ) ) + 1 )  FROM tickets
                     INNER join cats on ( tickets.tic_cat = cats.cat_id )
                     WHERE tickets.tic_files != "" AND  tickets.tic_cat = cat__id
                      LIMIT 1
                   )  as "count(ticket_files)",(
                    SELECT  GROUP_CONCAT(tickets.tic_files)  FROM tickets
                     inner join cats on ( tickets.tic_cat = cats.cat_id )
                     WHERE tickets.tic_files != "" AND tickets.tic_cat = cat__id
                      LIMIT 1
                   ) as "tickets_files",
                  COUNT(comments.tic_id),(
                     SELECT  ( LENGTH( GROUP_CONCAT(comments.com_files)) - LENGTH( REPLACE( GROUP_CONCAT(comments.com_files ) , "," , "" ) ) + 1 )  FROM comments
                     INNER join tickets on ( tickets.tic_id = comments.tic_id )
                     INNER join cats on ( tickets.tic_cat = cats.cat_id )
                     WHERE comments.com_files != "" AND comments.tic_id = tickets.tic_id and tickets.tic_cat = cat__id
                      LIMIT 1
                   )  as "count(com_files)",(
                    SELECT  GROUP_CONCAT(comments.com_files)  FROM comments
                     INNER join tickets on ( tickets.tic_id = comments.tic_id )
                     inner join cats on ( tickets.tic_cat = cats.cat_id )
                     WHERE comments.com_files != "" AND comments.tic_id = tickets.tic_id and tickets.tic_cat = cat__id
                      LIMIT 1
                   ) as "com_files"
            from tickets
            INNER JOIN cats ON (tickets.tic_cat = cats.cat_id)
            INNER JOIN comments ON ( comments.tic_id = tickets.tic_id )
  group by tickets.tic_cat

这是结果图片

Answer 1

可以像这样计算字符串中出现的次数:

SELECT LENGTH( GROUP_CONCAT(DISTINCT tic_files SEPARATOR ",") ) - LENGTH( REPLACE( GROUP_CONCAT(DISTINCT tic_files SEPARATOR ",") , ',' , '' ) ) + 1 AS `TIC File Count`

这是通过获取列的总长度，然后在用空格替换所有要搜索的字符后减去列的长度来实现的。

所以56a4sdasd.txt,jkasd5ass.jpg会变成56a4sdasd.txtjkasd5ass.jpg，返回值为1，加1就可以了有分隔符周围的字符串总数。