Блог

Главная - Вопросы по программированию - Выберите только строки, в которых изменяется значение

Выберите только строки, в которых изменяется значение

#sql #sql-server #tsql #sql-server-2008-r2

#sql #sql-сервер #tsql #sql-server-2008-r2

Вопрос:

Я использую SQL Server 2008 R2 и пытаюсь написать запрос, который возвращает все строки, в которых изменяется столбец.

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

CustomerID InvoiceID DateInvoice Сумма
209 9725772 2020-12-10 9.50
209 9725773 2021-01-15 1.50
209 9725774 2021-01-17 2.50
209 9725775 2021-01-19 3.50
209 9725776 2021-01-21 3.50 *
209 9725777 2021-01-23 9.50
209 9725778 2021-01-25 9.50 *
209 9725779 2021-01-25 3.50
210 9726132 2021-02-02 3.50
210 9726133 2021-03-02 9.50
210 9726134 2021-04-02 9.50 *

Я добавил звездочку в столбец суммы для строк, которые я не хочу возвращать.

Любые предложения будут приняты с благодарностью.

Комментарии:

1. Если бы вы использовали поддерживаемую версию SQL Server (даже с расширенной поддержкой), это было бы тривиально … ЗАДЕРЖКА (Transact-SQL) / ОПЕРЕЖЕНИЕ (Transact-SQL) . Вместо этого вам нужно будет использовать самосоединение к предыдущей строке.

Ответ №1:

Вы можете использовать функцию окна LAG() в более поздней, чем ваша версия SQL Server, но без нее вы можете использовать коррелированный подзапрос в предложении WHERE:

 SELECT t1.*
FROM tablename t1
WHERE t1.Amount <> COALESCE(
  (
    SELECT TOP 1 t2.Amount
    FROM tablename t2
    WHERE t2.CustomerId = t1.CustomerId AND t2.DateInvoice < t1.DateInvoice
    ORDER BY t2.DateInvoice DESC
  ), -1)

Посмотрите демонстрацию.
Результаты:

CustomerID InvoiceID DateInvoice Сумма
209 9725772 2020-12-10 9.50
209 9725773 2021-01-15 1.50
209 9725774 2021-01-17 2.50
209 9725775 2021-01-19 3.50
209 9725777 2021-01-23 9.50
209 9725779 2021-01-25 3.50
210 9726132 2021-02-02 3.50
210 9726133 2021-03-02 9.50

Комментарии:

1. Это действительно приятно, спасибо за вашу помощь, но есть ли лучший способ сделать бит «-1»? Возможно, что сумма счета может быть -1, я думаю, я мог бы поставить -9999999.

2. @fosbie Вы можете изменить его на любое число, которое, по вашему мнению, никогда не будет существовать в столбце Amount . Я использовал COALESCE() вместо подзапроса, чтобы сократить код.

3. @fosbie проверьте здесь код с подзапросом: dbfiddle.uk /…

Ответ №2:

Вы могли бы использовать CTE с ROW_NUMBER и LEFT JOIN для предыдущей строки:

 WITH CTE AS(
    SELECT CustomerId,
           InvoiceId,
           DateInvoice,
           Amount,
           ROW_NUMBER () OVER (PARTITION BY CustomerID ORDER BY DateInvoice ASC) AS RN
    FROM dbo.YourTable)
SELECT C1.CustomerId,
       C1.InvoiceId,
       C1.DateInvoice,
       C1.Amount
FROM CTE C1
     LEFT JOIN CTE C2 ON C1.CustomerId = C2.CustomerId
                     AND C1.Amount = C2.Amount
                     AND C1.RN = C2.RN   1
WHERE C2.CustomerId IS NULL;

Использование примеров данных forpas: db<>fiddle

Но LAG / LEAD было бы намного проще.

Комментарии:

1. На самом деле это работает намного быстрее, чем в других примерах, спасибо!

Ответ №3:

Я только что нашел способ сделать это, но для меня это выглядит ужасно, должен быть более понятный способ сделать это.

 SELECT t.CustomerId,
t.InvoiceId,
t.DateInvoice,
t.Amount,
(SELECT TOP 1 Amount 
 FROM #test t1 
 WHERE t1.CustomerId=t.CustomerId AND t1.DateInvoice<t.DateInvoice 
 ORDER BY DateInvoice DESC) AS PrevAmount
 FROM #test AS t
 WHERE ((SELECT TOP 1 Amount 
    FROM #test t1
    WHERE t1.CustomerId=t.CustomerId AND t1.DateInvoice<t.DateInvoice 
    ORDER BY DateInvoice DESC)) <> Amount 
 Or ((SELECT TOP 1 Amount 
 FROM #test t1 
 WHERE t1.CustomerId=t.CustomerId AND t1.DateInvoice<t.DateInvoice 
 ORDER BY DateInvoice DESC)) Is Null
Метки: