php 多个标签用逗号,MySql PHP从逗号分隔数据(标签)中选择不同值的计数

如何从MySql中以逗号分隔值存储的数据中选择不同值的计数？我将使用PHP最终从MySql输出数据.

那里有什么,每个帖子都有标签.所以最后,我正在尝试输出数据,就像stackoverflow使用它的标签一样,如下所示：

tag-name x 5

这就是表格中数据的样子(抱歉内容,但它是食谱的网站).

"postId" "tags" "category-code"

"1" "pho,pork" "1"

"2" "fried-rice,chicken" "1"

"3" "fried-rice,pork" "1"

"4" "chicken-calzone,chicken" "1"

"5" "fettuccine,chicken" "1"

"6" "spaghetti,chicken" "1"

"7" "spaghetti,chorizo" "1"

"8" "spaghetti,meat-balls" "1"

"9" "miso-soup" "1"

"10" "chanko-nabe" "1"

"11" "chicken-manchurian,chicken,manchurain" "1"

"12" "pork-manchurian,pork,manchurain" "1"

"13" "sweet-and-sour-pork,pork" "1"

"14" "peking-duck,duck" "1"

产量

chicken 5 // occurs 5 time in the data above

pork 4 // occurs 4 time in the data above

spaghetti 3 // an so on

fried-rice 2

manchurian 2

pho 1

chicken-calzone 1

fettuccine 1

chorizo 1

meat-balls 1

miso-soup 1

chanko-nabe 1

chicken-manchurian 1

pork-manchurian 1

sweet-n-sour-pork 1

peking-duck 1

duck 1

我试图在那里选择所有不同值的计数,但由于它是逗号分隔的数据,似乎没有办法做到这一点. select distinct不起作用.

你能想到一个很好的方式在mysql或使用PHP来获得输出,就像我做的那样？

解决方法:

解

我真的不知道如何将逗号分隔值的水平列表转换为行列表而不创建包含数字的表,因为您可能有逗号分隔值的数字.如果你可以创建这个表,这是我的答案：

SELECT

SUBSTRING_INDEX(SUBSTRING_INDEX(all_tags, ',', num), ',', -1) AS one_tag,

COUNT(*) AS cnt

FROM (

SELECT

GROUP_CONCAT(tags separator ',') AS all_tags,

LENGTH(GROUP_CONCAT(tags SEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(tags SEPARATOR ','), ',', '')) + 1 AS count_tags

FROM test

) t

JOIN numbers n

ON n.num <= t.count_tags

GROUP BY one_tag

ORDER BY cnt DESC;

返回：

+---------------------+-----+

| one_tag | cnt |

+---------------------+-----+

| chicken | 5 |

| pork | 4 |

| spaghetti | 3 |

| fried-rice | 2 |

| manchurain | 2 |

| pho | 1 |

| chicken-calzone | 1 |

| fettuccine | 1 |

| chorizo | 1 |

| meat-balls | 1 |

| miso-soup | 1 |

| chanko-nabe | 1 |

| chicken-manchurian | 1 |

| pork-manchurian | 1 |

| sweet-and-sour-pork | 1 |

| peking-duck | 1 |

| duck | 1 |

+---------------------+-----+

17 rows in set (0.01 sec)

释

脚本

>我们使用逗号连接所有标签,只创建一个标签列表,而不是每行一个

>我们计算列表中有多少个标签

>我们发现如何在此列表中获取一个值

>我们发现如何将所有值作为不同的行

>我们计算按其值分组的标签

上下文

让我们构建你的架构：

CREATE TABLE test (

id INT PRIMARY KEY,

tags VARCHAR(255)

);

INSERT INTO test VALUES

("1", "pho,pork"),

("2", "fried-rice,chicken"),

("3", "fried-rice,pork"),

("4", "chicken-calzone,chicken"),

("5", "fettuccine,chicken"),

("6", "spaghetti,chicken"),

("7", "spaghetti,chorizo"),

("8", "spaghetti,meat-balls"),

("9", "miso-soup"),

("10", "chanko-nabe"),

("11", "chicken-manchurian,chicken,manchurain"),

("12", "pork-manchurian,pork,manchurain"),

("13", "sweet-and-sour-pork,pork"),

("14", "peking-duck,duck");

连接所有标签列表

我们将在一行中使用所有标签,因此我们使用GROUP_CONCAT来完成工作：

SELECT GROUP_CONCAT(tags SEPARATOR ',') FROM test;

返回以逗号分隔的所有标记：

pho,pork,fried-rice,chicken,fried-rice,pork,chicken-calzone,chicken,fettuccine,chicken,spaghetti,chicken,spaghetti,chorizo,spaghetti,meat-balls,miso-soup,chanko-nabe,chicken-manchurian,chicken,manchurain,pork-manchurian,pork,manchurain,sweet-and-sour-pork,pork,peking-duck,duck

统计所有标签

