SQL查询以将逗号分隔的字符串与逗号分隔的字符串进行匹配? [英] SQL query to match a comma-separated string against a comma-separated string?

问题描述

下面的MySQL查询使用PHP提取$ sector(这是一个数字)和$ subsector_text(这是一个用逗号分隔的字符串). $ subsector_text可以是一个数字或多个ID的列表，例如"3,4,7,9".

The MySQL query below uses PHP to pull in the $sector, which is a single digit, and the $subsector_text, which is a comma separated string. The $subsector_text could be a single digit or a list of several IDs, such as "3,4,7,9".

  $sql = "
SELECT DISTINCT a.id
              , a.name
              , a.category_id
              , a.sector
              , a.subsector
              , a.year_in_operation
              , a.state
              , a.total_value
              , b.country_id
              , b.project_id
              , c.isocode_3
              , c.name
           FROM com_barchan_project a
           JOIN com_barchan_location b
             ON b.project_id = a.id
           JOIN com_barchan_country c
             ON c.id = b.country_id
           JOIN com_barchan_project_value_join d
             ON a.id = d.project_id
          WHERE a.state = 1 
            AND a.sector = '$sector'
            AND a.subsector REGEXP '^{$subsector_text}[,]|[,]{$subsector_text}[,]|[,]{$subsector_text}$|^{$subsector_text}$'
          ORDER 
             BY a.total_value DESC
              , a.category_id ASC
              , a.name ASC
";

我在上面的查询中遇到的问题是线路:

The problem I'm having with the query above is with the line:

AND a.subsector REGEXP '^{$subsector_text}[,]|[,]{$subsector_text}[,]|[,]{$subsector_text}$|^{$subsector_text}$'  

如果$ subsector_text ="3,4,5,9"，则它仅返回$ subsector字段中精确包含"3,4,5,9"的记录.

If the $subsector_text = "3,4,5,9", then it's only returning records that contain exactly "3,4,5,9" in the $subsector field.

期望的结果是它将返回任何在$ subsector_text中具有任何值的记录.例如，所有这些都应返回，但当前不返回.此列表仅作为示例，并不完全准确.

The desired result is that it would return any record that has any of the values in the $subsector_text. For instance, all these should be returned, but are currently not. This list is an example and by no means exact.

1,3
1,5
1,3,7,9
3,5
3,4,5,9
9
3
5
4

如何更改查询以选择$ subsector_text字符串中具有值的任何记录?

How do I change the query to select any records that has a value in that's in the $subsector_text string?

请注意:如果$ subsector_text = 11，则不应选择以下示例.

Please NOTE: That if the $subsector_text = 11, then the following, as an example, should not be selected.

1
12
21

任何帮助将不胜感激.

推荐答案

解决方案是重构应用程序.花费了几天时间，但是令人讨厌的代码消失了，并创建了一个新的子部门表.谢谢大家.

The solution was to refactor the app. It took a couple days, but the offending code is gone and a new subsector table was created. Thanks everyone.

这篇关于SQL查询以将逗号分隔的字符串与逗号分隔的字符串进行匹配?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！