WedX - журнал о программировании и компьютерных науках

SQL — сложный запрос — суммирование нескольких строк в столбцы

У меня небольшая проблема с запросом, который я пытаюсь разработать.

Вот как выглядит моя таблица: -

Таблица учетных записей

ClientNo        AccountType          Balance
  1234             SUP1                25
  1234            SUP1.1               35
  1234             RET1                20
  1111             SUP1                50
  1111             DIS4                60

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

ClientNo   TotSupBal   TotSuppAccts   TotRetBal  TotRetAccts  TotDisBal   TotDisAccts
 1234         70             2           20          1            0             0
 1111         50             1            0          0            60            1

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

Типы учетных записей всегда будут начинаться с одних и тех же символов, однако в зависимости от того, сколько из этих учетных записей, число может быть любым, а последующие учетные записи всегда будут десятичными, а затем числом... Например. первая учетная запись SUP — это просто SUP1, однако следующая учетная запись SUP будет SUP1.1, затем SUP1.2 и т. д.

Я написал следующий запрос

SELECT ClientNo, SUM(Balance) AS TotSupBal, COUNT(AccountType) AS TotSuppAccts
FROM Account
WHERE (AccountType LIKE 'SUP1.%') OR (AccountType = 'SUP1')
GROUP BY ClientNo

*Причина, по которой существует 2 разных предложения WHERE, заключается в том, что я не могу просто использовать SUP1%, поскольку существуют такие учетные записи, как SUP12, которые не совпадают с SUP1.

Этот запрос работает нормально, однако он создает список только для тех, у кого тип учетной записи SUP. Как я могу получить одинаковый вывод, но в нескольких столбцах для каждого типа учетной записи?

Я использую Microsoft SQL 2008 R2.

sql sql-server-2008-r2
28.12.2012

Ответы:


1

PIVOT — это то, что вам нужно >> https://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Вот полностью рабочее решение:

WITH Accounts (AccountCategory, ClientNo, Balance) as (
  select 
    case 
        when AccountType like 'SUP%' then 'sup'
        when AccountType like 'RET%' then 'ret'
        when AccountType like 'DIS%' then 'dis' 
    end as AccountCategory,
    ClientNo, 
    Balance
  from Account
)
select * from (
  select ClientNo, sup as TotSupBal, ret as TotRetBal, dis as TotDisBal from Accounts as SourceTable PIVOT (
    SUM(Balance)
    FOR AccountCategory IN ([sup], [ret], [dis])
  ) as pt
) as sums inner join (
  select ClientNo, sup as TotSupAccts, ret as TotRetAccts, dis as TotDisAccts from Accounts as SourceTable PIVOT (
   COUNT(Balance)
   FOR AccountCategory IN ([sup], [ret], [dis])
  ) as pt
) as counts on sums.ClientNo = counts.ClientNo

Попробуйте на SqlFiddle: https://sqlfiddle.com/#!6/d5e91/26

28.12.2012

2

Позвольте мне предположить, что вы заранее знаете, какие бывают типы счетов. В этом случае вам просто нужна условная сумма агрегации:

select clientNo,
       sum(case when (AccountType LIKE 'SUP1.%') OR (AccountType = 'SUP1')
                then Balance
            end) as TotSupBal,
       sum(case when (AccountType LIKE 'SUP1.%') OR (AccountType = 'SUP1')
                then 1
                else 0
           end) as TotSupAccts,
       sum(case when left(AccountType, 3) = 'RET'
                then Balance
            end) as TotRetBal,
       sum(case when left(AccountType, 3) = 'RET'
                then 1
                else 0
           end) as TotRetAccts,
       . . .
from account
group by clientNo

Я не уверен, какова точная логика для других учетных записей, поэтому я просто смотрю на первые три символа.

28.12.2012

3 
SELECT
    ClientNo, 
    SUM(Balance) AS TotSupBal,
    COUNT(AccountType) AS TotSuppAccts,
    ret_bal AS TotRetBal,
    total_ret AS TotRetAccts
FROM 
    Account,
    (
        SELECT
            ClientNo c_num,
            SUM(Balance) AS ret_bal,
            COUNT(AccountType) total_ret
        WHERE AccountType LIKE 'RET%'
        GROUP BY ClientNo
    ) Table1RET_type  -- Your name for new table(You create new temporary table for you select) 
WHERE
    ((AccountType LIKE 'SUP1.%') OR (AccountType = 'SUP1'))
    AND Table1RET_type.c_num = ClientNo -- This is called join Table(google it for more info)
GROUP BY ClientNo

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

28.12.2012
Новые материалы
Как создать диаграмму градиентной кисти с помощью D3.js
Резюме: Из этого туториала Вы узнаете, как добавить градиентную кисть к диаграмме с областями в D3.js. Мы добавим градиент к значениям SVG и применим градиент в качестве заливки к диаграмме с..
Я хотел выучить язык программирования MVC4, но не мог выучить его раньше, потому что это выглядит сложно…
Просто начните и учитесь самостоятельно Я хотел выучить язык программирования MVC4, но не мог выучить его раньше, потому что он кажется мне сложным, и я бросил его. Это в основном инструмент..
Лицензии с открытым исходным кодом: руководство для разработчиков и создателей
В динамичном мире разработки программного обеспечения открытый исходный код стал мощной парадигмой, способствующей сотрудничеству, инновациям и прогрессу, движимому сообществом. В основе..
Объяснение документов 02: BERT
BERT представил двухступенчатую структуру обучения: предварительное обучение и тонкая настройка. Во время предварительного обучения модель обучается на неразмеченных данных с помощью..
Как проанализировать работу вашего классификатора?
Не всегда просто знать, какие показатели использовать С развитием глубокого обучения все больше и больше людей учатся обучать свой первый классификатор. Но как только вы закончите..
Работа с цепями Маркова, часть 4 (Машинное обучение)
Нелинейные цепи Маркова с агрегатором и их приложения (arXiv) Автор : Бар Лайт Аннотация: Изучаются свойства подкласса случайных процессов, называемых дискретными нелинейными цепями Маркова..
Crazy Laravel Livewire упростил мне создание электронной коммерции (панель администратора и API) [Часть 3]
Как вы сегодня, ребята? В этой части мы создадим CRUD для данных о продукте. Думаю, в этой части я не буду слишком много делиться теорией, но чаще буду делиться своим кодом. Потому что..
Для любых предложений по сайту: wedx@cp9.ru