Как найти max в таблице

ProgramBox

Как найти max в таблице

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

#sql-server #sql-server-2012

#sql-сервер #sql-server-2012

Вопрос:

У нас есть таблица.

 ID     |department_ID|mod_date
---------------------------------
 A     | 33          | 12/03/2014
 A     | 44          | 23/04/2015
 A     | 55          | 20/05/2016
 B     | 44          | 26/01/2014
 B     | 33          | 24/02/2015
 B     | 12          | 24/12/2015
 B     | 55          | 12/02/2016
 C     | 13          | 22/05/2014
 C     | 14          | 01/05/2015
 C     | 55          | 02/07/2016
 C     | 14          | 03/08/2016
 C     | 55          | 05/10/2016

Из этой таблицы нам нужно извлечь те идентификаторы, в которых есть 55 department_id, но которые были изменены после 12/10/15 на что-либо другое, кроме 55 .

Пожалуйста, помогите с запросом, так как мне нужно сделать это для огромного объема данных.

1. что было изменено и какие действия вам нужно выполнить?

2. должно быть отслеживание предыдущего идентификатора отдела, чтобы мы могли определить, равно ли предыдущее значение 55 или нет

3. Приведите пример вывода, основанный на вашем примере ввода, а также на том, какой запрос вы пробовали до сих пор, и объясните, почему он не дает ожидаемых результатов

4. Текущий идентификатор отдела должен быть 55, но должен был измениться с любого другого идентификатора отдела на 55 после 12/10/2015.

5. Идентификатор результата будет A и B, но не C. Поскольку идентификатор отдела для A и B был изменен после 12/10/15 на многие другие идентификаторы отдела, но не на 55, и в настоящее время он равен 55. но для C отделы изменились на 55, а затем на 14, а затем снова на 55.

Ответ №1:

Если вы правильно поняли, то вы можете искать этот скрипт

 SELECT ID
FROM TableName
WHERE department_ID = 55 AND mod_date > '<your_date>'
GROUP BY ID
Having COUNT(department_ID) = 1

1. могу ли я также получить последний переход. Я имею в виду, что для идентификатора A мне нужны две записи с идентификаторами отдела 55 и 44, а также для идентификатора B

2. и у меня нет mod_date. Это должно быть максимум из них, группируемых по идентификатору элемента

3. Не уверен в ваших бизнес-требованиях, но если вы ищете два отдела, вы можете использовать предложение IN department_ID IN (55,44)

4. для данного примера второй идентификатор dep равен 44, но для всех остальных идентификаторов он не будет таким же.. поэтому нельзя использовать предложение in

Ответ №2:

Вы можете достичь этого с помощью подзапроса:

 CREATE TABLE #0 (ID varchar(100), department_ID int, mod_date date)

SET DATEFORMAT DMY

INSERT #0 VALUES
('A',33,'12/03/2014'),('A',44,'23/04/2015'),('A',55,'20/05/2016')
,('B',44,'26/01/2014'),('B',33,'24/02/2015'),('B',12,'24/12/2015')
,('B',55,'12/02/2016'),('C',13,'22/05/2014'),('C',14,'01/05/2015')
,('C',55,'02/07/2016'),('C',14,'03/08/2016'),('C',55,'05/10/2016');

SELECT DISTINCT latest.ID
FROM
(
    SELECT ID, mod_date = MAX(mod_date)
    FROM #0
    GROUP BY ID
) maximum
JOIN #0 latest
    ON latest.ID = maximum.ID
    AND latest.mod_date = maximum.mod_date
JOIN #0 older
    ON older.ID = latest.ID
    AND older.mod_date < latest.mod_date
    AND (older.department_ID <> latest.department_ID OR older.department_ID IS NULL)
WHERE latest.department_ID = 55
    AND older.mod_date > '2015-10-12'

1. Привет, спасибо за ваш ответ .. можем ли мы добавить что-то к этому запросу, чтобы он также выдавал последнюю транзакцию. Я имею в виду для

2. для 44 23/04/2015

3. Для A я должен получить две записи с идентификаторами отдела 44 и 55, а для B тоже самое. Извините за несколько комментариев, которые были по ошибке.

4. Хорошо, спасибо. Я заменил свой ответ новым, который устраняет эти проблемы.

Ответ №3:

Сортировка по дате:-

 SELECT * FROM table WHERE mod_date > '2015/10/12' AND 
department_ID=55 ORDER BY mod_date DESC Limit 1;

