Запрос доступа — возврат неповторяющихся и определенных повторяющихся записей

В данный момент я сталкиваюсь со стенами, пытаясь написать операторы, которые возвращали бы определенные записи в таблице, которые могут иметь какое-то дублированное уникальное значение (идентификатор сотрудника). См. пример ниже: (Таблица ниже была импортирована с использованием сохраненного импорта, и все поля имеют тип данных ShortText, и при импорте нет настройки PK или индексов)

ЭМПИД номер акта Положение дел Дата запроса Ластдатечанже 123 185236 Закрыто 01.02.2019 01.01.2020 123 210390 Открыть 01.01.2020 123 312395 Закрыто 01.01.2020 432 832102 Открыть 01.06.2020 313 921235 Закрыто 03.03.2020 01.05.2020 313 952305 Закрыто 03.03.2020 610 182349 Закрыто 01.11.2018

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

  1. Макс(CDate([ReqDate]))
  2. [Положение дел]
  3. Если обнаружены повторяющиеся записи EMPID, выберите Open [Status]
  4. Если обнаружены повторяющиеся записи EMPID, но только [Статус закрыто], верните ту, где [LastDateChange] не равно нулю

Единственный способ, которым я могу это сделать, - это несколько операторов:

  • Один для возврата Max [ReqDate] с [Status] Open
  • Один для возврата Max [ReqDate] с другим оператором, рассматривающим дубликаты с Closed [Status] And [LastDateChange] не равен нулю
  • Создайте временную таблицу, которая будет принимать записи из вышеуказанных запросов, прежде чем добавлять их в итоговую таблицу.

Это логика, о которой я думал, но пока застрял на ее построении (кажется, что слишком ветрено). Конечный результат, который я ищу, будет следующим:

ЭМПИД номер акта Положение дел Дата запроса Ластдатечанже 123 210390 Открыть 01.01.2020 432 832102 Открыть 01.06.2020 313 921235 Закрыто 03.03.2020 01.05.2020 610 182349 Закрыто 01.11.2018

Любая помощь будет замечательной и очень признательна, спасибо!

ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
0
229
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Итак, я нашел решение, хотя я уверен, что его можно упростить, чтобы получить тот же конечный результат. Ниже показано, что я придумал, выполнив 3 шага (вероятно, они отнимают много ресурсов и времени, особенно в таблице с более чем 60 тыс. записей):

  1. Извлечь записи с последней датой И статусом открытия независимо от того, дублируются они или нет (если учетная запись открыта с той же датой запроса, что и другая закрытая учетная запись, выбирается открытая).
  2. Извлечь записи, которые закрыты И не дублируются.
  3. Извлеките записи, которые имеют дубликаты со статусом «закрыто», И выберите те, у которых дата последнего изменения не является нулевой/пустой.

После этого объедините все вместе в целевую таблицу.

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

Это первая часть (только для добавления открытых счетов):

SELECT tblTest.[EMPID], tblTest.[AccountNumber], tblTest.[Status], tblTest.[RequestDate], tblTest.[RequestDate] AS RequestDate2    
INTO tblTestOutput    
FROM tblTest    
INNER JOIN (    
SELECT qryFullColumns.[EMPID],    
qryFullColumns.[AccountNumber],    
qryFullColumns.[Status],    
qryFullColumns.[RequestDate],    
qryFullColumns.[LastRequestDate]    
FROM (    
SELECT IIf(Isnull(tblTest.[EMPID]), 'ACT' & Right(tblTest.[AccountNumber],6), tblTest.[EMPID]) AS EMPID,    
tblTest.[AccountNumber],    
tblTest.[Status],    
IIf(Not tblTest.[RequestDate] Is Null, tblTest.[RequestDate],'12/31/2999') AS RequestDate,    
tblTest.[LastRequestDate]    
FROM tblTest)  AS qryFullColumns    
INNER JOIN (    
SELECT qryMaxReqDate.EMPID,    
Max(CDate(qryMaxReqDate.MaxReqDate)) AS MaxReqDate    
FROM (    
SELECT IIf(Isnull(tblTest.[EMPID]), 'ACT' & Right(tblTest.[AccountNumber],6), tblTest.[EMPID]) AS EMPID,    
IIf(Not [RequestDate] Is Null,[RequestDate],'12/31/2999') AS MaxReqDate    
FROM tblTest    
)  AS qryMaxReqDate    
GROUP BY EMPID)  AS qryMaxReqDate    
ON qryFullColumns.[EMPID] = qryMaxReqDate.[EMPID]    
WHERE CDate(qryFullColumns.[RequestDate]) = qryMaxReqDate.[MaxReqDate]    
)  AS qryMaxReqDate    
ON IIf(IsNull(tblTest.[EMPID]), 'ACT' & Right(tblTest.[AccountNumber],6), tblTest.[EMPID]) = qryMaxReqDate.[EMPID]    
WHERE qryFulLColumns.[Status] = 'Open' AND tblTest.[Status] <> 'Closed';

Для второго шага добавить только неповторяющиеся записи с закрытым статусом:

