Как я могу сопоставить строку в SQL Server только один раз?
У меня возникла следующая проблема, и я прошу вашей помощи при объединении двух таблиц в SQL Server 2016 (v13).
У меня есть 2 стола, Revenues и Cashins.
Revenues:
Cashins:
Цель состоит в том, чтобы автоматически сопоставлять денежные суммы с доходами (но только один раз!).
Обе таблицы имеют свои уникальные идентификаторы, но столбцы, используемые для объединения этих таблиц,
- Идантификационный номер продукта
- Счет №
- Количество
Для записей только с одной строкой в каждой таблице с такими критериями все работает нормально.
Однако иногда в этих столбцах есть несколько строк с одинаковым значением (как указано выше), но с уникальным идентификатором (это не ошибка, а так и должно быть).
Проблема в том, что при объединении получается декартово произведение.
Чтобы воссоздать таблицы, здесь операторы:
DROP TABLE IF EXISTS Revenues
GO
CREATE TABLE Revenues
(
RevenueID [nvarchar](10) NULL,
ProductID [nvarchar](10) NULL,
InvoiceNo [nvarchar](10) NULL,
Amount money NULL
)
GO
DROP TABLE IF EXISTS CashIns
GO
CREATE TABLE CashIns
(
CashinID [nvarchar](10) NULL,
ProductID [nvarchar](10) NULL,
InvoiceNo [nvarchar](10) NULL,
Amount money NULL
)
GO
INSERT INTO [Revenues] VALUES ('123', '456', '987', 1000)
INSERT INTO [Revenues] VALUES ('234', '456', '987', 1000)
INSERT INTO [CashIns] VALUES ('ABC', '456', '987', 1000)
INSERT INTO [CashIns] VALUES ('BCD', '456', '987', 1000)
Желаемый результат:
SELECT
R.RevenueID,
R.ProductID,
R.InvoiceNo,
R.Amount,
C.CashinID,
FROM
[Revenues] R
LEFT JOIN
[CashIns] C ON R.ProductID = C.ProductID
AND R.InvoiceNo = C.InvoiceNo
AND R.Amount = C.Amount
Полученные результаты:
Что теоретически имеет смысл, но я просто не могу найти решение, в котором каждая строка используется только один раз.
Две вещи, которые я нашел и попробовал, — это оконные функции и функция OUTER APPLY с выбором TOP(1). Оба пришли к одному и тому же результату:
SELECT
*
FROM
[Revenues] R
OUTER APPLY
(SELECT TOP(1) *
FROM [CashIns] C) C
Который возвращает нужные столбцы из таблицы Revenues, но соответствует только первому появлению из таблицы Cashins:
Я также подумал о чем-то вроде обновления таблицы Revenues, чтобы совпавший CashinID находился рядом со строкой, а затем каждый раз проверял, что CashinID еще не используется в этой таблице, но я не мог заставить это работать...
Заранее большое спасибо за любую помощь или подсказку в правильном направлении!
Технически ваш 4-строчный вывод означает: «Цель состоит в том, чтобы автоматически сопоставлять наличные деньги с доходами (но только один раз!»). поскольку нет никакой связи между RevenueID и CashinId
Ответы 1
Как я уже сказал в своем комментарии, у вас есть фундаментальная проблема с вашими отношениями данных. Вам нужно сослаться на уникальный идентификатор другой таблицы в одной из ваших таблиц. Если вы этого не сделаете, то сможете только упорядочить свои транзакции в обеих таблицах и соединить их по номеру строки. Вы используете надежду и молитву, чтобы объединить свои данные вместо ненадежных идентификаторов.
--This example orders the transactions in each transaction table and uses
--the order number to join them.
WITH RevPrelim AS (
SELECT *
, ROW_NUMBER() OVER(PARTITION BY InvoiceNo, ProductID, Amount ORDER BY RevenueID) AS row_num
FROM [Revenues] R
), CashinsPrelim AS (
SELECT *
, ROW_NUMBER() OVER(PARTITION BY InvoiceNo, ProductID, Amount ORDER BY CashinID) AS row_num
FROM [CashIns] AS C
)
SELECT *
FROM RevPrlim AS r
LEFT OUTER JOIN CashinsPrelim AS c
ON c.ProductID = r.ProductID
AND c.InvoiceNo = r.InvoiceNo
AND c.Amount = r.Amount
AND c.row_num = r.row_num
Большое спасибо за ваш ответ, @TimJarosz! Вы правы, это не лучшая база данных для работы, но это то, что у меня есть. По крайней мере, я могу положиться на тот факт, что не может быть никаких ложных значений с этими тремя условиями соединения - проблема была только в дублировании. Для тестовых данных ваше решение работает, большое спасибо! Я собираюсь проверить это на реальных данных. Еще раз спасибо и всего наилучшего.
У вас есть фундаментальная проблема с вашими отношениями данных. Вы присоединяетесь по неуникальным идентификаторам. Единственное возможное решение, которое я вижу, это упорядочить строки в Revenues и упорядочить строки в Cashins и сопоставить row_number.