时间戳

首页 > 解决方案 > MYSQL 单查询获取 COUNT(DISTINCT columnName) 与 group by 和 order by

java - MYSQL 单查询获取 COUNT(DISTINCT columnName) 与 group by 和 order by

问题描述

我有一个实体类

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
}

我想按顺序* , COUNT(DISTINCT userId)分组projectIdeventDate

我尝试了以下查询

  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;它没有给我正确的结果。

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

标签： javamysql

解决方案

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

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

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

推荐阅读