Блог

Следующий запрос возвращает две строки. PIDM (число) и два разных значения sortest_test_date.

 319824|21-MAR-18
319824|18-APR-18
 

Я хотел бы, чтобы запрос возвращал одну строку, содержащую PIDM и максимальное значение sortest_test_date. Как мне этого добиться?

 select distinct a.sortest_pidm pidm, max(a.sortest_test_date) max_test_date
from sortest a
where (a.sortest_tesc_code = 'ACC1' or a.sortest_tesc_code = 'ACC2')
and a.sortest_pidm = (select distinct sortest_pidm from sortest b where a.sortest_pidm = b.sortest_pidm and b.sortest_tesc_code = 'ACC1' and b.sortest_test_score >= 81)
and a.sortest_pidm = (select distinct sortest_pidm from sortest b where a.sortest_pidm = b.sortest_pidm and b.sortest_tesc_code = 'ACC2' and b.sortest_test_score >= 95)
and a.sortest_pidm = 319824
group by a.sortest_pidm, a.sortest_test_date;
 

В принципе, вам нужно удалить дату из group by . Вы также можете внести другие улучшения в запрос.

 select a.sortest_pidm pidm, max(a.sortest_test_date) max_test_date
from sortest a
where a.sortest_tesc_code in ('ACC1', 'ACC2') and
      a.sortest_pidm = (select sortest_pidm from sortest b where a.sortest_pidm = b.sortest_pidm and b.sortest_tesc_code = 'ACC1' and b.sortest_test_score >= 81
                       ) and
      a.sortest_pidm = (select sortest_pidm from sortest b where a.sortest_pidm = b.sortest_pidm and b.sortest_tesc_code = 'ACC2' and b.sortest_test_score >= 95) and
      a.sortest_pidm = 319824
group by a.sortest_pidm;
 

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

Просто Сделай Это.

 Select a.sortest_pidm pidm, a.sortest_test_date max_test_date
from sortest a join (
Select sortest_pidm pidm, max(a.sortest_test_date) max_test_date
from sortest 
group by sortest_pidm
) b on (a.pidm = b.pidm and a.sortest_test_date = b.max_test_date);
 

GROUP BY x, y означает «Я хочу одну строку результата на x и y». В вашем запросе говорится: «дайте мне одну строку результата на pidm и дату», и вы получите именно это. Если вместо этого вы хотите получить один результат ro на pidm, затем сгруппируйте по pidm.

Кроме того, вам нужны только строки, в которых существует значение ACC1 для pidm со счетом не менее 81. Для этого вы выполняете поиск по всем таким строкам только для того, чтобы DISTINCT затем свести их к pidm или null. Мой общий совет: Используйте EXISTS или IN когда вы просто хотите убедиться, что существует хотя бы одна соответствующая запись. Это дает СУБД намного меньше работы, потому что она может остановиться при первом найденном совпадении. В приведенном ниже запросе я показываю оба подхода: коррелированное EXISTS предложение для поиска AAC2 и некоррелированное IN предложение для поиска ACC1.

 select sortest_pidm pidm, max(sortest_test_date) as max_test_date
from sortest a
where sortest_tesc_code in ('ACC1', 'ACC2')
and sortest_pidm in
(
  select sortest_pidm
  from sortest
  where sortest_tesc_code = 'ACC1' and sortest_test_score >= 81
)
and exists
(
  select null
  from sortest b
  where b.sortest_pidm = a.sortest_pidm
  and b.sortest_tesc_code = 'ACC2' and b.sortest_test_score >= 95
)
--and sortest_pidm = 319824
group by sortest_pidm
order by sortest_pidm;
 

Но в своем запросе вы все равно просматриваете все строки ACC1 и ACC2, чтобы получить максимальную дату на pidm. Это означает, что при агрегировании мы можем сказать: пожалуйста, посчитайте совпадения и покажите мне только те пиды, для которых количество совпадений больше нуля. Это приводит к следующему запросу:

 select sortest_pidm pidm, max(sortest_test_date) as max_test_date
from sortest a
where sortest_tesc_code in ('ACC1', 'ACC2')
--and sortest_pidm = 319824
group by sortest_pidm
having count(case when sortest_tesc_code = 'ACC1' and sortest_test_score >= 81 then 1 end) > 1
   and count(case when sortest_tesc_code = 'ACC2' and sortest_test_score >= 95 then 1 end) > 1
order by sortest_pidm;
 

Этот метод называется условной агрегацией (потому что мы считаем на основе условий).

Используйте TOP 1 (SQL Server) в инструкции select, чтобы получить верхнюю строку. Используется order by для упорядочения результата, установленного по дате в desc порядке.