Сортировка по идентификатору:-

 SELECT * FROM table WHERE mod_date > '2015/10/12' AND 
department_ID=55 ORDER BY ID Limit 1;

Ответ №4:

может быть, это?

 declare @temp table (id varchar(1), department_id int, mod_Date date)

insert into @temp values ('A', 33, '20140312')
insert into @temp values ('A', 44, '20150423')
insert into @temp values ('A', 55, '20160520')
insert into @temp values ('B', 44, '20140126')
insert into @temp values ('B', 33, '20150224')
insert into @temp values ('B', 12, '20151224')
insert into @temp values ('B', 55, '20160212')
insert into @temp values ('C', 13, '20140522')
insert into @temp values ('C', 14, '20150501')
insert into @temp values ('C', 55, '20160702')
insert into @temp values ('C', 14, '20160803')
insert into @temp values ('C', 55, '20161005')

select id, max(mod_Date) as lastchanged
from   @temp
where  department_id = 55 
and    mod_date > '20151012'
group by id
having count(department_id) = 1

он возвращает :

 id  lastchanged 
A   2016-05-20  
B   2016-02-12

Ответ №5:

Вы можете сделать это с помощью ROW_NUMBER() twice . Добавление следующих двух столбцов к вашим данным:

 SELECT  ID,
        Department_ID,
        mod_date,
        RowNum = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY mod_date DESC),
        RowNum2 = ROW_NUMBER() OVER(PARTITION BY ID, Department_ID ORDER BY mod_date)
FROM    #T
WHERE   mod_Date > '20151012';

дает

 ID  Department_ID   mod_date    RowNum  RowNum2
--------------------------------------------------
A   55              2016-05-20  1       1
B   12              2015-12-24  2       1
B   55              2016-02-12  1       1
C   14              2016-08-03  2       1
C   55              2016-07-02  3       1
C   55              2016-10-05  1       2

Поскольку вас интересует только то, где текущий идентификатор отдела равен 55, вам необходимо выполнить фильтрацию RowNum = 1 AND Department_ID = 55 , которая оставляет:

 ID  Department_ID   mod_date    RowNum  RowNum2
--------------------------------------------------
A   55              2016-05-20  1       1
B   55              2016-02-12  1       1
C   55              2016-10-05  1       2

Обратите внимание, что RowNum2 for ID = C равно 2, поскольку это второй раз за период, когда C был равен 55, вас интересует только то, где идентификатор ранее не был изменен на 55, поэтому вам нужен дополнительный предикат RowNum2 = 1 для предоставления окончательного запроса:

 SELECT  ID,
        Department_ID,
        mod_date
FROM    (   SELECT  ID,
                    Department_ID,
                    mod_date,
                    RowNum = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY mod_date DESC),
                    RowNum2 = ROW_NUMBER() OVER(PARTITION BY ID, Department_ID ORDER BY mod_date)
            FROM    #T
            WHERE   mod_Date > '20151012'
        ) AS t
WHERE   Department_ID = 55
AND     RowNum = 1      -- CURRENT RECORD   
AND     RowNum2 = 1;    -- FIRST TIME ID HAS BEEN THIS DEPARTMENT

ПОЛНАЯ РАБОЧАЯ ДЕМОНСТРАЦИЯ

 CREATE TABLE #T (ID CHAR(1), Department_ID INT, mod_date DATE);
INSERT #T
VALUES
    ('A', 33, '20140312'),
    ('A', 44, '20150423'),
    ('A', 55, '20160520'),
    ('B', 44, '20140126'),
    ('B', 33, '20150224'),
    ('B', 12, '20151224'),
    ('B', 55, '20160212'),
    ('C', 13, '20140522'),
    ('C', 14, '20150501'),
    ('C', 55, '20160702'),
    ('C', 14, '20160803'),
    ('C', 55, '20161005');

SELECT  ID,
        Department_ID,
        mod_date
FROM    (   SELECT  ID,
                    Department_ID,
                    mod_date,
                    RowNum = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY mod_date DESC),
                    RowNum2 = ROW_NUMBER() OVER(PARTITION BY ID, Department_ID ORDER BY mod_date)
            FROM    #T
            WHERE   mod_Date > '20151012'
        ) AS t
WHERE   Department_ID = 55
AND     RowNum = 1      -- CURRENT RECORD   
AND     RowNum2 = 1;    -- FIRST TIME ID HAS BEEN THIS DEPARTMENT

Результаты

 ID  Department_ID   mod_date
----------------------------------
A   55              2016-05-20
B   55              2016-02-12