SQL СУММИРУЕТ и разделяет связанные таблицы

ProgramBox

SQL СУММИРУЕТ и разделяет связанные таблицы

Post author:admin
Запись опубликована:21 января, 2022
Post category:Вопросы по программированию

#mysql #sql-server #group-by #sum #invoice

Вопрос:

У меня есть следующие таблицы:

 create table Cars
(
  CarID int,
  CarType varchar(50),
  PlateNo varchar(20),
  CostCenter varchar(50),
  
);

insert into Cars (CarID, CarType, PlateNo, CostCenter) values 
(1,'Coupe','BC18341','CALIFORNIA'),
(2,'Hatchback','AU14974','DAKOTA'),
(3,'Hatchback','BC49207','NYC'),
(4,'SUV','AU10299','FLORIDA'),
(5,'Coupe','AU32703','NYC'),
(6,'Coupe','BC51719','CALIFORNIA'),
(7,'Hatchback','AU30325','IDAHO'),
(8,'SUV','BC52018','CALIFORNIA');

create table Invoices
(
  InvoiceID int,
  InvoiceDate date,
  CostCenterAssigned bit,
  InvoiceValue money 
);

insert into Invoices (InvoiceID, InvoiceDate, CostCenterAssigned, InvoiceValue) values 
(1, '2021-01-02', 0, 978.32),
(2, '2021-01-15', 1, 168.34),
(3, '2021-02-28', 0, 369.13),
(4, '2021-02-05', 0, 772.81),
(5, '2021-03-18', 1, 469.37),
(6, '2021-03-29', 0, 366.83),
(7, '2021-04-01', 0, 173.48),
(8, '2021-04-19', 1, 267.91);

create table InvoicesCostCenterAllocations
(
  InvoiceID int,
  CarLocation varchar(50)
);

insert into InvoicesCostCenterAllocations (InvoiceID, CarLocation) values 
(2, 'CALIFORNIA'),
(2, 'NYC'),
(5, 'FLORIDA'),
(5, 'NYC'),
(8, 'DAKOTA'),
(8, 'CALIFORNIA'),
(8, 'IDAHO');

Как я могу рассчитать общую стоимость счета, выделенную для этого автомобиля, на основе его центра затрат?

Если счет-фактура распределяется по автомобилям в определенных центрах затрат, то CostCenterAssigned для столбца устанавливается значение true, и центры затрат перечислены в InvoicesCostCenterAllocations таблице, связанной с Invoices таблицей InvoiceID столбцом. Если распределение центра затрат отсутствует ( CostCenterAssigned столбец является ложным), то стоимость счета — фактуры делится на общее количество автомобилей и суммируется.

Примеры данных в скрипке: http://sqlfiddle.com/#!18/9bd18/3

1. Что вы пробовали до сих пор? Почему это не сработало? Вы получили сообщение об ошибке? Неожиданные результаты? Что-то еще?

2. Вы не публиковали свой запрос здесь, скорее, он был только в скрипке SQL. Ваша главная проблема заключается в том, что ваш подзапрос вернет более одной записи (при условии, что вы ожидаете результатов от более чем одного центра и/или центра и некоторых не назначенных автомобилей).

3. @Lanu: Вы найдете мой запрос в ссылке SQL fiddle.

4. @Eli: Как суммировать возвращенные значения по подзапросу?

5. Я не думаю, что вы хотите, чтобы они были суммированы, так как вам захочется иметь возможность рассказать о разных центрах и т. Д. Попробуйте включить оператор CASE в основной запрос, и тогда у вас должен быть полный запрос, т. Е. ваша логика, чтобы цифры были записаны в центре

Ответ №1:

Структура данных здесь не идеальна, поэтому нам нужен дополнительный код для решения этой проблемы. Мне нужно было собрать количество автомобилей в каждом месте, а также распределить суммы по каждому счету-фактуре в зависимости от того, был ли он назначен какому-либо месту или нет. Я вывел итоговые данные для каждого типа счета, чтобы вы могли видеть компоненты, которые собираются вместе, они вам не понадобятся в конечном результате.

 ;WITH CarsByLocation AS(    
    SELECT  
         CostCenter
        ,COUNT(*) AS Cars
    FROM Cars 
    GROUP BY CostCenter
    UNION ALL
    SELECT  
         ''
        ,COUNT(*) AS Cars
    FROM Cars   
),CostCenterAssignedInvoices AS (
    SELECT 
         InvoicesCostCenterAllocations.CarLocation
        ,SUM(invoicevalue) / CarsByLocation.cars AS InvoiceTotal
    FROM Invoices 
    INNER JOIN InvoicesCostCenterAllocations ON invoices.InvoiceID = InvoicesCostCenterAllocations.InvoiceID
    INNER JOIN CarsByLocation on InvoicesCostCenterAllocations.CarLocation = CarsByLocation.CostCenter
    WHERE CostCenterAssigned = 1  --Not needed, put here for clarification
    GROUP BY InvoicesCostCenterAllocations.CarLocation,CarsByLocation.Cars
),UnassignedInvoices AS (
    SELECT 
         '' AS Carlocation
        ,SUM(invoicevalue)/CarsByLocation.Cars InvoiceTotal
    FROM Invoices 
    INNER JOIN CarsByLocation on CarsByLocation.CostCenter = ''
    WHERE CostCenterAssigned = 0
    group by CarsByLocation.Cars
)
SELECT 
      Cars.*
     ,cca.InvoiceTotal AS AssignedTotal
     ,ui.InvoiceTotal AS UnassignedTotal
     ,cca.InvoiceTotal   ui.InvoiceTotal AS Total
FROM Cars 
LEFT OUTER JOIN CostCenterAssignedInvoices CCA ON Cars.CostCenter = CCA.CarLocation
LEFT OUTER JOIN UnassignedInvoices UI ON UI.Carlocation = ''
ORDER BY 
     Cars.CostCenter
    ,Cars.PlateNo;