为了计算所有标签,我们得到完整标签列表的长度,并且在替换之后,我们删除完整标签列表的长度.我们加1,因为分隔符在两个值之间.

SELECT LENGTH(GROUP_CONCAT(tags SEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(tags SEPARATOR ','), ',', '')) + 1 AS count_tags

FROM test;

返回：

+------------+

| count_tags |

+------------+

| 28 |

+------------+

1 row in set (0.00 sec)

获取标记列表中的第N个标记

我们使用SUBSTRING_INDEX函数来获取

-- returns the string until the 2nd delimiter\'s occurrence from left to right: a,b

SELECT SUBSTRING_INDEX('a,b,c', ',', 2);

-- return the string until the 1st delimiter, from right to left: c

SELECT SUBSTRING_INDEX('a,b,c', ',', -1);

-- we need both to get: b (with 2 being the tag number)

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c', ',', 2), ',', -1);

有了这样的逻辑,为了在列表中获得第3个标记,我们使用：

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(tags SEPARATOR ','), ',', 3), ',', -1)

FROM test;

返回：

+-------------------------------------------------------------------------------------+

| SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(tags SEPARATOR ','), ',', 3), ',', -1) |

+-------------------------------------------------------------------------------------+

| fried-rice |

+-------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

将所有值作为不同的行

我的想法有点棘手：

>我知道我们可以通过连接表来创建行

>我需要使用上面的请求在列表中获取第N个标记

因此,我们将创建一个表,其中包含从1到您列表中可能包含的最大标记数的所有数字.如果您可以拥有1M值,请创建1到1,000,000的1M条目.对于100个标签,这将是：

CREATE TABLE numbers (

num INT PRIMARY KEY

);

INSERT INTO numbers VALUES

( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( 8 ), ( 9 ), ( 10 ),

( 11 ), ( 12 ), ( 13 ), ( 14 ), ( 15 ), ( 16 ), ( 17 ), ( 18 ), ( 19 ), ( 20 ),

( 21 ), ( 22 ), ( 23 ), ( 24 ), ( 25 ), ( 26 ), ( 27 ), ( 28 ), ( 29 ), ( 30 ),

( 31 ), ( 32 ), ( 33 ), ( 34 ), ( 35 ), ( 36 ), ( 37 ), ( 38 ), ( 39 ), ( 40 ),

( 41 ), ( 42 ), ( 43 ), ( 44 ), ( 45 ), ( 46 ), ( 47 ), ( 48 ), ( 49 ), ( 50 ),

( 51 ), ( 52 ), ( 53 ), ( 54 ), ( 55 ), ( 56 ), ( 57 ), ( 58 ), ( 59 ), ( 60 ),

( 61 ), ( 62 ), ( 63 ), ( 64 ), ( 65 ), ( 66 ), ( 67 ), ( 68 ), ( 69 ), ( 70 ),

( 71 ), ( 72 ), ( 73 ), ( 74 ), ( 75 ), ( 76 ), ( 77 ), ( 78 ), ( 79 ), ( 80 ),

( 81 ), ( 82 ), ( 83 ), ( 84 ), ( 85 ), ( 86 ), ( 87 ), ( 88 ), ( 89 ), ( 90 ),

( 91 ), ( 92 ), ( 93 ), ( 94 ), ( 95 ), ( 96 ), ( 97 ), ( 98 ), ( 99 ), ( 100 );

现在,我们使用以下查询获取numth(num是一行数)：

SELECT n.num, SUBSTRING_INDEX(SUBSTRING_INDEX(all_tags, ',', num), ',', -1) as one_tag

FROM (

SELECT

GROUP_CONCAT(tags SEPARATOR ',') AS all_tags,

LENGTH(GROUP_CONCAT(tags SEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(tags SEPARATOR ','), ',', '')) + 1 AS count_tags

FROM test

) t

JOIN numbers n

ON n.num <= t.count_tags

返回：

+-----+---------------------+

| num | one_tag |

+-----+---------------------+

| 1 | pho |

| 2 | pork |

| 3 | fried-rice |

| 4 | chicken |

| 5 | fried-rice |

| 6 | pork |

| 7 | chicken-calzone |

| 8 | chicken |

| 9 | fettuccine |

| 10 | chicken |

| 11 | spaghetti |

| 12 | chicken |

| 13 | spaghetti |

| 14 | chorizo |

| 15 | spaghetti |

| 16 | meat-balls |

| 17 | miso-soup |

| 18 | chanko-nabe |

| 19 | chicken-manchurian |

| 20 | chicken |

| 21 | manchurain |

| 22 | pork-manchurian |

| 23 | pork |

| 24 | manchurain |

| 25 | sweet-and-sour-pork |

| 26 | pork |

| 27 | peking-duck |

| 28 | duck |

+-----+---------------------+

28 rows in set (0.01 sec)

计算标记出现次数

只要我们现在有经典行,我们就可以轻松计算每个标记的出现次数.

标签：php,sql,mysql

来源： https://codeday.me/bug/20190925/1816332.html