MySQL JOIN cu LIMIT 1 pe tabelul alăturat (Programare, Mysql, A Se Alătura, Limita)

John Davidson a intrebat.

Vreau să alătur două tabele, dar să obțin doar 1 înregistrare din tabelul2 pentru fiecare înregistrare din tabelul1

De exemplu:

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

Acest lucru mi-ar obține toate înregistrările din products, , ceea ce nu este ceea ce îmi doresc. Vreau 1 [primul] produs [primul] pentru fiecare categorie (am un sort coloană în câmpul produse).

Cum procedez pentru a face acest lucru?

11 răspunsuri
goggin13

Eu aș încerca ceva de genul acesta:

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

Comentarii

    20

  • acest lucru aruncă o eroare, Error Code: 1241. Operandul trebuie să conțină 1 coloană(e) –  > Por kabirbaidhya.
  • Întrebarea se referă la join. –  > Por Constantin.
Kostanos

Îmi place mai mult o altă abordare descrisă într-o întrebare similară: https://stackoverflow.com/a/11885521/2215679

Această abordare este mai bună mai ales în cazul în care aveți nevoie să afișați mai multe câmpuri în SELECT. Pentru a evita Error Code: 1241. Operand should contain 1 column(s) sau dubla sub-selecție pentru fiecare coloană.

Pentru situația dumneavoastră, interogarea ar trebui să arate astfel:

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
 )

Comentarii

  • @BenoitDuffez pun pariu că este mai dificil cu exact un grad (sub-selecția). –  > Por ulkas.
  • @BenoitDuffez – Vedeți soluția mea pentru a răspunde la întrebarea dvs. cu privire la impactul asupra performanței –  > Por Gravy.
  • Din păcate, prea lent 🙁 – –  > Por Arnes.
Gravy

Răspunsul acceptat de @goggin13 pare greșit. Alte soluții furnizate până în prezent vor funcționa, dar suferă de problema n+1 și, ca atare, suferă o lovitură de performanță.

Problema n+1: Dacă există 100 de categorii, atunci ar trebui să facem 1 select pentru a obține categoriile, apoi, pentru fiecare dintre cele 100 de categorii returnate, ar trebui să facem un select pentru a obține produsele din acea categorie. Astfel, ar trebui efectuate 101 interogări SELECT.

Soluția mea alternativă rezolvă problema n+1 și, prin urmare, ar trebui să fie mult mai performantă, deoarece se efectuează doar 2 selecții.

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;

Comentarii

  • Este interesant. În mod normal, dacă folosiți ORDER BY și GROUP BY într-o singură interogare, GROUP BY se va executa primul, iar ORDER BY va sorta grupurile. Să înțeleg că aceasta este modalitatea de a face ca ORDER BY să fie primul. –  > Por Gherman.
  • @German – Este corect. Și în acest fel, puteți selecta group_concat asigurându-vă că rezultatele sunt în ordinea corectă. –  > Por Gravy.
  • Poate cineva să sugereze o modificare pentru a face acest lucru să funcționeze cu MySQL 5.7? Se plânge de coloane neagregate, deoarece ONLY_FULL_GROUP_BY este activat în mod implicit. Mulțumesc! –  > Por Chris Bartley.
  • @ChrisBartley – Adăugați pur și simplu câmpurile lipsă de grupare prin câmpuri pe orice coloane agregate. De asemenea, ați putea utiliza ANY_VALUE() conform dev.mysql.com/doc/refman/5.7/en/group-by-handling.html –  > Por Gravy.
Jessé Catrinck
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

Acest lucru va returna primele date din produse (egal cu limita 1)

Comentarii

  • Gruparea pare să aibă un cost suplimentar masiv. Dacă execut interogarea fără ea (cu o limită), aceasta este mult mai mult mai rapid. Presupun că ignoră indicii? –  > Por Robbie Averill.
  • acest lucru va returna unul, dar ar fi neapărat primul? –  > Por Yazgan.
  • Ușor de implementat și, dacă este necesar, puteți grupa după mai multe câmpuri. –  > Por caram.
Krab

Ce ziceți de asta?

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;

la_kal

Clauza With ar fi de ajuns. Ceva de genul acesta:

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

Yaki Klein

Când folosiți postgres, puteți utiliza clauza DISTINCT ON pentru a limita numărul de coloane returnate din oricare dintre tabele.

Iată un exemplu de cod:

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)

Șmecheria este să nu vă alăturați direct la tabelul cu mai multe apariții ale id-ului, ci mai degrabă să creați mai întâi un tabel cu o singură apariție pentru fiecare id.

Mchl

Presupunând că doriți un produs cu MIN()valoarea inițială în sort ar trebui să arate cam așa.

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

Alan

Un alt exemplu cu 3 tabele imbricate:1/ User2/ UserRoleCompanie3/ Companie

  • 1 utilizator are mai multe UserRoleCompanie.
  • 1 UserRoleCompanie are 1 utilizator și 1 Companie.
  • 1 Companie are mai multe UserRoleCompanie
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            | [email protected]           | cjtuflye81dwt0748e4hnkiv0 | OWNER        | cjtuflye71dws0748r7vtuqmg | leclerc           |

Abdias Natanael Vrech

După părerea mea, acesta este cel mai bun răspuns (generalizându-l):

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  
    )  

Comentarii

  • Răspundeți la o întrebare veche de 10 ani. Acest răspuns nu va limita rezultatul, deoarece clauza WHERE nu pare să facă nimic pentru ca selecția JOIN să fie unică. –  > Por Dennis .
Matías

Înlocuiți tabelele cu ale dvs:

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