Oracle中的条件左联接，每个实体单行

我需要一份我公司的名单。
每年，公司可能会被分配到一个类别。因此，每年都有一组类别，公司可能（或可能不）必须被分配到给定年份的类别之一。
在我的公司列表中，我想添加有关公司当前年份（假设为2023年）的类别信息。如果今年没有类别，则为null。
下面是我复制和粘贴的示例模式：

-- Table COMPANY
CREATE TABLE COMPANY (
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(20) NOT NULL
);

INSERT INTO COMPANY (ID, NAME) VALUES (1, 'Meta');
INSERT INTO COMPANY (ID, NAME) VALUES (2, 'Alphabet');
INSERT INTO COMPANY (ID, NAME) VALUES (3, 'Microsoft');

-- Table EVENT, containing information about the year
CREATE TABLE EVENT (
    ID NUMBER PRIMARY KEY,
    YEAR NUMBER NOT NULL
);

INSERT INTO EVENT (ID, YEAR) VALUES (1, 2021);
INSERT INTO EVENT (ID, YEAR) VALUES (2, 2022);
INSERT INTO EVENT (ID, YEAR) VALUES (3, 2023);

-- Table CATEGORY referencing the table EVENT
CREATE TABLE CATEGORY (
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(20) NOT NULL,
    EVENT_ID NUMBER,
    CONSTRAINT fk_event FOREIGN KEY (EVENT_ID) REFERENCES EVENT(ID)
);

-- Populating the table CATEGORY
INSERT INTO CATEGORY (ID, NAME, EVENT_ID) VALUES (1, 'A 21', 1);
INSERT INTO CATEGORY (ID, NAME, EVENT_ID) VALUES (2, 'A 22', 2);
INSERT INTO CATEGORY (ID, NAME, EVENT_ID) VALUES (3, 'A 23', 3);
INSERT INTO CATEGORY (ID, NAME, EVENT_ID) VALUES (4, 'B 21', 1);
INSERT INTO CATEGORY (ID, NAME, EVENT_ID) VALUES (5, 'B 22', 2);
INSERT INTO CATEGORY (ID, NAME, EVENT_ID) VALUES (6, 'B 23', 3);

-- Assigning companies to categories, MANY TO MANY relation
CREATE TABLE CMP_X_CAT (
    COMPANY_ID NUMBER,
    CATEGORY_ID NUMBER,
    CONSTRAINT pk_cmp_x_cat PRIMARY KEY (COMPANY_ID, CATEGORY_ID),
    CONSTRAINT fk_cmp FOREIGN KEY (COMPANY_ID) REFERENCES COMPANY(ID),
    CONSTRAINT fk_cat FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORY(ID)
);

INSERT INTO CMP_X_CAT (COMPANY_ID, CATEGORY_ID) VALUES (1, 1); -- Meta: A 21
INSERT INTO CMP_X_CAT (COMPANY_ID, CATEGORY_ID) VALUES (1, 2); -- Meta: A 22
INSERT INTO CMP_X_CAT (COMPANY_ID, CATEGORY_ID) VALUES (2, 5); -- Alphabet: B 22
INSERT INTO CMP_X_CAT (COMPANY_ID, CATEGORY_ID) VALUES (2, 6); -- Alphabet: B 23

字符串
在结果集中，每个公司应该只出现一次。
我正在努力的结果：

有时候，有些公司是失踪
有时每个公司有不止一行

看看我的尝试和结果：
1.为每个类别输出一行。如果公司没有任何类别，也输出一行：

SELECT company.*, category.*, event.*
FROM company 
LEFT JOIN cmp_x_cat ON company.id = cmp_x_cat.company_id 
LEFT JOIN category ON category.id = cmp_x_cat.category_id 
LEFT JOIN event ON category.event_id = event.id
ORDER BY company.id;

ID NAME                         ID NAME                   EVENT_ID         ID       YEAR
---------- -------------------- ---------- -------------------- ---------- ---------- ----------
         1 Meta                          1 A 21                          1          1       2021
         1 Meta                          2 A 22                          2          2       2022
         2 Alphabet                      5 B 22                          2          2       2022
         2 Alphabet                      6 B 23                          3          3       2023
         3 Microsoft

的数据
1.使用连接条件，打印与上面相同的内容，但只有在等于2023时才设置event。这有助于我识别没有当前年度类别的公司：

