Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Составление SQL-запросов > Сложный запрос по двум таблицам с MAX значением

Автор: KateL 16.9.2020, 14:06

MS Access, C++ Builder

Есть две таблицы, каждой строке из Table_A может соответствовать несколько строк из Table_B по Table_A.ID = Table_B.A_ID

Table_A с полями: ID, Year, ...
Table_B с полями: ID, A_ID, N, Name, X, Y, ...

Надо составить запрос, в котором из всех строк Table_B (для одного A_ID) выбирается одна строка с максимальным значением N (даже, если таких максимальных значений несколько) + условие по полям N, Yr, X и Y

В результате должна получиться таблица: Yr, N_max, Name с сортировкой по N_max

У меня получился такой запрос. Возможно, я там наворотила лишнего, т.к. плохо разбираюсь в сложных запросах.

Код


SELECT E.N, A.Yr, E.Name FROM Table_A as A
INNER JOIN ( SELECT C.A_ID, C.Name, C.N from Table_B as C 

INNER JOIN ( SELECT B.A_ID, MAX(B.N) as N_max FROM Table_A as A, Table_B as B WHERE (тут условия на B.N, B.X, B.Y, A.Yr)
             GROUP BY B.A_ID ) as D ON D.A_ID = C.A_ID and D.N_max = C.H

           ) as E ON E.A_ID = A.ID
ORDER BY E.N DESC

Но! В случае, если максимальных значений N для одного A_ID несколько, все строки с ними выводятся в результате.
А надо, чтобы для каждой строки Table_A в результате была только одна строка с максимальным значением N.

Пример:

Код


Table_A  Table_B

ID Yr    ID A_ID N  Name 

1  2000  1  1    50 aaa1   
         2  1    41 aaa2   
         3  1    35 aaa3   
         4  1    50 aaa4   
2  2010  5  2    15 bbb1   
         6  2    10 bbb2   
         7  2    15 bbb3
3  2015   

Хочется получить:
2000 50 aaa1
2010 15 bbb1

А получаю:
2000 50 aaa1   
2000 50 aaa4   
2010 15 bbb1   
2010 15 bbb3

Помогите, пожалуйста

Автор: Агрох 16.9.2020, 15:20

TOP 1

Код


SELECT E.N, A.Yr, E.Name FROM Table_A as A
INNER JOIN ( SELECT TOP 1 C.A_ID, C.Name, C.N from Table_B as C 
INNER JOIN ( SELECT B.A_ID, MAX(B.N) as N_max FROM Table_A as A, Table_B as B WHERE (тут условия на B.N, B.X, B.Y, A.Yr)
             GROUP BY B.A_ID ) as D ON D.A_ID = C.A_ID and D.N_max = C.H
           ) as E ON E.A_ID = A.ID
ORDER BY E.N DESC

Но в таком виде возьмёт условно случайную строку. В разное время или на разных серверах один и тот же запрос над одними и теми же данными вернёт разные значения, что не есть хорошо. Лучше добавить ещё какую то сортировку, чтобы добиться однозначности.

Автор: KateL 16.9.2020, 16:20

Цитата(Агрох @ 16.9.2020, 15:20

)

TOP 1

Увы, так я пробовала, но получается ВСЕГО одна строка

Автор: Akina 16.9.2020, 19:57

Цитата(KateL @ 16.9.2020, 15:06

)

надо, чтобы для каждой строки Table_A в результате была только одна строка с максимальным значением N.

А если в Table_B имеется две строки с одинаковым максимальным N - что выводить?

А вообще

Код


SELECT *
FROM table_a t1
JOIN table_b t2 ON t1.ID = t2.A_ID
JOIN ( SELECT t3.A_ID, MAX(t3.N) AS N
       FROM table_b t3
       GROUP BY t3.A_ID ) AS t4 ON t2.A_ID = t4.A_ID AND t2.N = t4.N

Автор: KateL 16.9.2020, 21:45

Цитата(Akina @ 16.9.2020, 19:57

)

А если в Table_B имеется две строки с одинаковым максимальным N - что выводить?

это не важно, главное значение N

Цитата(Akina @ 16.9.2020, 19:57

)

А вообще ...

1) JOIN заменила на INNER JOIN (а то ругался)

2) дальше тоже не проходит. Выдает ошибку:

Ошибка синтаксиса (пропущен оператор) в выражении запроса
't1.ID = t2.A_ID
INNER JOIN (SELECT t3.A_ID, MAX(t3.N) AS N
FROM table_b t3
GROUP BY t3.A_ID) AS t4 ON t2.A_ID = t4.A_ID'

