MySQL - сопоставить почтовый индекс на основе одного или двух первых символов

Я пытаюсь создать оператор SQL, чтобы найти соответствующую запись на основе предоставленного почтового индекса и сохраненных почтовых индексов в базе данных, а также аспекта веса.

Почтовые индексы в базе данных состоят из 1 или 2 символов, т.е. B, BA...

Теперь - значение, переданное оператору SQL, всегда будет иметь 2 первых символа почтового индекса клиента. Как я могу найти соответствие для него? Скажем, у меня есть почтовый индекс B1, который будет соответствовать только одному B в базе данных плюс весовой аспект, с которым я согласен.

Вот мой текущий оператор SQL, который также учитывает фактор бесплатной доставки сверх определенного веса:

SELECT `s`.*,
IF (
    '{$weight}' > (
        SELECT MAX(`weight_from`)
        FROM `shipping`
        WHERE UPPER(SUBSTRING(`post_code`, 1, 2)) = 'B1'
    ),
    (
        SELECT `cost`
        FROM `shipping`
        WHERE UPPER(SUBSTRING(`post_code`, 1, 2)) = 'B1'
        ORDER BY `weight_from` DESC
        LIMIT 0, 1
    ),
    `s`.`cost`
) AS `cost`
FROM `shipping` `s`
WHERE UPPER(SUBSTRING(`s`.`post_code`, 1, 2)) = 'B1'
AND 
(
    (
        '{$weight}' > (
            SELECT MAX(`weight_from`)
            FROM `shipping`
            WHERE UPPER(SUBSTRING(`post_code`, 1, 2)) = 'B1'
        )
    )
    OR 
    ('{$weight}' BETWEEN `s`.`weight_from` AND `s`.`weight_to`)
)
LIMIT 0, 1

Однако в приведенном выше примере используется функция SUBSTRING() с жестко закодированным числом символов, равным 2 - здесь мне действительно нужна помощь, чтобы она соответствовала только количеству символов, соответствующему предоставленному почтовому индексу - в данном случае B1.

Маркус — спасибо за помощь — отличный пример — вот как выглядит мой код для тех, кому тоже интересно:

Сначала я запустил следующий оператор, чтобы получить правильный почтовый индекс:

(
    SELECT `post_code`
    FROM `shipping`
    WHERE `post_code` = 'B1'
)
UNION
(
    SELECT `post_code`
    FROM `shipping`
    WHERE `post_code` = SUBSTRING('B1', 1, 1)
)
ORDER BY `post_code` DESC
LIMIT 0, 1

Затем, на основе возвращаемого значения, присвоенного индексу post_code, за моим вторым оператором следует:

$post_code = $result['post_code'];

SELECT `s`.*,
IF (
    '1000' > (
        SELECT MAX(`weight_from`)
        FROM `shipping`
        WHERE `post_code` = '{$post_code}'  
    ),
    (
        SELECT `cost`
        FROM `shipping`
        WHERE `post_code` = '{$post_code}'
        ORDER BY `weight_from` DESC
        LIMIT 0, 1
    ),
    `s`.`cost`
) AS `cost`
FROM `shipping` `s`
WHERE `s`.`post_code` = '{$post_code}'
AND 
(
    (
        '1000' > (
            SELECT MAX(`weight_from`)
            FROM `shipping`
            WHERE `post_code` = '{$post_code}'
            ORDER BY LENGTH(`post_code`) DESC
        )
    )
    OR 
    ('1000' BETWEEN `s`.`weight_from` AND `s`.`weight_to`)
)
LIMIT 0, 1

mysql postal-code
person user398341    schedule 26.01.2012    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Следующий запрос получит все результаты, в которых post_code в таблице отгрузки соответствует началу переданного в post_code, затем упорядочивает его от наиболее явного к наименее явному, возвращая наиболее явный:

SELECT *
FROM shipping
WHERE post_code = SUBSTRING('B1', 1, LENGTH(post_code))
ORDER BY LENGTH(post_code) DESC
LIMIT 1

Обновить

Хотя этот запрос является гибким, он не очень быстрый, поскольку не может использовать индекс. Если таблица доставки большая, и вы будете передавать не более двух символов, возможно, будет быстрее сделать два отдельных вызова.

Во-первых, попробуйте самый явный вызов.

SELECT *
FROM shipping
WHERE post_code = 'B1'

Если он не возвращает результат, выполните поиск по одному символу:

SELECT *
FROM shipping
WHERE post_code = SUBSTRING('B1', 1, 1)

Конечно, вы можете объединить их с помощью UNION, если вам нужно сделать это за один вызов:

SELECT * FROM
((SELECT *
FROM shipping
WHERE post_code = 'B1')
UNION
(SELECT *
FROM shipping
WHERE post_code = SUBSTRING('B1', 1, 1))) a
ORDER BY post_code DESC
LIMIT 1
person Marcus Adams    schedule 26.01.2012
comment
Спасибо, Маркус, я попробую через секунду и посмотрю, как пойдет. - person user398341; 26.01.2012