SELECT company.*, category.*, event.*
FROM company 
LEFT JOIN cmp_x_cat ON company.id = cmp_x_cat.company_id 
LEFT JOIN category ON category.id = cmp_x_cat.category_id 
LEFT JOIN event ON category.event_id = event.id AND event.year = 2023 OR event.year IS NULL
ORDER BY company.id;

ID NAME                         ID NAME                   EVENT_ID         ID       YEAR
---------- -------------------- ---------- -------------------- ---------- ---------- ----------
         1 Meta                          2 A 22                          2                      
         1 Meta                          1 A 21                          1                      
         2 Alphabet                      5 B 22                          2                      
         2 Alphabet                      6 B 23                          3          3       2023
         3 Microsoft

的字符串

INNER JOIN跳过没有任何类别的公司（Microsoft）：

SELECT company.*, category.*, event.*
FROM company 
LEFT JOIN cmp_x_cat ON company.id = cmp_x_cat.company_id 
LEFT JOIN category ON category.id = cmp_x_cat.category_id 
INNER JOIN event ON category.event_id = event.id
ORDER BY company.id;

ID NAME                         ID NAME                   EVENT_ID         ID       YEAR
---------- -------------------- ---------- -------------------- ---------- ---------- ----------
         1 Meta                          1 A 21                          1          1       2021
         1 Meta                          2 A 22                          2          2       2022
         2 Alphabet                      5 B 22                          2          2       2022
         2 Alphabet                      6 B 23                          3          3       2023

1.打印每个公司的单行，这是我想要的，但不包含Meta。因为它被分配到类别，但不是从2023年开始：

SELECT company.*, category.*, event.*
FROM company 
LEFT JOIN cmp_x_cat ON company.id = cmp_x_cat.company_id 
LEFT JOIN category ON category.id = cmp_x_cat.category_id 
LEFT JOIN event ON category.event_id = event.id
WHERE event.year = 2023 OR event.year IS NULL
ORDER BY company.id;

ID NAME                         ID NAME                   EVENT_ID         ID       YEAR
---------- -------------------- ---------- -------------------- ---------- ---------- ----------
         2 Alphabet                      6 B 23                          3          3       2023
         3 Microsoft

我能够使用嵌套SELECT实现我想要的东西，但我不能使用它，因为我受到框架的限制-我需要将SQL转换为Doctrine DQL，它不支持嵌套SELECT。
也许可以对行进行分组，或者以某种方式使用UNION？

编辑：* 以下是可能的预期结果：

ID NAME           ID  NAME     EVENT_ID         ID       YEAR
--- ------------- ---- ------ ---------- ---------- ----------
  1 Meta           ??? ???          null       null       null
  2 Alphabet         6 B 23            3          3       2023
  3 Microsoft     null null         null       null       null

型

可以使用方括号指定联接的优先级：

SELECT  c.*, t.name, e.year
FROM    company c
        LEFT OUTER JOIN (
          cmp_x_cat x
          INNER JOIN category t
          ON x.category_id = t.id
          INNER JOIN event e
          ON e.id = t.event_id AND e.year = 2023
        )
        ON (c.id = x.company_id)

字符串
对于样本数据，输出：
| ID|名称|名称|年|
| --|--|--|--|
| 2 |Alphabet| B 23| 2023 |
| 3 |微软| * 空 | 空 *|
| 1 |Meta| * 空 | 空 *|
如果一个公司在一年中可以有多个类别，并且你想在一行中以逗号分隔的列表显示它们，那么使用LISTAGG进行聚合：

SELECT  c.id,
        MAX(c.name) AS name,
        LISTAGG(t.name, ',') WITHIN GROUP (ORDER BY t.name) AS categories
FROM    company c
        LEFT OUTER JOIN (
          cmp_x_cat x
          INNER JOIN category t
          ON x.category_id = t.id
          INNER JOIN event e
          ON e.id = t.event_id AND e.year = 2023
        )
        ON (c.id = x.company_id)
GROUP BY c.id

型
对于样本数据（没有任何具有多个类别的公司），输出：
| ID|名称|类别|
| --|--|--|
| 1 |Meta| * 空 *|
| 2 |Alphabet| B 23|
| 3 |微软| * 空 *|
fiddle

Oracle中的条件左联接，每个实体单行

1条答案

相关问题

热门标签

最新问答