在`SQLite`中获取按类别和子类别分组的表中的子类别计数

abithluo 于 2023-05-07 发布在 SQLite

关注(0)|答案(2)|浏览(382)

下面是一个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

其中字段EmployeeId、LoanCatId和LoanSubCatId分别引用以下表的ID作为外键：
表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'（Loans表的第一行）：

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

数据库是SQLite。先谢谢你了。

sqlite

来源：https://stackoverflow.com/questions/76189832/getting-the-count-of-subcategories-in-a-table-grouped-by-categories-and-subcateg

2条答案

按热度按时间

ldfqzlk81#

您需要LoanCategories和LoanSubCategories的CROSS联接来获取类别和子类别的所有组合，并需要LEFT联接到Loans以获取所需的特定日期。
最后，您必须为每个类别/子类别进行聚合：

SELECT c.CategoryName, 
       s.CategoryName AS SubCategoryName,
       COUNT(l.LoanDate) AS Count
FROM LoanCategories c CROSS JOIN LoanSubCategories s
LEFT JOIN Loans l ON l.LoanCatId = c.CategoryId AND l.LoanSubCatId = s.CategoryId AND l.LoanDate = '19990125'
GROUP BY c.CategoryId, s.CategoryId;

参见demo。

赞(0）回复(0）举报 2023-05-07

mkh04yzy2#

在categories表上使用CROSS JOIN获得所有可能的组合，使用right join合并数据，使用条件聚合获得所需的结果。

select s.catName, s.subCatName, count(case when l.LoanDate = 19990125 then 1 end) as count_
from Loans l
inner join Employees e on e.EmployeeId = l.EmployeeId 
right join (
  select c.CategoryId as catId, c.CategoryName as catName, s.CategoryId as subCatId, s.CategoryName as subCatName
  FROM LoanCategories c 
  CROSS JOIN LoanSubCategories s 
) as s on s.catId = l.LoanCatId and s.subCatId = l.LoanSubCatId
group by s.catId, s.subCatId

Demo here

赞(0）回复(0）举报 2023-05-07

我来回答

在`SQLite`中获取按类别和子类别分组的表中的子类别计数

2条答案

相关问题

热门标签

最新问答