справка по sql-запросу (количество)

У меня 3 стола

CUSTOMERS (CUSTOMER_ID, LASTNAME, FIRSTNAME, ... other fields)
LICENSES(LICENSE_ID, CREATED_AT, RELEASE_ID, CUSTOMER_ID, ... other fields)
RELEASES(RELEASE_ID, RELEASE_NAME, RELEASE_NUMBER, ... other fields)

CREATED_AT в ЛИЦЕНЗИИ - это поле DATE (NOT NULL).

таблицы связаны по первичному ключу / внешнему ключу с тем же именем; у клиента может быть 0 или более лицензий, и у каждой лицензии есть выпуск.

Я бы хотел получить из этих таблиц:

имя, фамилия и идентификатор выпуска последней созданной лицензии клиента (согласно полю CREATED_AT в ЛИЦЕНЗИИ, чтобы найти последнюю), если таковая имеется.

для этого я использовал этот запрос:

SELECT CUSTOMERS.FIRSTNAME,
       CUSTOMERS.LASTNAME,

  (SELECT RELEASES.RELEASE_ID
   FROM RELEASES
   INNER JOIN LICENSES ON LICENSES.RELEASE_ID = RELEASES.RELEASE_ID
   INNER JOIN CUSTOMERS AS t ON t.CUSTOMER_ID = LICENSES.CUSTOMER_ID
   WHERE t.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID
   ORDER BY LICENSES.CREATED_AT DESC LIMIT 1) AS REL_ID
FROM CUSTOMERS
WHERE EXISTS
    (SELECT 1
     FROM RELEASES
     INNER JOIN LICENSES ON LICENSES.RELEASE_ID = RELEASES.RELEASE_ID
     INNER JOIN CUSTOMERS AS t ON LICENSES.CUSTOMER_ID = t.CUSTOMER_ID
     AND t.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID)

Кажется, это работает, но я спрашиваю, может ли кто-нибудь подтвердить мне это или можно сделать это проще.

Другие данные, которые я хочу получить из этих таблиц, - это RELEASES.RELEASE_ID, RELEASES.RELEASE_NAME, RELEASES.RELEASE_NUMBER и количество клиентов, у которых последняя лицензия (согласно CREATED_AT в LICENSES) имеет этот выпуск.

Мне не удалось создать этот запрос. Я использую базу данных h2.

Спасибо за помощь


sql jooq h2
person res1    schedule 08.09.2014    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Вероятно, это должно быть эквивалентно

SELECT CUSTOMERS.FIRSTNAME,
       CUSTOMERS.LASTNAME,
      (SELECT LICENSES.RELEASE_ID
       FROM LICENSES
       WHERE LICENSES.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID
       ORDER BY LICENSES.CREATED_AT DESC
       LIMIT 1) AS REL_ID
FROM CUSTOMERS
WHERE EXISTS (
    SELECT 1 
    FROM LICENSES
    WHERE LICENSES.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
)

Вам не нужно объединять все эти таблицы, если у вас есть ограничения внешнего ключа для LICENSES.RELEASE_ID и LICENSES.CUSTOMER_ID. В частности, нет смысла присоединяться к RELEASES таблице, потому что столбец LICENSES.RELEASE_ID уже содержит нужную информацию.

LATERAL / CROSS APPLY присоединиться к другим базам данных

Для полноты картины, если бы это были PostgreSQL, Oracle, SQL Server и т. Д., Вы могли бы выполнить боковое соединение, которое также известно как _ 6_.

SELECT CUSTOMERS.FIRSTNAME,
       CUSTOMERS.LASTNAME,
       l.RELEASE_ID
FROM CUSTOMERS 
CROSS JOIN LATERAL (
    SELECT *
    FROM LICENSES
    WHERE LICENSES.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID
    ORDER BY LICENSES.CREATED_AT DESC
    LIMIT 1
) l
person Lukas Eder    schedule 09.09.2014
comment
Привет, спасибо за ответ и советы. Мой вопрос состоит из двух запросов, второй находится в последней части вопроса, и мне не удалось его создать. Есть ли у вас какие-нибудь предложения по этому поводу? - person res1; 09.09.2014
comment
@ res1: задайте новый вопрос здесь, в Stack Overflow. Будущим посетителям будет намного легче понять, что происходит ... - person Lukas Eder; 09.09.2014

arrow_upward
1
arrow_downward

Как насчет этого:

SELECT c.FIRSTNAME, c.LASTNAME, r.RELEASE_ID
FROM CUSTOMERS c
INNER JOIN LICENSES l ON (c.CUSTOMER_ID = l.CUSTOMER_ID)
INNER JOIN RELEASES r ON (r.RELEASE_ID = l.RELEASE_ID)

WHERE r.CREATED_AT = ( SELECT MAX(t.CREATED_AT) FROM RELEASES t
                       WHERE t.RELEASE_ID = r.RELEASE_ID )

из-за внутреннего соединения будут перечислены только клиенты с выпусками.

person Theo Müller    schedule 08.09.2014
comment
Таблица RELEASES здесь не нужна. Кроме того, RELEASE_ID (с самой последней LICENSES.CREATED_AT датой) отсутствует в наборе результатов. - person Lukas Eder; 09.09.2014
comment
Вы правы, я не заметил эту колонку. Я обновил запрос выше - person Theo Müller; 09.09.2014
comment
спасибо за ответ, как я уже сказал в комментариях к ответу Лукаса, мой вопрос состоит из 2 запросов, мне не удалось создать последний - person res1; 09.09.2014