Блог

Я использую MySQL 5.6. У меня есть таблица SQL со списком пользователей:

 id name
1  Alice
2  Bob
3  John
 

и таблица SQL со списком подарков для каждого пользователя (пронумерованы в порядке предпочтения):

 id gift       rank
1  balloon    2
1  shoes      1
1  seeds      3
1  video-game 1
2  computer   2
3  shoes      2
3  hat        1
 

И я хотел бы получить список предпочтительных подарков для каждого пользователя (самый высокий ранг — если два подарка имеют одинаковый ранг, выбирайте только один случайным образом) (бонус: если список можно рандомизировать, это было бы идеально!):

 id name  gift     rank
2  Bob   computer 2
1  Alice shoes    1
3  John  hat      1
 

Я пытался использовать предложение GROUP BY, но без какого-либо успеха.

Учет ранга как части ваших данных; Без использования оконных функций или сложных вложенных запросов

 SELECT u.id, u.name, g.gift
FROM users u
JOIN gifts g ON g.id = u.id
LEFT JOIN gifts g2 ON g2.id = g.id AND g2.rank > g.rank
WHERE g2.id IS NULL;

 

Добавлена ссылка http://sqlfiddle.com /#!9/62f59e/15/0

Вы можете использовать row_number , чтобы получить одну строку для каждого пользователя. (Mysql 8.0 )

 SELECT A.ID,NAME,GIFT,`RANK` FROM USERS A
LEFT JOIN (
    SELECT ID,GIFT,`RANK` FROM 
        (SELECT *,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY `RANK` ASC) AS RN  FROM X) X
    WHERE RN =1
) B 
ON A.ID= B.ID
 

Я не знаю DB, что вы используете. И я не эксперт в SQL (у меня может быть какая-то ошибка в next). Но я думаю, что это не сложно.

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

Сначала все, что мне нужно, это самый высокий ранг. Итак, я должен получить это.

 SELECT  MAX(RANK)
FROM    GIFT
GROUP BY ID
 

И тогда я думаю, что мне нужно получать подарки от этого ранга.

 SELECT GIFT.*
FROM   GIFT
INNER JOIN(
  SELECT ID, MAX(RANK)
  FROM GIFT
  GROUP BY ID
) filter ON GIFT.ID = filter.ID AND GIFT.RANK = filter.RANK
 

Я думаю, что это таблица, которую вы хотите!

Итак, если приведенный ниже код работает, это то, что вы действительно хотите.

 SELECT *
FROM USER
LEFT OUTER JOIN(
  above table
) GIFT ON USER.ID = GIFT.ID
 

Но помните об этом, я сказал, что я не эксперт в SQL. Может быть лучший способ.

Проверка запроса

 SELECT tbluser.id,name,gift,rank into tblrslt
FROM tbluser 
LEFT JOIN tblgifts
ON tbluser.id = tblgifts.id  order by id,rank;

SELECT tt.*
FROM tblrslt tt
INNER JOIN
    (SELECT id, min(rank) AS rank
    FROM tblrslt
    GROUP BY id) groupedtt 
ON tt.id = groupedtt.id 
AND tt.rank = groupedtt.rank order by id

 

В версиях MySQL старше 8 у вас нет доступных функций ранжирования. Вместо этого вы выберете минимальный ранг для каждого пользователя и будете использовать эти ранги для выбора строк подарков. Это означает, что вы дважды обращаетесь к таблице подарков.

Я предлагаю это:

 select *
fron users u
join gifts g 
  on g.id = u.id 
  and (g.id, g.rank) in (select id, min(rank) from gifts group by id)
order by u.id;
 

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