如何将逗号分隔的列转换为行并添加计数器 [英] How to Convert Comma Seperated Column into rows and add counter

问题描述

我正在使用具有这些值的表

I am having a table with these values

表格:文档

id  |       document
----|-------------
1   |   doc.txt , doc1.txt , doc2.rtf , doc3.docx , doc4.doc
2   |   doc.txt 
3   |   doc.txt , doc1.txt 
4   |   doc.txt , doc1.txt , doc2.rtf 
5   |   doc.txt , doc1.txt , doc2.rtf , doc3.docx , doc4.doc
6   |   doc.txt , doc1.txt , doc2.rtf , doc3.docx 
7   |   doc.txt , doc1.txt , doc2.rtf , doc3.docx , doc4.doc
8   |   doc.txt , doc1.txt , doc2.rtf 
9   |   doc.txt , doc1.txt , doc2.rtf , doc3.docx , doc4.doc
10  |   doc.txt , doc1.txt 

SQL FIDDLE SCHEMA

我需要这样的结果. id = 5

I need result like this. Where id = 5

Counter |   docs
    ----|-----------
    1   |   doc.txt
    2   |   doc1.txt
    3   |   doc2.rtf
    4   |   doc3.docx
    5   |   doc4.doc

id = 4的地方

Counter |   docs
    ----|-----------
    1   |   doc.txt
    2   |   doc1.txt
    3   |   doc2.rtf

您看到我需要爆炸逗号分隔的列并计算其中有多少个值.我不喜欢这种模式，但是我正在处理一个现有项目，无法更改它.我需要计数器才能显示在用户界面中.因此，计数器也是必要的.我怎样才能做到这一点?同样，我无法在php端执行此操作，因为我使用的是pyrocms，并且我需要使用pyrocms tage显示它，这不允许我在视图中使用php.

You see i need to explode comma seperated column and count how many values are there. I dont like this schema but i am working on an existing project and can not change it. I need counter to display in the user interface. So counter is necessary too. How can i do that? Also i can not do it on the php end because i am using pyrocms and i need to display it using pyrocms tage which does not allow me to use php in the views.

推荐答案

查看此 SQL FIDDLE ，也许就是您想要的.您必须使用助手表sequence

Look at this SQL FIDDLE , maybe it is that you want. You must use helper table sequence

SELECT * FROM
(SELECT S.Id Counter,
REPLACE(SUBSTRING(SUBSTRING_INDEX(document, ' , ', S.Id),
       LENGTH(SUBSTRING_INDEX(document, ' , ', S.Id - 1)) + 1),
       ' , ', '') docs
FROM documents D, sequence S
WHERE D.id = 3) A
WHERE A.docs <> ''

这篇关于如何将逗号分隔的列转换为行并添加计数器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！