如何使用链接表中的多个组进行 sql 查询？

我们需要获取 table1 中每条记录的 cdr 和 dxf 格式的文件数。并找到有 2 个 cdr 文件和 4 个 dxf 文件的条目。

create table table1
(
   id int, 
   name varchar(255)
);

create table table2
(
   id int,
   table1_id int,
   filename varchar(255),
   filetype varchar(255)
);

insert into table1 (id,name) values
(123, 'name123'),
(124, 'name124'),
(125, 'name125'),
(126, 'name126'),
(127, 'name127'),
(128, 'name128')

insert into table2 (id,table1_id,filename,filetype) values
(1, 123, 'file1', 'cdr'),
(2, 123, 'file1', 'dxf'),
(3, 123, 'file2', 'cdr'),
(4, 123, 'file2', 'dxf'),
(5, 123, 'file3', 'dxf'),
(6, 123, 'file4', 'dxf'),
(7, 124, 'file11', 'cdr'),
(8, 124, 'file11', 'dxf'),
(9, 124, 'file12', 'cdr'),
(10, 124, 'file12', 'dxf')

select * from table1

编号 | 姓名   
--: | :------
123 | 名称123
124 | 名称124
125 | 名称125
126 | 名称126
127 | 名称127
128 | 名称128

select * from table2

编号 | table1_id | 文件名 | 文件类型
-: | --------: | :------- | :--------
 1 | 123 | 文件1 | cdr     
 2 | 123 | 文件1 | dxf     
 3 | 123 | 文件2 | cdr     
 4 | 123 | 文件2 | dxf     
 5 | 123 | 文件3 | dxf     
 6 | 123 | 文件4 | dxf     
 7 | 124 | 文件11 | cdr     
 8 | 124 | 文件11 | dxf     
 9 | 124 | 文件12 | cdr     
10 | 124 | 文件12 | dxf     

-- пример рабочего запроса. но в нем только по типу cdr ищется.

SELECT table1.id, count(table2.filetype) as cdrCount
FROM table1
INNER JOIN table2 ON table1.id = table2.table1_id
WHERE table2.filetype = 'cdr'
GROUP BY table2.table1_id, table2.filetype
HAVING COUNT(table2.filetype) = 2

编号 | cdrCount
--: | --------:
123 | 2
124 | 2

-- ЭТОТ ЗАПРОС НЕ РАБОТАЕТ

-- Ожидаемый результат:
-- ----------------------------
-- | id  | cdrCount | dxfCount |
-- |-----|----------|----------|
-- | 123 |    2     |    4     |  
-- -----------------------------

SELECT table1.id, count(t21.filetype) as cdrCount, count(t22.filetype) as dxfCount
FROM table1
INNER JOIN table2 t21 ON table1.id = t21.table1_id
INNER JOIN table2 t22 ON table1.id = t22.table1_id
WHERE t21.filetype = 'cdr' AND t22.filetype = 'dxf'
GROUP BY t21.table1_id, t21.filetype, t22.table1_id, t22.filetype
HAVING COUNT(t21.filetype) = 2 AND COUNT(t22.filetype) = 4

编号 | cdr计数 | dxfCount
-: | --------: | --------:

db<>在这里摆弄