java - MYSQL单个查询获取COUNT(DISTINCT columnName)，带有group by和order by

Question

我有一个实体类

public class TeklaEventE {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;
    private Integer projectId;
    private Integer drawingId;
    private Integer userId;
    private String eventType;
    private LocalDateTime eventDate; 
    private LocalDateTime modifiedDate; 
    private String projectName;
    private String userName;
    private transient int noOfUser;    
    // getter setter
}

我想通过按 eventDate 排序对 projectId 进行分组来获取 * , COUNT(DISTINCT userId)

我尝试了以下查询

1. SELECT *,COUNT(DISTINCT userId) as noOfUser FROM teklaevent group by projectId order by eventDate desc;它给出了正确的计数，但它首先运行group by，然后运行order by，但我需要具有不同projectId的最新行。然后我按照建议尝试第二个查询

2. select * FROM TeklaEvent where id in (select max(id) FROM TeklaEvent group by projectId) order by eventDate desc; 它提供了我需要的完美行，因为 eventDate 始终是当前日期。但是当我尝试从下面的查询中获取 COUNT(DISTINCT userId) 时。

3. select *,COUNT(DISTINCT userId) as noOfUser FROM TeklaEvent where id in (select max(id) FROM TeklaEvent group by projectId) order by eventDate desc; 它无法给我正确结果。

如何在单个查询中执行此操作？

Answer 1

您可以在子选择上使用联接来进行计数

select a.*, t.noOfUser 
from  teklaevent
inner join (

  select projectId , count(distinct userId) noOfUser
  from  teklaevent
  group by projectId
) t a.projectId = t.projectId