一个查询中的不同记录计数值

本文介绍了一个查询中的不同记录计数值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的目标是获得如下所示的结果

My goal is to achieve a result set like the following

CODE | TOTAL1 | TOTAL2 1 | 56 | 34 2 | 12 | 15 3 | 90 | 3

有2个表，例如tableA和tableB 计数因tableB.type而不同

There are 2 tables e.g tableA and tableB The counts are different by tableB.type

SELECT code, COUNT (*) AS total1 FROM tableA a WHERE a.ID IN (select ID from tableB where type = 'XYZ') GROUP BY code SELECT code, COUNT (*) AS total2 FROM tableA a WHERE a.ID IN (select ID from tableB where type = 'ABC') GROUP BY code

我想在同一查询中显示每种类型的每个代码的计数

I'd like to display the count for each code per type in the same query

预先感谢

推荐答案

子查询:

SELECT code, (select COUNT (*) AS total1 FROM tableA a1 WHERE a.ID IN (select ID from tableB where type = 'XYZ') and a1.code = tableA.code) as Total1, (select COUNT (*) AS total2 FROM tableA a2 WHERE a.ID IN (select ID from tableB where type = 'ABC') and a2.code = tableA.code) as Total2) from tableA group by Code