我有一个POST表、一个CATEGORY表、一个CATEGORY_TYPE表和一个ASSIGNMENT表，我解释了ASSIGNMENT表包含每个类别到category_type的分配，例如ID = 4且category_id = 3的POST在2018 category_type_id = 1中具有最后一次分配，因此查询应获得特定年份的最后一次分配

//POST table
+----+-------------+
| id | category_id |
+----+-------------+
|  1 |      1      |
|  2 |      2      |
|  3 |      1      |
|  4 |      3      |
|  5 |      1      |
|  6 |      1      |
|  7 |      1      |
|  8 |      3      |
|  9 |      2      |
| 10 |      2      |
| 11 |      4      |
+----+-------------+

//CATEGORY table
+----+------------------+
| id |   category_name  |
+----+------------------+
|  1 |    category_1    |
|  2 |    category_2    |
|  3 |    category_3    |
|  4 |    category_4    |
|  5 |    category_5    |
+----+------------------+

//CATEGORY_TYPE table
+----+------------------+
| id |  category_type   |
+----+------------------+
|  1 |     type_1       |
|  2 |     type_2       |
|  3 |     type_3       |
+----+------------------+

//ASSIGNMENT table
+----+------------------+---------------------+--------------+
| id |   category_id    |   category_type_id  | Date         |
+----+------------------+---------------------+--------------+
|  1 |        1         |          3          |  2017-01-01  |
|  2 |        3         |          2          |  2017-11-10  |
|  3 |        1         |          2          |  2017-12-02  |
|  4 |        5         |          3          |  2018-01-01  |
|  5 |        2         |          1          |  2018-04-03  |
|  6 |        3         |          1          |  2018-05-06  |
|  7 |        2         |          2          |  2018-09-21  |
|  8 |        1         |          3          |  2018-11-01  |
|  9 |        4         |          2          |  2018-12-29  |
| 10 |        3         |          3          |  2019-02-16  |
| 11 |        5         |          1          |  2019-06-20  |
| 12 |        1         |          1          |  2019-12-01  |
+----+------------------+---------------------+--------------+

因此，我想计数后的类别_类型取决于最后一次分配的specifique年

select category_type, count(*) as cnt 
from(
select max(ctt.category_type) AS category_type
from post p 
        inner join category ct on (p.category_id = ct.id)
        left outer join (
            select category_id,
            max(category_type_id) keep (dense_rank last order by id) AS category_type_id
            from assignment ag
            where extract (year from ag.date) <= 2018
            group by category_id
            ) ag
        on (ct.id= ag.category_type_id)
        left join category_type ctt on ag.category_type_id = ctt.id      
        group by ctt.id
)
group by category_type;

遵循此处的逻辑是预期要选择的数据

+---------------+----------+
| category_type |    cnt   |
+---------------+----------+
|     type_1    |     2    |
|     type_2    |     4    |
|     type_3    |     5    |
+---------------+----------+

根据数据，cnt的总和= 11个帖子，这是正确的，但问题是查询没有返回正确的结果

+---------------+----------+
| category_type |    cnt   |
+---------------+----------+
|     type_1    |     4    |
|     type_2    |     6    |
|     type_3    |     8    |
+---------------+----------+

最好的问候