Блог

Я хочу объединить несколько столбцов строк как одну запись в MySQL

Например: фактические данные

 --------------------------------------------------------------
     id         name          Loantype        Amount
--------------------------------------------------------------
     1          ABC              1            500000
     2          ABC              2            3500000
     3          XYZ              1            250000
     4          XYZ              2            2500000
  

Я попытался со следующим запросом

 SELECT
    id,
    (
        CASE Loantype
            WHEN 1 THEN Amount
            ELSE NULL
        END
    ) AS PersonalLoan,
    (
        CASE Loantype
            WHEN 2 THEN Amount
            ELSE NULL
        END
    ) AS HomeLoan
FROM
    customer
WHERE
    name = 'ABC'
  

но результат выглядит следующим образом

 --------------------------------------------------------------
     id         name          PersonalLoan    HomeLoan
--------------------------------------------------------------
     1          ABC            500000         NULL
     1          ABC            NULL         2500000
  

Ожидаемый набор результатов

 --------------------------------------------------------------
     id         name          PersonalLoan    HomeLoan
--------------------------------------------------------------
     1          ABC            500000         3500000
  

Вы можете самостоятельно объединить таблицу, чтобы вы могли объединить 2 вида кредитов в одну строку:

 SELECT t1.id, t1.name, t1.amount AS personal, t2.amount AS home
FROM customer AS t1
LEFT JOIN customer AS t2 ON t1.name = t2.name AND t2.loantype = 2
WHERE t1.loantype = 1
  

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

 SELECT t1.id, t1.name, t1.amount AS personal, t2.amount AS home
FROM (SELECT * FROM customer WHERE name = "ABC" AND loantype = 1) AS t1
LEFT JOIN customer AS t2 ON t1.name = t2.name AND t2.loantype = 2
  

• Обратите внимание, что, вообще говоря, вы не должны денормализовать данные в SQL (например, преобразование строк в столбцы: SQL ориентирован на строки, а не на столбцы) — я предполагаю, что это упрощает логику отображения — просто будьте осторожны с использованием подобных запросов, когда вы хотите передать значимые данные в другие части базы данныха не напрямую пользователю.
• GROUP BY Сначала вам нужно.
• Вы также можете упростить свои CASE выражения: ELSE NULL всегда неявно, и если CASE WHEN выражение представляет собой сравнение на равенство, вы можете использовать более простой синтаксис в стиле switch.
  • Так CASE WHEN a = b THEN c ELSE NULL END что можно упростить до CASE a WHEN b THEN c END .
• Я добавил COALESCE , чтобы запрос возвращался 0 для SUM агрегатов, если нет совпадающих строк вместо NULL . Обратите внимание, что COUNT (в отличие SUM от) обычно не нужно заключать в a COALESCE (хотя я забыл, как именно MySQL обрабатывает это — это также зависит от того, какую версию MySQL вы используете и какие параметры строгого режима и соответствия ANSI / ISO включены).

• Обратите внимание, что опубликованный вами дизайн базы данных, похоже, позволяет одному и тому же customer.name иметь несколько одинаковых заимствований loantype .

  • Вы можете избежать этого, добавив UNIQUE CONSTRAINT или UNIQUE INDEX или используя составной первичный ключ:

    • CREATE UNIQUE INDEX UX_name_loantype ON customer ( name, loantype )

  • Чтобы эти строки не вызывали проблем в этом запросе, здесь используются a SUM и a COUNT , чтобы читателям было ясно, что данные являются совокупными по нескольким строкам:

 SELECT
    name,
    
    COUNT( CASE Loantype WHEN 1 THEN 1 END ) AS CountPersonalLoans,
    COALESCE( SUM( CASE Loantype WHEN 1 THEN Amount END ), 0 ) AS SumPersonalLoans,

    COUNT( CASE Loantype WHEN 2 THEN 1 END ) AS CountHomeLoans,
    COALESCE( SUM( CASE Loantype WHEN 2 THEN Amount END ), 0 ) AS SumHomeLoans

FROM
    customer
GROUP BY
    name
  

Чтобы максимизировать повторное использование кода запроса, если вы хотите фильтровать name , преобразуйте это в VIEW — или, если это одноразовый, сделайте его CTE запросом, например:

 WITH aggs AS (
    SELECT
        name,
        
       COUNT( CASE Loantype WHEN 1 THEN 1 END ) AS CountPersonalLoans,
        COALESCE( SUM( CASE Loantype WHEN 1 THEN Amount END ), 0 ) AS SumPersonalLoans,

        COUNT( CASE Loantype WHEN 2 THEN 1 END ) AS CountHomeLoans,
        COALESCE( SUM( CASE Loantype WHEN 2 THEN Amount END ), 0 ) AS SumHomeLoans

    FROM
        customer
    GROUP BY
        name
)
SELECT
    name,
    CountPersonalLoans,
    SumPersonalLoans,
    CountHomeLoans,
    SumHomeLoans
FROM
    aggs
WHERE
    name = 'ABC'
ORDER BY
    name