Проблема с таблицей соединения SQL «один ко многим»
У меня есть 2 таблицы, которые являются отношениями один ко многим. Я хочу объединить обе таблицы, но результат, показанный после объединения, не тот, который мне нужен. Ниже представлена таблица и примеры данных.
Create table #temp (
DONo varchar(10),
Amount decimal(18,2),
OrderNo varchar(10)
)
Create table #order (
Item varchar(10),
Amount decimal(18,2),
OrderNo varchar(10),
PackingDate date
)
Insert into #temp(DONo, Amount, OrderNo)
VALUES('A123', 1000, 'O123')
Insert into #temp(DONo, Amount, OrderNo)
VALUES('A124', 1100, 'O124')
Insert into #order (OrderNo, Item, Amount, PackingDate)
VALUES('O123', 'Pen', 100, '2024-02-01')
Insert into #order (OrderNo, Item, Amount, PackingDate)
VALUES ('O123', 'Color', 800, '2024-02-01')
Insert into #order (OrderNo, Item, Amount, PackingDate)
VALUES ('O123', 'Box', 100, '2024-02-02')
Я попробовал ниже запрос на соединение
select
do.DONo, do. Amount, do.OrderNo, SUM(so.Amount)[[OrderAmount], so.PackingDate
from #temp do
left join
#order so
on
do.OrderNo = so.OrderNo
group by
do.DONo, do. Amount, do.OrderNo, so.PackingDate
Вот результат приведенного выше запроса
DONo DOAmount OrderNo Amount PackingDate
A123 1000.00 O123 900.00 2024-02-01
A123 1000.00 O123 100.00 2024-02-02
A124 1100.00 O124 NULL NULL
Ожидаемый результат, который я хочу, как показано ниже
DONo DOAmount OrderNo Amount PackingDate
A123 1000.00 O123 900.00 2024-02-01
A123 O123 100.00 2024-02-02
A124 1100.00 O124 NULL NULL
Поскольку номер DO одинаков для заказа O123, сумму DO мне нужно указать один раз. Причина, по которой мне нужно показать количество DO один раз, заключается в том, что при визуализации инструменты просуммируют количество DO, и на гистограмме оно увеличится вдвое.
Я думаю, что мое направление неверно и дизайн моей таблицы был неправильным. Пожалуйста, подскажите мне, как я могу это исправить и показать нужный мне результат.
Отвечает ли это на ваш вопрос? Как получить значение первой строки из каждой группы и применить только к первой записи, остальное в SQL Server будет равно 0 или нулю
Это похоже на проблему X/Y. «потому что при визуализации инструменты суммируют сумму DO» — Можете ли вы изменить их, чтобы этого не делать? Вместо суммирования они могут сделать что-то еще, например. выбрать самый большой? Какие инструменты вы используете?
@ThomA, спасибо, что указал мне направление. Теперь это работа.
Ответы 2
Ваш дизайн в порядке, и ваш текущий результат вполне действителен.
Это проблема не SQL, а инструмента визуализации, который вы используете.
Однако, если вы не можете найти способ, позволяющий инструменту визуализации игнорировать несколько значений одного и того же DONo, все равно есть способ обработать это в чистом T-SQL, используя RowNumber и общее табличное выражение:
;WITH CTE AS
(
select
do.DONo, do.Amount, do.OrderNo, SUM(so.Amount)[OrderAmount], so.PackingDate,
ROW_NUMBER() OVER (PARTITION BY do.DONo ORDER BY so.PackingDate) As RN
from #temp do
left join
#order so
on
do.OrderNo = so.OrderNo
group by
do.DONo, do. Amount, do.OrderNo, so.PackingDate
)
SELECT DONo, IIF(RN = 1, Amount, NULL) As Amount, OrderNo, OrderAmount, PackingDate
FROM CTE
ORDER BY DONo, PackingDate;
Примечание. Символ точки с запятой (;) является символом-терминатором.
На самом деле он не стоит перед ключевым словом WITH, однако, поскольку в T-SQL он обычно необязателен (если только следующий оператор не является общим табличным выражением), общепринятой практикой является начало общих табличных выражений с точки с запятой перед ключевым словом WITH.
Я не думаю, что CTE здесь вообще нужен, посмотрите это демо
@GuidoG Я согласен, так лучше. хотите опубликовать в качестве ответа или отредактировать это в этом?
я это написал в ответе
просто замените
do.Amount,
этим
case when row_number() over (partition by do.DONo order by do.DONo) = 1 then do.Amount else null end as Amount,
полный запрос
select do.DONo,
case when row_number() over (partition by do.DONo order by do.DONo) = 1 then do.Amount else null end as Amount,
do.OrderNo,
sum(so.Amount)OrderAmount,
so.PackingDate
from temp do
left join orders so on do.OrderNo = so.OrderNo
group by
do.DONo, do. Amount, do.OrderNo, so.PackingDate
результат
Посмотрите это демо
Что означает пробел? Это не
NULL, как вы явно указываете значенияNULL, но это также не может быть строка нулевой длины, посколькуDOAmountявно является числовым типом. Хотя, похоже, вам просто нужно проверить значениеROW_NUMBERи изменить значение, если оно не1.