조인된 테이블에 LIMIT 1이 있는 MySQL JOIN

조인된 테이블에 LIMIT 1이 있는 MySQL JOIN

2개의 테이블을 결합하고 싶은데 table1의 레코드당 table2의 레코드는 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개의 [첫 번째] 제품을 원합니다(제1의 제품이 있습니다).sort를 누릅니다).

어떻게 하면 좋을까요?

비슷한 질문에서 설명한 또 다른 접근법이 더 마음에 듭니다.https://stackoverflow.com/a/11885521/2215679

이 방법은 특히 SELECT에 여러 필드를 표시할 필요가 있는 경우에 적합합니다.피하기 위해Error Code: 1241. Operand should contain 1 column(s)또는 각 열에 대해 두 번 선택합니다.

고객님의 경우 쿼리는 다음과 같이 표시됩니다(이 쿼리는 PostgresQL에서도 동작하며 매우 빠릅니다.다음 업데이트를 참조하십시오).

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
 )

추신. 여기서 제안한 쿼리와 다른 쿼리의 퍼포먼스 테스트를 실시했는데, 이 쿼리는 최고의 옵션입니다!

업데이트(2022-07-20, 포스트그레스)SQL)

한동안 mySQL을 사용하지 않았기 때문에 솔루션 성능을 테스트하기로 했습니다(MySQL과 Postgres 모두에서 완벽하게 작동합니다).QL)를 사용하여 PostgresQL v.12.9에서 @Gravy에서 제공하는 솔루션을 제공합니다.

이를 위해 100개의 카테고리와 10만개의 제품으로 더미 테이블과 데이터를 작성하기로 했습니다.이 GIST에서 코드를 확인할 수 있습니다.

위에서 쿼리를 실행했는데 실행하는데 13ms밖에 걸리지 않았습니다.

(postgres의 경우) @Gravy로부터의 쿼리를 약간 수정한 후:

SELECT
  id,
  category_title,
  (array_agg(product_title))[1]  
FROM
    (SELECT c.id, c.title AS category_title, p.id AS product_id, p.title AS product_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, category_title;

실행해보세요.내 기계는 150ms 이상 걸렸다.10배 이상 느립니다.

@gravy의 해법에 대해서는 n+1 문제에 동의합니다.그러나, 이 특별한 경우, 보통 제품의 수는 카테고리보다 훨씬 더 많습니다.따라서 @Gravy의 질문처럼 각 카테고리를 실행하는 것이 각 제품을 실행하는 것보다 훨씬 비용이 적게 듭니다.

참고로 테이블이 100개 카테고리의 1mln 제품이 있는 경우, 쿼리 속도는 그대로(9~17ms)입니다만, [@Gravy]로부터의 쿼리는 2초 이상 걸립니다.

다시 말씀드리면, 현시점에서는, 현재의 태스크에 최적인 퍼포먼스·솔루션입니다.

편하게 말씀해주세요.

@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;

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)

이건 어때?

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;

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);

MIN() in im im im 。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

테이블이 3개인 다른 예: 1/사용자 2/사용자 역할 회사 3/컴패니

• 1명의 사용자가 다수의 UserRoleCompany를 가지고 있습니다.
• 1 UserRoleCompany는 사용자 1명과 회사 1명이 있습니다.
• 1 Company에는 다수의 User Role Company가 있다

SELECT 
u.id as userId, 
u.firstName,
u.lastName,
u.email,
urc.id ,
urc.companieRole,
c.id as companieId,
c.name as companieName
FROM User as u 
JOIN UserRoleCompanie as urc ON u.id = urc.userId
    AND urc.id = (
        SELECT urc2.id
        FROM UserRoleCompanie urc2 
        JOIN Companie ON urc2.companieId = Companie.id
        AND urc2.userId = u.id 
        AND Companie.isPersonal = false
        order by Companie.createdAt DESC
        
        limit 1
    )
    
LEFT JOIN Companie as c ON urc.companieId = c.id

+---------------------------+-----------+--------------------+---------------------------+---------------------------+--------------+---------------------------+-------------------+
| userId                    | firstName | lastName           | email                     | id                        | companieRole | companieId                | companieName      |
+---------------------------+-----------+--------------------+---------------------------+---------------------------+--------------+---------------------------+-------------------+
| cjjt9s9iw037f0748raxmnnde | henry     | pierrot            | henry@gmail.com           | cjtuflye81dwt0748e4hnkiv0 | OWNER        | cjtuflye71dws0748r7vtuqmg | leclerc           |

제 의견으로는 이것이 최선의 답변입니다(일반적으로 설명).

SELECT  
TB1.Id  
FROM Table1 AS TB1  
INNER JOIN Table2 AS TB2 ON (TB1.Id = TB2.Id_TB1)  
    AND TB2.Id = (  
        SELECT Id  
        FROM Table2  
        WHERE TB1.Id = Id_TB1  
        ORDER BY Table2.Id DESC  
        LIMIT 1  
    )  

postgres를 할 수 .DISTINCT ON각 테이블에서 반환되는 열 수를 제한하려면 syntex를 선택합니다.

다음은 코드의 예입니다.

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)
개 있는 ID마다입니다.

테이블을 사용자 테이블로 교체합니다.

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

저는 이런 걸 해보고 싶어요

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

언급URL : https://stackoverflow.com/questions/6879391/mysql-join-with-limit-1-on-joined-table