在SQLite中按贷款类别与子类别分组统计指定日期的子类别数量

现有表结构与数据 #

Loans表 #

EmployeeId  LoanCatId   LoanSubCatId    LoanDate
------------------------------------------------
1           4           1               19990125
3           3           2               20101210
6           1           1               19910224
4           4           2               20120219
1           3           1               19920214
2           4           2               19930614
1           3           2               19840705
6           1           1               20030917
5           1           1               19900204
3           1           2               20181113

Employees表 #

EmployeeId  Name
------------------
1           John
2           Jack
3           Alex
4           Fred
5           Danny
6           Russel

LoanCategories表 #

CategoryId  CategoryName
------------------------
1           CA
2           CB
3           CC
4           CD

LoanSubCategories表 #

CategoryId  CategoryName
------------------------
1           SCA
2           SCB

需求说明 #

指定一个LoanDate（示例为19990125），统计所有贷款分类与子分类组合的贷款数量，无对应贷款的组合需显示计数为0，输出格式如下：

CategoryName    SubCategoryName Count
-------------------------------------
CA              SCA             0
CA              SCB             0
CB              SCA             0
CB              SCB             0
CC              SCA             0
CC              SCB             0
CD              SCA             1
CD              SCB             0

解决方案SQL #

WITH all_category_combinations AS (
    -- 生成所有分类与子分类的全组合
    SELECT 
        lc.CategoryName,
        lsc.CategoryName AS SubCategoryName,
        lc.CategoryId AS CatId,
        lsc.CategoryId AS SubCatId
    FROM LoanCategories lc
    CROSS JOIN LoanSubCategories lsc
),
target_date_counts AS (
    -- 统计指定日期下各分类子分类的贷款数
    SELECT 
        LoanCatId,
        LoanSubCatId,
        COUNT(*) AS count
    FROM Loans
    WHERE LoanDate = '19990125' -- 替换为目标日期
    GROUP BY LoanCatId, LoanSubCatId
)
-- 关联全组合与统计结果，空值补0
SELECT 
    acc.CategoryName,
    acc.SubCategoryName,
    COALESCE(tdc.count, 0) AS Count
FROM all_category_combinations acc
LEFT JOIN target_date_counts tdc 
    ON acc.CatId = tdc.LoanCatId 
    AND acc.SubCatId = tdc.LoanSubCatId
ORDER BY acc.CategoryName, acc.SubCategoryName;

代码解释 #

1. all_category_combinations：用CROSS JOIN生成分类和子分类的所有可能组合，确保结果不遗漏任何分类对。
2. target_date_counts：筛选指定日期的贷款数据，按分类和子分类分组统计实际贷款数量。
3. 最终查询：通过LEFT JOIN保留所有分类组合，用COALESCE将无对应数据的组合计数替换为0，最后按分类名称排序输出。

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