SQL Server 2014: как получить записи, содержащие строки с разными значениями только определенного столбца

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

Мне нужен список учетных записей, в которых есть:

  1. Более 1 номерного знака и
  2. Идентификатор более 1 страхового счета

Я использую 3 таблицы:

  • Account таблица A для информации об аккаунте
  • Plate таблица P для информации о номерных знаках
  • EventLog таблица E для информации о том, когда было отправлено письмо с уведомлением

Отношение:

A.AccountId = P.AccountId = E.AccountId

Мой код на данный момент:

SELECT 
    A.AccountNumber, A.AccountId, A.CurrentBalance,
    E.NotificationDt,
    P.LicPlateNo, A.RegistrationTypeId, P.InsuranceAccountId
FROM 
    Account A
INNER JOIN 
    Plate P ON A.AccountId = P.AccountId
INNER JOIN  
    EventLog E ON A.AccountId = E.AccountId
WHERE 
    A.RegistrationTypeId = 3
    AND P.EndDate IS NULL               
    AND A.AccountStatusId = 1           
    AND A.DelinquencyStatusId = 11
    AND E.EventId = 64
    AND P.PlateStatusId = 1
ORDER BY 
    AccountNumber, A.AccountId, P.LicPlateNo

Мои образцы данных выглядят так:

+---------+---------+---------+-------------+----------+---+--------+
|AccNo    | AccId   |CurrBal  |NotifDt      |LicPlateNo|RTI|InsAccId|
+---------+---------+---------+-------------+----------+---+--------+
|21234561 |123456   |    56.79|   2017-01-01|ABC123    |  3|1234ABC |
|21234572 |123457   |    83.25|   2017-01-03|DEF345    |  3|345DEF  |
|22345672 |234567   |   104.38|   2017-01-03|GHI345    |  3|567GHI  |
|22345672 |234568   |   104.38|   2017-01-03|JKL678    |  3|789MNO  |
+---------+---------+---------+-------------+----------+---+--------+

В моих выборочных данных последние два столбца относятся к одному и тому же AccountNumber, у которого разные LicencePlateNos и разные Insurance AccountIds.

Я бы хотел, чтобы мои данные выглядели так:

+---------+---------+---------+-------------+----------+---+--------+
|22345672 |234567   |   104.38|   2017-01-03|GHI345    |  3|567GHI  |
|22345672 |234568   |   104.38|   2017-01-03|JKL678    |  3|789MNO  |
+---------+---------+---------+-------------+----------+---+--------+

Каким будет код, который предоставит мне эти данные?

Я пробовал использовать предложение GROUP BY... HAVING, но это дает мне только уникальные AccountNumber с несколькими номерными знаками. Я хотел бы также показать разные номера номерных знаков и разные InsuranceAccountID.


sql sql-server having
person user1777929    schedule 04.07.2017    source источник
comment
Можете ли вы добавить заголовки столбцов к вашим образцам данных? Я предполагаю, что он должен соответствовать вашему образцу запроса, но было бы неплохо указать.   -  person Doug Knudsen    schedule 05.07.2017
comment
Добавлены сокращенные заголовки.   -  person user1777929    schedule 05.07.2017

Ответы (2)


arrow_upward
1
arrow_downward

Легкий способ сделать это - ПРИСОЕДИНЯТЬСЯ к набору результатов, который позволит вам проверить одно или оба условия. Просто включите соответствующее предложение фильтра:

...
INNER JOIN EventLog E ON A.AccountId = E.AccountId

INNER JOIN
(
    SELECT
        Plate.AccountId,
        LicensePlateCount = COUNT(DISTINCT Plate.LicPlateNo),
        InsuranceAccountCount = COUNT(DISTINCT Plate.InsuranceAccountId)
    FROM
        Plate
    WHERE
        Plate.EndDate IS NULL
        AND Plate.PlateStatusId = 1
    GROUP BY
        Plate.AccountId
) PlateAccounts ON A.AccountId = PlateAccounts.AccountId
    -- 1) More than 1 License Plate:
    AND PlateAccounts.LicensePlateCount > 1
    -- 2) More than 1 Insurance Account IDs:
    AND PlateAccounts.InsuranceAccountCount > 1

WHERE A.RegistrationTypeId = 3
...
person Doug Knudsen    schedule 04.07.2017
comment
Это выглядит многообещающе. Спасибо, Дуг. Но какой код я бы разместил НАД ВНУТРЕННИМ СОЕДИНЕНИЕМ? - person user1777929; 05.07.2017
comment
@ user1777929 - я добавил строку кода вверху и внизу, чтобы вы могли видеть, куда вставить этот код. - person Doug Knudsen; 05.07.2017
comment
Спасибо, Дуг. Сработало отлично. Однако, хотя я получаю разные строки с несколькими номерными знаками, я получаю несколько строк с одинаковыми номерами страховых счетов ____ На самом деле цель состояла в том, чтобы получить уникальные, разные номера страховых счетов для каждой учетной записи, в которой они были ... потому что в идеале каждый на счету должен быть только ОДИН номер страхового счета ...... Большое спасибо за вашу помощь. Я очень ценю это. - person user1777929; 05.07.2017
comment
@ user1777929 - Можете ли вы привести пример результата, который вы видите, но это не то, что вы ищете? Если вам нужны только учетные записи с несколькими номерами страховых счетов, попробуйте закомментировать строку AND PlateAccounts.LicensePlateCount ›1. - person Doug Knudsen; 06.07.2017

arrow_upward
1
arrow_downward

Простой способ - использовать ваш запрос в качестве основы и подсчитывать значения с более чем одной строкой:

SELECT a.*
FROM (SELECT A.AccountNumber, A.AccountId, A.CurrentBalance, E.NotificationDt,
             P.LicPlateNo, A.RegistrationTypeId, P.InsuranceAccountId,
             COUNT(*) OVER (PARTITION BY A.AccountNumber) as cnt
      FROM Account A INNER JOIN
           Plate P
           ON A.AccountId = P.AccountId INNER JOIN
           EventLog E
           ON A.AccountId = E.AccountId
      WHERE A.RegistrationTypeId = 3 AND
            P.EndDate IS NULL AND  
            A.AccountStatusId = 1 AND        
            A.DelinquencyStatusId = 11 AND
            E.EventId = 64 AND
            P.PlateStatusId = 1
     ) a
WHERE cnt > 1;   
ORDER BY AccountNumber, AccountId, LicPlateNo;
person Gordon Linoff    schedule 04.07.2017
comment
Этот код хорош, но я получаю повторяющиеся номера страховых счетов. Мне нужны номера счетов с отдельными номерными знаками и отдельные номера страховых счетов. Спасибо за вашу помощь. - person user1777929; 05.07.2017