Oracle中实现单实体单行的条件左连接查询需求

需要获取公司列表，要求每个公司在结果集中仅出现一次，同时附带该公司2023年度的分类信息（若无则显示null）。

示例数据库Schema #

-- 公司表 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');

-- 年度事件表 EVENT
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);

-- 分类表 CATEGORY（关联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)
);

-- 插入分类数据
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);

-- 公司-分类关联表（多对多）
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

遇到的问题 #

尝试多种查询方式后，要么部分公司缺失，要么同一公司出现多行。之前用嵌套查询实现了需求，但因框架限制需要转换为Doctrine DQL，而该DQL不支持所需的嵌套查询，现寻求替代方案（如分组、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 

1. 原生SQL实现（左连接+分组） #

通过左连接关联所有表，并仅过滤2023年的分类数据，同时用GROUP BY确保每个公司只出现一行：

SELECT 
    c.ID AS company_id,
    c.NAME AS company_name,
    MAX(ct.ID) AS category_id,
    MAX(ct.NAME) AS category_name,
    MAX(ct.EVENT_ID) AS event_id,
    MAX(e.ID) AS event_table_id,
    MAX(e.YEAR) AS event_year
FROM COMPANY c
LEFT JOIN CMP_X_CAT cc ON c.ID = cc.COMPANY_ID
LEFT JOIN CATEGORY ct ON cc.CATEGORY_ID = ct.ID
LEFT JOIN EVENT e ON ct.EVENT_ID = e.ID AND e.YEAR = 2023
GROUP BY c.ID, c.NAME
ORDER BY c.ID;

逻辑说明：

• 左连接保证所有公司都被保留，即使没有2023年的分类
• 用MAX()聚合函数提取2023年的分类数据（若存在），无数据则返回null
• GROUP BY c.ID, c.NAME强制每个公司仅输出一行

2. Doctrine DQL实现（适配框架限制） #

将上述逻辑转换为DQL，避免嵌套查询，使用左连接和分组：

SELECT 
    c.id AS company_id,
    c.name AS company_name,
    MAX(ct.id) AS category_id,
    MAX(ct.name) AS category_name,
    MAX(ct.eventId) AS event_id,
    MAX(e.id) AS event_table_id,
    MAX(e.year) AS event_year
FROM App\Entity\Company c
LEFT JOIN c.categories cc
LEFT JOIN cc.category ct
LEFT JOIN ct.event e WITH e.year = 2023
GROUP BY c.id, c.name
ORDER BY c.id

注： 需对应你的Doctrine实体映射关系，若公司和分类是直接多对多映射（跳过中间表），DQL可简化为：

SELECT 
    c.id AS company_id,
    c.name AS company_name,
    MAX(ct.id) AS category_id,
    MAX(ct.name) AS category_name,
    MAX(ct.event.id) AS event_id,
    MAX(ct.event.id) AS event_table_id,
    MAX(ct.event.year) AS event_year
FROM App\Entity\Company c
LEFT JOIN c.categories ct WITH ct.event.year = 2023
GROUP BY c.id, c.name
ORDER BY c.id

3. UNION备选方案 #

如果分组方式不符合需求，可通过UNION组合两类结果：

-- 有2023分类的公司
SELECT 
    c.ID, c.NAME,
    ct.ID, ct.NAME, ct.EVENT_ID,
    e.ID, e.YEAR
FROM COMPANY c
JOIN CMP_X_CAT cc ON c.ID = cc.COMPANY_ID
JOIN CATEGORY ct ON cc.CATEGORY_ID = ct.ID
JOIN EVENT e ON ct.EVENT_ID = e.ID
WHERE e.YEAR = 2023
UNION
-- 没有2023分类的公司
SELECT 
    ID, NAME,
    NULL, NULL, NULL,
    NULL, NULL
FROM COMPANY c
WHERE NOT EXISTS (
    SELECT 1 FROM CMP_X_CAT cc
    JOIN CATEGORY ct ON cc.CATEGORY_ID = ct.ID
    JOIN EVENT e ON ct.EVENT_ID = e.ID
    WHERE cc.COMPANY_ID = c.ID AND e.YEAR = 2023
)
ORDER BY ID;

注意： Doctrine DQL对UNION的支持有限，需确认框架版本是否兼容，优先推荐左连接+分组的方案。

内容的提问来源于stack exchange，提问作者Radium