INSERT INTO tblTestOutput ([EMPID], [AccountNumber], [Status], [RequestDate], [RequestDate2])    
SELECT tblTest.[EMPID], tblTest.[AccountNumber], tblTest.[Status], tblTest.[RequestDate], tblTest.[RequestDate] as [RequestDate2]    
FROM tblTest    
INNER JOIN (    
SELECT qryMaxReqDate.EMPID, Max(CDate(qryMaxReqDate.MaxReqDate)) AS MaxReqDate    
FROM (    
SELECT IIf(Isnull(tblTest.[EMPID]), 'ACT' & Right(tblTest.[AccountNumber],6), tblTest.[EMPID]) As EMPID,    
IIf(Not [RequestDate] Is Null,[RequestDate],'12/31/2999') As MaxReqDate    
FROM tblTest    
) As qryMaxReqDate    
GROUP BY EMPID HAVING Count([EMPID]) =1    
) As qrySub ON IIf(IsNull(tblTest.[EMPID]), 'ACT' & Right(tblTest.[AccountNumber],6), tblTest.[EMPID]) = qrySub.[EMPID]    
WHERE tblTest.[Status] = 'Closed'

И последний шаг — добавить дубликаты аккаунтов, закрытых с датой последнего запроса:

SELECT tblTest.[EMPID], tblTest.[AccountNumber], tblTest.[Status], tblTest.[RequestDate], tblTest.[RequestDate] as [RequestDate2]    
FROM tblTest    
INNER JOIN (    
SELECT qryMaxReqDate.EMPID, Max(CDate(qryMaxReqDate.MaxReqDate)) AS MaxReqDate    
FROM (    
SELECT IIf(Isnull(tblTest.[EMPID]), 'ACT' & Right(tblTest.[AccountNumber],6), tblTest.[EMPID]) As EMPID,    
IIf(Not [RequestDate] Is Null,[RequestDate],'12/31/2999') As MaxReqDate    
FROM tblTest) As qryMaxReqDate    
GROUP BY EMPID HAVING Count([EMPID]) >1    
) As qrySub ON IIf(IsNull(tblTest.[EMPID]), 'ACT' & Right(tblTest.[AccountNumber],6), tblTest.[EMPID]) = qrySub.[EMPID]    
WHERE tblTest.[Status] = 'Closed' AND Not tblTest.[LastRequestDate] Is Null
Ответ принят как подходящий

UNION и NOT IN возвращают желаемый результат для заданных выборочных данных.

SELECT EmpID, ActNumber, Status, ReqDate, LastDateChange FROM tblTest 
    WHERE Status = "Open"
UNION SELECT EmpID, ActNumber, Status, ReqDate, LastDateChange FROM tblTest 
    WHERE NOT LastDateChange Is Null 
    AND EmpID NOT IN (SELECT EmpID FROM tblTest WHERE Status = "Open")
UNION SELECT EmpID, ActNumber, Status, ReqDate, LastDateChange FROM tblTest 
    WHERE LastDateChange Is Null 
    AND EmpID NOT IN (SELECT EmpID FROM tblTest WHERE Status = "Open") 
        AND EmpID NOT IN (SELECT EmpID FROM tblTest WHERE NOT LastDateChange Is Null 
        AND EmpID NOT IN (SELECT EmpID FROM tblTest WHERE Status = "Open"))
;

Обновлено - KNog Это пересмотренное выше предложение, и я получил правильный результат.

SELECT tblTest.EmpID, ActNumber, Status, ReqDate, LastDateChange 
FROM tblTest 
INNER JOIN (
     SELECT EmpID, Max(CDate(ReqDate)) As MaxDate From tblTest WHERE Not EmpID Is Null AND Status = 'Open' GROUP BY EmpID) As MaxOpnAct
ON tblTest.EmpID = MaxOpnAct.EmpID
WHERE Status = "Open"

UNION SELECT tblTest.EmpID, ActNumber, Status, ReqDate, LastDateChange 
FROM tblTest
INNER JOIN (
     SELECT EmpID, Max(CDate(ReqDate)) As MaxDate From tblTest WHERE Not EmpID Is Null AND Status = 'Closed' GROUP BY EmpID HAVING Count(EmpID)=1) As MaxClsNonDup
ON tblTest.EmpID = MaxClsNonDup.EmpID

UNION SELECT tblTest.EmpID, ActNumber, Status, ReqDate, LastDateChange 
FROM tblTest
INNER JOIN (
     SELECT EmpID, Max(CDate(ReqDate)) As MaxDate From tblTest WHERE Not EmpID Is Null AND Status = 'Closed' GROUP BY EmpID HAVING Count(EmpID)>1) As MaxClsDup
ON tblTest.EmpID = MaxClsDup.EmpID
WHERE NOT LastDateChange Is Null
     AND Status = 'Closed'
     AND CDate(ReqDate) = MaxDate

Спасибо, это сработало намного лучше. Однако он не выводил те, которые «Закрытые» не дублируются, и те, которые дублируются с «Закрытыми», которые затем отфильтровывались с использованием Not LastDateChange Is Null. Это, вероятно, моя вина за неясные требования. Тем не менее, это здорово, как начало для меня, спасибо! Я также переписал свои шаги ниже, которые намного более сжаты, и, конечно, я также могу включить UNION между тремя запросами.

KNog 18.12.2020 14:48

Когда я реализовал описанный выше метод в своем реальном проекте, я заметил, что в ответ получаю повторяющиеся записи, поскольку UNION объединяет все записи вместе, независимо от того, имеет ли уже предыдущий результат запроса тот же соответствующий EMPID или нет. Итак, что я сделал, чтобы обойти это, вместо использования UNION, я просто (в моем случае) создаю таблицу с первым набором запросов, а затем вставляю следующий во вновь созданную таблицу, опуская EMPID, где он соответствует в Таблица. Затем то же самое делается для последнего, что дает окончательный результат, который я ожидал.

KNog 18.12.2020 18:02

Другие вопросы по теме