Intereting Posts
Сложные предложения SQL-запросов, пожалуйста сценарий проектирования реляционных баз данных Невозможно увидеть значение поля BIT MySQL при использовании SELECT Материализованный взгляд на таблицы: каковы преимущества? Выполнение общего количества повторной группы по элементам в зависимости от времени в Oracle SQL Как добавить часы, минуты, секунды в dateadd sql? Как удалить либо первый, либо последний набор строк в динамическом режиме Doctrine Query, чтобы найти общее количество результатов в MySQL с помощью LIMIT Преобразование простого запроса в сложную именованную область в RoR Python SQLite3 SQL Injection Vulnerable Code Функция PostgreSQL для итерации через / действие во многих строках с состоянием DB Независимое программирование на Java – Предложения? Почему возникает ошибка при попытке суммирования значений столбца, полученного в ResultSet? Использование критериев и прогнозов Hibernate для выбора нескольких отдельных столбцов Запрос MySQL для поиска поля с помощью строки JSON

SQL-запрос от одного до многих отношений объединяется без дубликатов

Я выполняю запросы в SQL Server 2008. У меня есть таблица sales таблица payments . Иногда у продажи есть несколько способов оплаты (часть подарочной карты + часть наличных денег или часть кредита + часть наличных денег и т. Д.), Так что я хочу чтобы сделать это список продаж и платежей за каждую продажу в таблице.

Если я делаю LEFT JOIN ON sales.SaleID = payments.SaleID Я получаю дубликаты рядов продаж, когда есть несколько строк соответствия.

Итак, что я делаю, это получение всех продаж и подсчет количества подходящих строк платежей с (SELECT COUNT(*) FROM payments WHERE payments.SaleID = sales.SaleID) AS NumOfPayments . Затем в моем скрипте PHP я проверяю количество платежей, и если это > 1 я запускаю еще один запрос, чтобы получить детали оплаты.

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

 ----------------------------------------------------- | SaleID | SaleDate | Amount | Payments | ----------------------------------------------------- | 123 | 2013-07-23 | $ 19.99 | Cash: $ 19.99 | | 124 | 2013-07-23 | $ 7.53 | Cash: $ 7.53 | | 125 | 2013-07-23 | $174.30 | Credit: $124.30 | | | | | GiftCard: $ 50.00 | | 126 | 2013-07-23 | $ 79.99 | Cash: $ 79.99 | | 127 | 2013-07-23 | $100.00 | Credit: $ 90.00 | | | | | Cash: $ 10.00 | ----------------------------------------------------- 

Если в продажах 125 и 127 перечислены многочисленные платежи, но информация о продаже появляется только один раз и не дублируется для каждого платежа.

Таблицы sales и payments выглядят следующим образом:

 Sales Payments --------------------------------- -------------------------------------------- | SaleID | SaleDate | Amount | | PaymentID | SaleID | PmtMethod | PmtAmt | --------------------------------- -------------------------------------------- | 123 | 2013-07-23 | $ 19.99 | | 158 | 123 | 4 | $ 19.99 | | 124 | 2013-07-23 | $ 7.53 | | 159 | 124 | 4 | $ 7.53 | | 125 | 2013-07-23 | $174.30 | | 160 | 125 | 2 | $124.30 | | 126 | 2013-07-23 | $ 79.99 | | 161 | 125 | 3 | $ 50.00 | | 127 | 2013-07-23 | $100.00 | | 162 | 126 | 4 | $ 79.99 | --------------------------------- | 163 | 127 | 2 | $ 90.00 | | 164 | 127 | 4 | $ 10.00 | -------------------------------------------- 

Мне кажется, что если я смогу сделать это с помощью SQL, это будет быстрее. Есть ли способ сделать это с помощью чистого SQL вместо того, чтобы использовать код на стороне сервера для запуска условных запросов.

Я бы не смешивал извлечение данных и отображение данных, о чем я думаю, о чем вы спрашиваете. У вас есть какой-то столбец, указывающий, какой платеж должен отображаться первым? Я думаю, что-то вроде:

 SELECT columnlist, rn = ROW_NUMBER() OVER (PARTITION BY sales.salesID ORDER BY payment.paymentID) FROM sales JOIN payments ON sales.salesID=payments.salesID 

Затем в вашем графическом интерфейсе просто отобразите значения для первых 3 столбцов, где RN = 1, и затушите значения, где RN> 1.

В интерфейсе, вероятно, проще сделать это.

Основной запрос, который вы хотите:

  select s.saleID, s.SaleDate, s.Amount, p.PaymentType, p.PaymentAmount, ROW_NUMBER() over (partition by p.SaleId order by p.PaymentAmount desc) as seqnum from sales s join payments p on p.saleID = s.saleId order by 1, 2 

Однако вы пытаетесь заполнить поля. Для этого вам нужно преобразовать все поля в строки и затем проверить, находятся ли они в первой строке для каждого SaleId :

 select (case when seqnum > 1 then '' else CAST(SaleId as varchar(255)) end) as SaleId, (case when seqnum > 1 then '' else CONVERT(varchar(10), SaleDate, 121) end) as SaleDate, (case when seqnum > 1 then '' else '$'+STR(amount, 6, 2) end) as Amount, PaymentType, PaymentAmount from (select s.saleID, s.SaleDate, s.Amount, p.PaymentType, p.PaymentAmount, ROW_NUMBER() over (partition by p.SaleId order by p.PaymentAmount desc) as seqnum from sales s join payments p on p.saleID = s.saleId ) sp order by SaleId, SaleDate; 

Это не тот тип операций, для которого предназначен SQL. SQL работает с таблицами, где все столбцы имеют одинаковое значение. Здесь вы вводите другое значение для столбца, в зависимости от его положения в продаже. Да, SQL может это сделать. Нет, это непросто.

Следующий запрос даст нужные результаты, вам нужно пропустить столбец SalesIDToSort.

  ;with SalesData as ( select Sales.SalesID, Sales.SalesDate, Sales.TotalAmount, Payments.PaymentMode, Payments.Amount, Row_Number() over(Partition by Sales.SalesID order by Payments.PaymentID) RowNum from Payments inner join Sales on Payments.SalesID = Sales.SalesID ) select SalesID, SalesDate, TotalAmount, PaymentMode, Amount, SalesID SalesIDToSort from SalesData where SalesData.RowNum = 1 union all select null, null, null, PaymentMode, Amount, SalesID SalesIDToSort from SalesData where SalesData.RowNum > 1 order by 6 

Рабочая демонстрация

Вы можете попробовать gourp с помощью функции, такой как Format to:

 select salesid, salesDate, Amount, FORMAT(payment, 9999.999) as payments from FROM payments WHERE payments.SaleID = sales.SaleID group by salesid; 

это может сработать, если у вас есть FORMAT в качестве доступной функции.