Oracle查询语句编写：统计各分行储蓄账户数量并显示地址

看起来你需要统计各分行的储蓄账户数量并关联显示分行地址，我先帮你梳理现有代码的问题，再给出完整的可执行方案：

1. 补全并修正对象类型与表定义 #

你的现有代码存在语法错误（比如中文括号）和不完整的地方，我先帮你补全并修正：

-- 定义地址对象类型
CREATE TYPE address AS OBJECT (
    street VARCHAR2(20),
    city VARCHAR2(10),
    p_code VARCHAR2(8)
);

-- 定义分行对象类型
CREATE TYPE Branch AS OBJECT (
    BId VARCHAR2(3), -- 分行ID
    brAddress address, -- 分行地址
    bPhone INT
);

-- 创建分行表（修正语法，添加主键约束）
CREATE TABLE tb_Branch OF Branch (
    BId PRIMARY KEY
);

-- 定义账户对象类型（补全分支关联字段）
CREATE TYPE account AS OBJECT (
    accNum VARCHAR2(4), -- 账户编号（主键）
    accType VARCHAR2(8), -- 账户类型：current/saving
    balance NUMBER(8,2),
    BId_ref VARCHAR2(3) -- 关联分行ID的外键字段
);

-- 创建账户表，添加类型检查和外键约束
CREATE TABLE tb_Account OF account (
    accNum PRIMARY KEY,
    CONSTRAINT chk_accType CHECK (accType IN ('current', 'saving')),
    CONSTRAINT fk_acc_branch FOREIGN KEY (BId_ref) REFERENCES tb_Branch(BId)
);

2. 统计储蓄账户数量的核心查询 #

现在可以通过分组关联查询，实现统计每个分行的储蓄账户数量，并展示完整分行地址：

SELECT 
    b.BId AS 分行ID,
    -- 拼接地址字段得到完整地址
    b.brAddress.street || ', ' || b.brAddress.city || ' ' || b.brAddress.p_code AS 分行完整地址,
    COUNT(a.accNum) AS 储蓄账户数量
FROM tb_Branch b
LEFT JOIN tb_Account a 
    ON b.BId = a.BId_ref 
    AND a.accType = 'saving' -- 仅筛选储蓄账户
GROUP BY b.BId, b.brAddress.street, b.brAddress.city, b.brAddress.p_code
ORDER BY 储蓄账户数量 DESC;

关键说明： #

• 使用LEFT JOIN保证没有储蓄账户的分行也会被显示（数量为0），避免遗漏数据
• 按分行ID和地址字段分组，确保统计结果与分行一一对应
• 排序规则可以根据需求调整，这里按储蓄账户数量倒序排列

3. 可选：优化地址展示方式 #

如果想让地址格式化更优雅，可以给address类型添加一个成员方法来返回完整地址：

-- 重新定义address类型，添加格式化方法
CREATE OR REPLACE TYPE address AS OBJECT (
    street VARCHAR2(20),
    city VARCHAR2(10),
    p_code VARCHAR2(8),
    MEMBER FUNCTION get_full_address RETURN VARCHAR2
);

-- 实现地址格式化方法
CREATE OR REPLACE TYPE BODY address AS
    MEMBER FUNCTION get_full_address RETURN VARCHAR2 IS
    BEGIN
        RETURN street || ', ' || city || ' ' || p_code;
    END;
END;
/

此时查询语句可以简化为：

SELECT 
    b.BId AS 分行ID,
    b.brAddress.get_full_address() AS 分行完整地址,
    COUNT(a.accNum) AS 储蓄账户数量
FROM tb_Branch b
LEFT JOIN tb_Account a 
    ON b.BId = a.BId_ref 
    AND a.accType = 'saving'
GROUP BY b.BId, b.brAddress
ORDER BY 储蓄账户数量 DESC;

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