我试图找到一个查询解决方案来查找一个表中出现次数最多的两个表中的项。

@Entity
@Table(name = "item")
public class Item extends AbstractEntity {
   // ID is added by AbstractEntity
}

@Entity
@Table(name = "itemCounter1")
public class ItemCounter1 extends AbstractEntity {
   private UUID itemId;
   private ZonedDateTime datetime;
}

@Entity
@Table(name = "itemCounter2")
public class ItemCounter2 extends AbstractEntity {
   private UUID itemId;
   private ZonedDateTime datetime;
}

我相信我的查询应该如下所示，但它还不正确，我有点困惑什么可能是最好的方法...甚至不确定我是否应该在查询中这样做，但如果能够将其交给数据库的力量，而不是需要编写几个单独的DB调用并合并结果，那肯定会很好。

public interface ItemRepository extends JpaRepository<Item, UUID> {
    @Query("""
        SELECT i
            FROM Item i 
                LEFT OUTER JOIN ItemCounter1 counter1 ON i.id = counter1.itemId
                LEFT OUTER JOIN ItemCounter2 counter2 ON i.id = counter2.itemId
            WHERE counter1.datetime >= :fromDate
                AND counter2.datetime >= :fromDate
            GROUP BY counter1.itemId, counter2.itemId
            ORDER BY (COUNT(counter1.itemId) + COUNT(counter2.itemId)) DESC
        """)
    Page<Item> findMostCounted(ZonedDateTime fromDate, PageRequest of);
}