Eine Tabelle oder ein Array für jede Zeile einer größeren Select-Abfrage auswählen

Question

Verwendung von Oracle 11g

Angenommen, wir haben zwei Tabellen:

CREATE TABLE items (
    item_id NUMBER(22,0)
);

CREATE TABLE messages (
    item_id NUMBER(22,0),
    message_code NUMBER(22,0)
);

wo es eine Beziehung von einem (Artikel) zu vielen (Nachrichten) gibt. Ich möchte das gesamte Material in einer einzigen Abfrage auswählen, um es in Java zu erhalten. Ist es möglich, eine solche Abfrage zu schreiben, dass sie mir eine Ausgabe wie geben würde:

ID         MESSAGE
----------------------------------
1          (100, 105, 201)
2          (100, 105)

wobei die Spalte MESSAGE ein Array oder etwas Ähnliches ist (keine verkettete Zeichenkette)?

Answer 1

Sie können Cursor auf Feldebene verwenden:

WITH    items AS
        (
        SELECT  1 AS item_id
        FROM    dual
        UNION ALL
        SELECT  2 AS item_id
        FROM    dual
        ),
        messages AS
        (
        SELECT  1 AS item_id, 100 AS message_code
        FROM    dual
        UNION ALL
        SELECT  1 AS item_id, 105 AS message_code
        FROM    dual
        UNION ALL
        SELECT  1 AS item_id, 201 AS message_code
        FROM    dual
        UNION ALL
        SELECT  2 AS item_id, 100 AS message_code
        FROM    dual
        UNION ALL
        SELECT  2 AS item_id, 15 AS message_code
        FROM    dual
        )
SELECT  item_id,
        CURSOR
        (
        SELECT  message_code
        FROM    messages m
        WHERE   m.item_id = i.item_id
        )
FROM    items i

oder einen Tabellentyp erstellen und eine MULTISET der Werte zu diesem Typ:

CREATE TYPE t_message_code AS TABLE OF INTEGER

WITH    items AS
        (
        SELECT  1 AS item_id
        FROM    dual
        UNION ALL
        SELECT  2 AS item_id
        FROM    dual
        ),
        messages AS
        (
        SELECT  1 AS item_id, 100 AS message_code
        FROM    dual
        UNION ALL
        SELECT  1 AS item_id, 105 AS message_code
        FROM    dual
        UNION ALL
        SELECT  1 AS item_id, 201 AS message_code
        FROM    dual
        UNION ALL
        SELECT  2 AS item_id, 100 AS message_code
        FROM    dual
        UNION ALL
        SELECT  2 AS item_id, 15 AS message_code
        FROM    dual
        )
SELECT  item_id,
        CAST
        (
        MULTISET
        (
        SELECT  message_code
        FROM    messages m
        WHERE   m.item_id = i.item_id
        ) AS t_message_code
        )
FROM    items i

Answer 2

Um auf Quassnois Antwort aufzubauen, gibt es auch die COLLECT Funktion, die dasselbe tut wie die MULTISET in der anderen Antwort, aber ohne die Unterabfrage.

CREATE TYPE t_message_code AS TABLE OF INTEGER;

WITH    items AS
        (
        SELECT  1 AS item_id
        FROM    dual
        UNION ALL
        SELECT  2 AS item_id
        FROM    dual
        ),
        messages AS
        (
        SELECT  1 AS item_id, 100 AS message_code
        FROM    dual
        UNION ALL
        SELECT  1 AS item_id, 105 AS message_code
        FROM    dual
        UNION ALL
        SELECT  1 AS item_id, 201 AS message_code
        FROM    dual
        UNION ALL
        SELECT  2 AS item_id, 100 AS message_code
        FROM    dual
        UNION ALL
        SELECT  2 AS item_id, 15 AS message_code
        FROM    dual
        )
SELECT  i.item_id, 
  cast(collect(m.message_code) as t_message_code) as messages_codes
FROM    
  items i
  join messages m on (m.item_id = i.item_id)
group by i.item_id;

Answer 3

ANMERKUNG Dies wird Ihnen einen String liefern (den Sie nicht wollten), aber ich lasse diese Antwort hier, da diese Frage oft gestellt wird und noch nicht alle Leute diese einfache Lösung kennen

  SELECT item_id AS id,
         wm_concat(message_code) AS message
    FROM messages
GROUP BY item_id

Wenn Sie Klammern um die Liste der Nachrichten-IDs benötigen, können Sie die Verkettung verwenden:

'(' || wm_concat(message_code) || ')' AS message