php - 按替代拼写分组

Question

我有一个地址表，其中包含预期的列(公寓、房屋、街道、邮政编码、国家/地区等)。

我正在使用 PHP 从表单输入 ($street) 执行 MySQL 搜索。我已经成功做到了，这样我就可以通过所有道路拼写变体(例如:“street”、“st”、“st.”)进行搜索，如下所示。我希望输出数组按街道分组。下面按每个单独的拼写进行分组。有没有办法让我的返回组可以包含拼写的所有变体？

我的代码

<?php
    $results = $db->prepare(
        "SELECT * FROM entries
        WHERE 
        street = :street
        OR street = REPLACE(:street, 'st', 'street')
        OR street = REPLACE(:street, 'st.', 'street')
        OR street = REPLACE(:street, 'st.', 'st')
        OR street = REPLACE(:street, 'street', 'st')
        OR street = REPLACE(:street, 'street', 'st.')

        GROUP BY street, house
    ")

    $results->bindParam(':street', $street);
    $results->execute();

    $output = $results->fetchAll(PDO::FETCH_ASSOC);    
    return $output;
?>

当前示例输出

[1] Church Street
    [1] 12 Church Street
    [2] 25 Church Street
[2] Church St.
    [1] 19 Church St.

所需的示例输出

[1] Church Street
    [1] 12 Church Street
    [2] 25 Church Street
    [3] 19 Church St.

Answer 1

我认为您的数据中存在与您试图在 where 子句中处理的相同问题。纠正地址可能是一个挑战。这是这个小例子的尝试:

SELECT *
FROM entries
WHERE street = :street
      OR street = REPLACE(:street, 'st', 'street')
      OR street = REPLACE(:street, 'st.', 'street')
      OR street = REPLACE(:street, 'st.', 'st')
      OR street = REPLACE(:street, 'street', 'st')
      OR street = REPLACE(:street, 'street', 'st.')
GROUP BY (case when street like '%st' then concat(street, 'reet')
               when street like '% st.' then replace(street, ' st.', ' street')
               else street
          end), house;

编辑:

您应该在 select` 子句中重复 group by 表达式:

SELECT (case when street like '%st' then concat(street, 'reet')
             when street like '% st.' then replace(street, ' st.', ' street')
             else street
        end) as street, house
FROM entries
WHERE street = :street
      OR street = REPLACE(:street, 'st', 'street')
      OR street = REPLACE(:street, 'st.', 'street')
      OR street = REPLACE(:street, 'st.', 'st')
      OR street = REPLACE(:street, 'street', 'st')
      OR street = REPLACE(:street, 'street', 'st.')
GROUP BY (case when street like '%st' then concat(street, 'reet')
               when street like '% st.' then replace(street, ' st.', ' street')
               else street
          end), house;