MySQL JOIN с LIMIT 1 на объединенной таблице

Я хочу присоединиться к двум таблицам, но получаю только 1 запись таблицы2 за запись в таблице1

Например:

SELECT c.id, c.title, p.id AS product_id, p.title
FROM categories AS c
JOIN products AS p ON c.id = p.category_id

Это даст мне все записи в products, чего я не хочу. Я хочу 1 [первый] продукт для каждой категории (у меня есть столбец sort в поле продуктов).

Как мне это сделать?

Ответ 1

Я бы попробовал что-то вроде этого:

SELECT C.*,
      (SELECT P.id, P.title 
       FROM products as P
       WHERE P.category_id = C.id
       LIMIT 1)
FROM categories C

Ответ 2

Мне больше нравится другой подход, описанный в похожем вопросе: fooobar.com/questions/174079/...

Этот подход лучше, особенно если вам нужно показать более одного поля в SELECT. Чтобы избежать Error Code: 1241. Operand should contain 1 column(s) или двойной суб-выбор для каждого столбца.

В вашей ситуации запрос должен выглядеть следующим образом:

SELECT
 c.id,
 c.title,
 p.id AS product_id,
 p.title AS product_title
FROM categories AS c
JOIN products AS p ON
 p.id = (                                 --- the PRIMARY KEY
  SELECT p1.id FROM products AS p1
  WHERE c.id=p1.category_id
  ORDER BY p1.id LIMIT 1
 )

Ответ 3

Принятый ответ @goggin13 выглядит неправильно. Другие решения, предоставленные на сегодняшний день, будут работать, но страдают от проблемы n + 1 и, как таковые, пострадают от производительности.

n + 1 проблема: если существует 100 категорий, нам нужно будет сделать 1 выбор, чтобы получить категории, то для каждой из 100 возвращаемых категорий нам нужно сделать выбор для получения продуктов в этой категории, Таким образом, будет выполнено 101 запрос SELECT.

Мое альтернативное решение решает проблему n + 1 и, следовательно, должно быть значительно более эффективным, поскольку выполняется только 2 выбора.

SELECT
  *
FROM
    (SELECT c.id, c.title, p.id AS product_id, p.title
    FROM categories AS c
    JOIN products AS p ON c.id = p.category_id
    ORDER BY c.id ASC) AS a 
GROUP BY id;

Ответ 4

SELECT c.id, c.title, p.id AS product_id, p.title
FROM categories AS c
JOIN products AS p ON c.id = p.category_id
GROUP BY c.id

Это вернет первые данные в продуктах (равное пределу 1)

Ответ 5

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

SELECT c.id, c.title, (SELECT id from products AS p 
                            WHERE c.id = p.category_id 
                            ORDER BY ... 
                            LIMIT 1)
   FROM categories AS c;

Ответ 6

Предложение With сделало бы трюк. Что-то вроде этого:

WITH SELECTION AS (SELECT id FROM products LIMIT 1)
SELECT a.id, c.id, c.title FROM selection a JOIN categories c ON (c.id = a.id);

Ответ 7

Предполагая, что вам нужен продукт с MIN() imial значением в столбце sort, он будет выглядеть примерно так.

SELECT 
  c.id, c.title, p.id AS product_id, p.title
FROM 
  categories AS c
INNER JOIN (
  SELECT
    p.id, p.category_id, p.title
  FROM
    products AS p
  CROSS JOIN (
    SELECT p.category_id, MIN(sort) AS sort
    FROM products
    GROUP BY category_id
  ) AS sq USING (category_id)
) AS p ON c.id = p.category_id

Ответ 8

При использовании postgres вы можете использовать синтаксис DISTINCT ON чтобы ограничить число столбцов, возвращаемых из любой таблицы.

Вот пример кода:

SELECT c.id, c.title, p.id AS product_id, p.title FROM categories AS c JOIN ( SELECT DISTINCT ON(p1.id) id, p1.title, p1.category_id FROM products p1 ) p ON (c.id = p.category_id)
Хитрость заключается не в том, чтобы объединить непосредственно таблицу с несколькими вхождениями идентификатора, а в первую очередь создать таблицу с единственным вхождением для каждого идентификатора.

Ответ 9

Замените таблицы своими:

SELECT * FROM works w 
LEFT JOIN 
(SELECT photoPath, photoUrl, videoUrl FROM workmedias LIMIT 1) AS wm ON wm.idWork = w.idWork