Intrebari generale

Re: Join cu doar primul rand din tabela

rsocol — Tue, 02 Sep 2008 06:09:58 GMT

Dar hai să complicăm puţin problema şi să presupunem că ai mai multe coloane în tabela B:

CREATE TABLE A (
    ID_A int PRIMARY KEY,
    A1 varchar(10),
    A2 varchar(10)
)

CREATE TABLE B (
    ID_B int PRIMARY KEY,
    ID_A int NOT NULL REFERENCES A,
    B1 varchar(10),
    B2 varchar(10)
)

INSERT INTO A VALUES (1, 'X1', 'Y1')
INSERT INTO A VALUES (2, 'X2', 'Y2')
INSERT INTO A VALUES (3, 'X3', 'Y3')

INSERT INTO B VALUES (1, 1, 'ABC', '321')
INSERT INTO B VALUES (4, 2, 'ABC', '321')
INSERT INTO B VALUES (2, 2, 'BBB', '222')
INSERT INTO B VALUES (3, 2, 'AAA', '333')

În acest caz, să presupunem că rezultatul dorit ar fi:

ID_A        A1         A2         B1         B2
----------- ---------- ---------- ---------- ----------
1           X1         Y1         ABC        321
2           X2         Y2         AAA        333
3           X3         Y3         NULL       NULL
4           X4         Y4         CCC        123

(adică pentru fiecare rând din A, dorim primul rând din B, în ordinea dată de B1 şi B2)

Dacă modificăm primul query de mai sus, astfel:

SELECT A.*,
    (SELECT MIN(B1) FROM B WHERE B.ID_A=A.ID_A) B1,
    (SELECT MIN(B2) FROM B WHERE B.ID_A=A.ID_A) B2
FROM A

atunci obţinem alt rezultat:

ID_A        A1         A2         B1         B2
----------- ---------- ---------- ---------- ----------
1           X1         Y1         ABC        321
2           X2         Y2         AAA        222
3           X3         Y3         NULL       NULL
4           X4         Y4         CCC        123

pentru că la rândul cu ID_A=2 s-a luat cel mai mic B2, în loc să se ia B2-ul corespunzător rândului cu cel mai mic B1.

Putem obţine rezultatul dorit cu unul din următoarele query-uri:

SELECT X.*,
    (SELECT MIN(B2) FROM B WHERE B.ID_A=X.ID_A AND B.B1=X.B1) B2
FROM (
    SELECT A.*,
        (SELECT MIN(B1) FROM B WHERE B.ID_A=A.ID_A) B1
    FROM A
) X

SELECT A.*, B.B1, B.B2 FROM A LEFT JOIN B ON A.ID_A=B.ID_A
AND B.ID_B=(
    SELECT TOP 1 ID_B FROM B WHERE A.ID_A=B.ID_A
    ORDER BY B1, B2
)

SELECT X.ID_A, X.A1, X.A2, B.B1, B.B2 FROM (
    SELECT A.*, (
        SELECT TOP 1 ID_B FROM B WHERE A.ID_A=B.ID_A
        ORDER BY B1, B2
    ) ID_B
    FROM A
) X LEFT JOIN B ON X.ID_B=B.ID_B

SELECT A.*, X.B1, X.B2 FROM A LEFT JOIN (
    SELECT B.*, ROW_NUMBER() OVER (PARTITION BY ID_A ORDER BY B1,B2) N
    FROM B
) X ON A.ID_A=X.ID_A AND X.N=1;

Răzvan

Re: Join cu doar primul rand din tabela

rsocol — Tue, 02 Sep 2008 05:52:20 GMT

Când ai o problemă de acest gen, e recomandabil să prezinţi structura tabelelor (sub formă de CREATE TABLE, inclusiv constraint-urile aferente) şi date de test (sub formă de INSERT INTO), de exemplu:

CREATE TABLE A (
    ID_A int PRIMARY KEY,
    A1 varchar(10),
    A2 varchar(10)
)

CREATE TABLE B (
    ID_B int PRIMARY KEY,
    ID_A int NOT NULL REFERENCES IA,
    B1 varchar(10)
)

INSERT INTO A VALUES (1, 'X1', 'Y1')
INSERT INTO A VALUES (2, 'X2', 'Y2')
INSERT INTO A VALUES (3, 'X3', 'Y3')

INSERT INTO B VALUES (1, 1, 'ABC')
INSERT INTO B VALUES (4, 2, 'ABC')
INSERT INTO B VALUES (2, 2, 'BBB')
INSERT INTO B VALUES (3, 2, 'AAA')

Atunci când spui "primul rând din tabela B" trebuie să precizezi în ce ordine vrei să consideri rândurile respective, deoarece rândurile dintr-o tabelă nu au o ordine predefinită (nu contează ordinea în care au fost adăugate). Presupunând că ordinea dorită este dată de coloana B1, rezultatul aşteptat ar putea fi următorul:

ID_A        A1         A2         B1
----------- ---------- ---------- ----------
1           X1         Y1         ABC
2           X2         Y2         AAA
3           X3         Y3         NULL

Rezultatul de mai sus poate fi obţinut cu oricare din următoarele query-uri:

SELECT A.*, (SELECT MIN(B1) FROM B WHERE B.ID_A=A.ID_A) B1 FROM A;

SELECT A.*, (SELECT TOP 1 B1 FROM B WHERE B.ID_A=A.ID_A ORDER BY B1) B1 FROM A;

SELECT A.*, MIN(B1) AS B1
FROM A LEFT JOIN B ON B.ID_A=A.ID_A
GROUP BY A.ID_A, A1, A2;

SELECT A.*, X.B1 FROM A LEFT JOIN (
    SELECT ID_A, MIN(B1) B1 FROM B
    GROUP BY ID_A
) X ON A.ID_A=X.ID_A;

SELECT A.*, X.B1 FROM A LEFT JOIN (
    SELECT B.*, ROW_NUMBER() OVER (PARTITION BY ID_A ORDER BY B1) N
    FROM B
) X ON A.ID_A=X.ID_A AND X.N=1;

WITH X AS (
    SELECT B.*, ROW_NUMBER() OVER (PARTITION BY ID_A ORDER BY B1) N
    FROM B
)
SELECT A.*, X.B1 FROM A LEFT JOIN X ON A.ID_A=X.ID_A AND X.N=1

Răzvan

Join cu doar primul rand din tabela

liviu.costea — Mon, 01 Sep 2008 15:08:49 GMT

Am o tabela A (id, A1, A2) si o tabela B (id_A, id_B, B1), B fiind legat de A. Vreau sa scot randul din A cu doar primul rand din B. Inainte (SQL 2000) foloseam un subquery cu TOP 1, mai recent (2005) am inceput sa folosesc CTE si ROW_NUMBER().

Cred ca problema asta a mai fost intalnita si de altcineva, deci voi ce folositi (poate stiti o metoda mai buna)?