SQL 查询从逗号分隔列中检索值

Question

可能的重复：
在 SQL 中分割字符串
搜索包含逗号分隔值的列

在我的PHP< /strong> 项目 我在编写 SQL 时遇到问题：

我有一个表“consultants”，其中包含字段categories，其值如下:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ id |  consultant_name  | categories                           +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+  1 |  AOAOAO           | health,insurance,programming,        +
+  2 |  BOBOBO           | health,gaming,windows,mobile,        +
+  3 |  CCCCCC           | insurance,windows,                   + 
+  4 |  DDDDDD           | mobile,                              +
+  . |  ......           | ............                         +
+  . |  ......           | ............                         +
+  . |  ......           | ............                         +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

而且我有包含顾问可以注册的所有类别的数组。

$arrayOfCategories =  $array("health","insurance","programming",
                             "sports","gaming","windows","apple",
                             "mobile","astrology");

我想要的是一个SQL查询，它应该给我一个输出，例如：

+++++++++++++++++++++++++++++++
+ category      | occurrence  +    
+++++++++++++++++++++++++++++++
+ health        | 2           +
+ insurance     | 2           +
+ programming   | 1           +
+ sports        | 0           +
+ gaming        | 1           +
+ windows       | 2           +
+ apple         | 0           +
+ mobile        | 2           +
+ astrology     | 0           +
+++++++++++++++++++++++++++++++

任何类型的帮助将不胜感激...

提前致谢...

Answer 1

糟糕的数据库设计但有趣的问题，这是我的做法（php代码）：

$arrayOfCategories =  $array("health","insurance","programming",
                             "sports","gaming","windows","apple",
                             "mobile","astrology");

 $count = count($arrayOfCategories);
 $query = "";
 for($i = 0; $i < $count; $i++)
 {
   $value = $arrayOfCategories[$i];
   $query += "SELECT '$value' AS category,
                 COUNT(*) AS occurrence
                 FROM consultants
                 WHERE FIND_IN_SET('$value', categories)";
    if($i < $count -1) {
    $query += " UNION "
    }


 }

生成的查询的结果应该给你你想要的。糟糕的设计常常会带来有趣的问题......:)

Answer 2

这是一个糟糕的数据库设计。如果我坚持下去，我不会尝试使用 SQL 来获取事件，而是使用我的应用程序。

创建一个名为 $occurrence 的新数组，其中包含每个单词的索引和计数。
选择每一行并用逗号分割类别的内容。循环遍历这个结果数组并为每个单词递增。

我对 PHP 有点生疏，所以这是我对 Perl 的建议：

my %occurrence;

while ( my ($categories) = $sth->fetchrow_array() ){

   my @cats = split(',', $categories);

   foreach my $c (@cats){
      $occurrence{$c}++;
   }

}

这种方式的好处之一是您会发现任何拼写错误的类别，并且在添加新类别时不必更新 SQL。

Answer 3

如果您有一个包含类别的表，您可以这样做：

SELECT cat.name, COUNT(*) 
FROM categories cat JOIN consultants con ON con.categories LIKE '%'+cat.name+'%'

尽管如此，我认为这很尴尬，您应该考虑规范化数据库方案，以便有一个顾问类别关系的映射表