3) Вот так работает, но снова с дублями

Код


SELECT * 
FROM table_a t1 
INNER JOIN ( table_b t2  
INNER JOIN ( SELECT t3.A_ID, MAX(t3.N) AS N
                    FROM table_b t3 
                    GROUP BY t3.A_ID) AS t4 ON t2.A_ID = t4.A_ID AND t2.N = t4.N ) ON t1.ID = t2.A_ID

Автор: ksnk 16.9.2020, 22:29

У меня нет возможности проверить на msaccess :( так что я на MySql

Код


select a.Yr,b.N,x.name from (
SELECT A_ID, min(Name) as Name from Table_B 
group by A_ID order by N desc
  ) as x 
left join  Table_B as b on b.A_ID=x.A_ID and b.Name=x.Name
left join  Table_A as a on a.ID=x.A_ID

http://sqlfiddle.com/#!9/131805/13
вот тут можно посмотреть. Вроде, то что надо.
Но это только для случая, когда комбинация N+Name однозначно определяет строку таблицы Table_В. Если данные такие и есть, то вполне работоспособно, с точностью до версии sql для Access

Автор: Akina 17.9.2020, 07:14

KateL, смотрим http://forum.vingrad.ru/index.php?showtopic=260944&view=findpost&p=1879602 и делаем... а то пальцами в воздухе можно вертеть до бесконечности.

Сделал мин. модель. Вот как выглядит запрос по мнению MS Access 2010:

Код


SELECT *
FROM table_a INNER JOIN 
    ( table_b INNER JOIN 
         ( SELECT t3.A_ID, Max(t3.N) AS N
           FROM table_b AS t3 
           GROUP BY t3.A_ID)  AS t4 
         ON (table_b.N = t4.N) AND (table_b.A_ID = t4.A_ID)) 
    ON table_a.ID = table_b.A_ID;

Автор: Агрох 17.9.2020, 10:33

Писал на FB. В Access аналогом FIRST_VALUE, по моему, является просто FIRST.

Код


SELECT DISTINCT
  A.ID,
  A.Year,
  FIRST_VALUE(B.ID) OVER(PARTITION BY A.ID ORDER BY B.N DESC) AS B_ID,
  FIRST_VALUE(B.NAME) OVER(PARTITION BY A.ID ORDER BY B.N DESC) AS B_NAME,
  FIRST_VALUE(B.X) OVER(PARTITION BY A.ID ORDER BY B.N DESC) AS B_X,
  FIRST_VALUE(B.Y) OVER(PARTITION BY A.ID ORDER BY B.N DESC) AS B_Y
FROM T_A AS A
  left JOIN T_B AS B
    ON B.A_ID = A.ID

Вариант без оконных функций:

Код


SELECT
  *
FROM T_A AS A
  LEFT JOIN (
    SELECT
      B.*
    FROM T_B AS B
      INNER join (
        SELECT
          B.A_ID,
          MAX(B.ID) AS ID
        FROM T_B AS B
          INNER JOIN (
            SELECT
              A_ID,
              MAX(N) AS N_MAX
            FROM T_B
            GROUP BY A_ID) AS B1
            ON B1.A_ID = B.A_ID
              AND B1.N_MAX = B.N
        GROUP BY B.A_ID
      ) AS B2
      ON B2.ID = B.ID
    ) AS B3
  ON B3.A_ID = A.ID

Автор: KateL 17.9.2020, 11:32

Цитата(ksnk @ 16.9.2020, 22:29

)

Вроде, то что надо

Спасибо за уделенное моему вопросу время. Но это совсем не то.
В примере я написала Name как aaa1 aaa2 просто, чтобы как-то обозначить. Они вообще могут быть разные. Это как дополнительный комментарий к полю N

Мне же надо в таблице Table_B для каждой группы по A_ID найти одно максимальное значение N и добавить к нему соответствующий Name (любой, если максимальных значений несколько) и Yr из Table_A

Автор: KateL 17.9.2020, 12:51

Цитата(Агрох @ 17.9.2020, 10:33

)

Вариант без оконных функций

УРРРААА!!! ЗЗАРРРАБОТАЛО! smile

Только LEFT JOIN заменила на INNER JOIN, т.к. не для всех строк Table_A есть соответствия в Table_B
Ну и нужные мне сравнения вставила в select с MAX()

Агрох, БОЛЬШОЕ вам спасибо smile