☰

2-переменный SQL-запрос для данных за предыдущий год

tag-icon • tag-icon microsoft-access
2-переменный SQL-запрос для данных за предыдущий год

Создайте таблицу с идентификаторами, назначаемыми записям по мере их создания.

Имеются записи за несколько лет (иногда годы пропускаются, например: 2019, 2017, 2016 и т. д.).

Записи включают поле «Год» в виде текста и поле «Имя» в виде текста (это поле «Имя» одинаково для всех лет), а также поле «Дата» в виде даты.

С помощью запроса Access SQL хотелось бы показать:

Имя, текущий год, AptDate текущего года, AptDate «предыдущего» года (при условии, что Имя идентично, а Год — следующий по времени год)

Достигнуто следующее с помощью SQL (показано AptDate «предыдущего» года для EnteredSampleName):

SELECT Table.AptDate
FROM Table
WHERE (((Table.Name)=[EnteredSampleName]) AND ((Year=(SELECT Max(Table.Year) AS LastYear
FROM Table
WHERE (((Table.Year)<(SELECT MAX(Table.Year) FROM Table WHERE ((Table.Name)=[EnteredSampleName]))))))));

Хотелось бы удалить EnteredSampleName и показать эту «предыдущую» AptDate для всех записей (некоторые «предыдущие» годы могут быть нулевыми, поскольку для этого имени может не быть более ранней записи).

Похоже, мне не удаётся разгадать эту загадку. Любая помощь будет оценена по достоинству.

Спасибо за ваши полезные ответы — я скоро их протестирую.

Тем временем, отвечая на просьбу Ли:

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

ID  Name    Year    AptDate
1   A   2015    2015-02-02
2   B   2015    2015-03-05
3   C   2017    2017-04-04
4   B   2017    2017-06-09
5   C   2018    2018-07-03
6   A   2018    2018-09-10
7   A   2019    2019-01-08
8   D   2019    2019-01-09

Цель результата запроса:

Name    Year    AptDate AptDatePrevious
A   2019    2019-01-08  2018-09-10
B   2017    2017-06-09  2015-03-05
C   2018    2018-07-03  2017-04-04
D   2019    2019-01-09

Как и ожидалось, AptDatePrevious для D отсутствует. Надеюсь, это прояснит ситуацию. Спасибо.

решение1

Пронумерованные примеры с этого сайта MS могут указать вам правильное направление:
https://support.office.com/en-us/article/nest-a-query-inside-another-query-or-in-an-expression-by-using-a-subquery-a8532322-e42f-4f94-bc38-ace7c182916a

Вам нужно будет сделать что-то вроде этого:

Выберите Таблица.Имя, Таблица.Год, Таблица.Поддат., expr(вычислить предыдущую дату здесь*)
из таблицы
Упорядочить по {как хотите}

*Выражение будет выглядеть примерно так:

Выбрать max(PrevAptDate.AptDate
из таблицы как PrevAptDate,
где (PrevAptDate.AptDate < Table.AptDate)
и
(PrevAptDate.Name = Table.Name)

Это все экспромтом, но это должно быть близко и указать вам правильное направление. Надеюсь, это поможет.

решение2

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

select t0.name, t0.year, t0.aptdate, q2.aptdate
from YourTable t0 left join
(
    select t1.name, t1.aptdate
    from YourTable t1 inner join
    (
        select t2.name, max(t2.year) as lastyear
        from YourTable t2
        where t2.year < year(date())
        group by t2.name
    ) q1 on
    t1.name = q1.name and 
    t1.year = q1.lastyear
) q2 on 
t0.name = q2.name
where t0.year = year(date())

В приведенном выше запросе замените каждое вхождение на YourTableимя вашей таблицы.

Здесь самый внутренний запрос q2получает наибольшее значение yearдля каждого name, где yearменьше текущего года year(date()).

Результат этого запроса затем внутренне соединяется с исходной таблицей, чтобы выбрать соответствующее aptdateдля каждого name& maximum year. Это формирует подзапрос q2.

Наконец, исходная таблица выглядит так:левыйприсоединяется к подзапросу q2(в случае отсутствия записи за предыдущий год для заданного имени), а результаты выводятся родительским запросом.

Связанный контент