oracle查询with groupby子句

Question

我有一个上传表，如下所示：

bulk_upload_hist

id  file_name   doc_blob    upload_date upload_by
10  abc.pdf                 12-APR-21   123
11  xyz.pdf                 12-APR-21   123

清单历史记录存储文件中的记录，如下所示: inventory_doc_hist

id upload_id    upload_status   create_date     create_by   inv_doc_type
1   10          1               12-APR-21       123         20
2   10          1               12-APR-21       123         20
3   10          0               12-APR-21       123         10
4   10          1               12-APR-21       123         10
4   11          1               12-APR-21       123         20
5   11          0               12-APR-21       123         10

我希望每次批量上传的输出如下所示：

id  file_name   successful/10   Successful/20   UnSuccessful    upload_date upload_by
10  abc.pdf     2               1               1               12-APR-21   123
11  xyz.pdf     1               1               0               12-APR-21   123

做这件事最好的方法是什么？

Answer 1

我认为您需要一个join和条件聚合：

select buh.id, buh.file_name,
       sum(case when ih.inv_doc_type = 10 then 1 else 0 end) as num_successful_10,
       sum(case when ih.inv_doc_type = 20 then 1 else 0 end) as num_successful_20,
       sum(case when ih.inv_doc_type not in (10, 20) then 1 else 0 end) as num_successful_20,
       ih.upload_date, ih.upload_by
from bulk_upload_hist buh join
     inventory_history ih
     on ih.upload_id = ih.id
group by buh.id, buh.file_name, ih.upload_date, ih.upload_by;