SQL: one to many, select just one

Обновить

November 2018

Просмотры

1.4k раз

2

Я пишу небольшой инструмент для управления людьми, зарегистрированных в ассоциацию.

У меня есть таблица USERSи таблица CARDS. Каждый пользователь может иметь более одной карты (потому что карты истекает).

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

Вот очень простой пример:

таблица USERS

IDUSER  NAME    SURNAME
------------------------
1       Robert  Hill

КАРТОЧКИ стол

IDCARD  IDOWNER  ISSUEDATE   DESC 
----------------------------------------
1       1        2010-01-01  'CARD ONE'
2       1        2010-08-18  'CARD TWO'

В основном я хотел бы получить назад от моего запроса этих полей:

IDUSER  NAME     SURNAME  IDCARD  IDOWNER  ISSUEDATE   DESC
---------------------------------------------------------------
1       Robert   Hill     2       1        2010-08-18  'CARD TWO'

Я пытался, но я не могу управлять :(

EDIT: Что произойдет, если карта памяти не связан с пользователем? (Один пользователь зарегистрирован, но ассоциация не дать ему / ей любую карту еще). Есть ли способ, чтобы адаптировать этот запрос, чтобы получить обратно информацию пользователя в любом случае? Идея заключается в том, чтобы получить всю возможную информацию для каждого пользователя. Эта информация в дальнейшем используется с помощью JSON на рекламном ExtJS приложения.

Спасибо за ваши ответы.

3 ответы

1

Вариант 1 с NOT EXISTS запрос:

select u.IDUSER, u.NAME, u.SURNAME,
       c.IDCARD, c.IDOWNER, c.ISSUEDATE, c.DESC
from USERS u
join CARDS c on u.IDUSER = c.IDOWNER
where not exists (select 1 from CARDS where IDOWDER = u.IDUSER and ISSUEDATE > c.ISSUEDATE)

Вариант 2 с LIMIT

select u.IDUSER, u.NAME, u.SURNAME,
       c.IDCARD, c.IDOWNER, c.ISSUEDATE, c.DESC
from USERS u
join CARDS c on u.IDUSER = c.IDOWNER
order by c.ISSUEDATE desc
LIMIT 1
1
select u.IDUSER, u.NAME, u.SURNAME,
       c.IDCARD, c.IDOWNER, c.ISSUEDATE, c.DESC
from USERS u
join CARDS c on u.IDUSER = c.IDOWNER and c.IDCARD in (
     /* select biggest IDCARD numbers that has same IDOWNER thus eliminating multiple rows and remaining only 1 for each IDOWNER */
     select MAX(IDCARD) as IDCARD
     from USERS join CARDS on USERS.IDUSER = CARDS.IDOWNER
     group by CARDS.IDOWNER

)
1

Altho как LIMITправило , не допускаемые в MySQL подзапросы, LIMIT 1является исключением, так что «Выберите только один» является идеальным для этого случая.

LEFT JOINгарантирует , что вы также будете иметь пользователь без данных карты (данные карт будет NULL, вы можете использовать , ifnull(desc, 'No Card Issued')чтобы показать , что по вашему желанию)

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

SELECT * 
  FROM users u 
       LEFT JOIN cards c 
         ON c.idcard = (SELECT idcard 
                          FROM cards c1 
                         WHERE c1.idowner = u.iduser 
                         ORDER BY issuedate DESC 
                         LIMIT 1)