mysql - 重复行的条件

Question

我有 4 列:

用户 ID、开始日期 (D/M/Y)、类型和名称

user_id | start_date | type | name
1       | 1-01-2017  | 0    | Jack
1       | 1-01-2017  | 1    | Jack
1       | 2-01-2017  | 0    | Jack 
1       | 3-01-2017  | 0    | Jack
1       | 4-01-2017  | 0    | Jack 
1       | 5-01-2017  | 0    | Jack

我想通过 id 、 type 和 start date 检索数据。但条件是，如果日期相同并且优先级是 type = 1 ，我只想要单个记录。不想要重复的日期。我已经尝试过 Group by 但它只显示类型为 0 的地方。

我想要像这样:

user_id | start_date | type | name
1       | 1-01-2017  | 1    | Jack
1       | 2-01-2017  | 0    | Jack 
1       | 3-01-2017  | 0    | Jack
1       | 4-01-2017  | 0    | Jack 
1       | 5-01-2017  | 0    | Jack

按结果分组:

user_id | start_date | type | name
1       | 1-01-2017  | 0    | Jack
1       | 2-01-2017  | 0    | Jack 
1       | 3-01-2017  | 0    | Jack
1       | 4-01-2017  | 0    | Jack 
1       | 5-01-2017  | 0    | Jack

Answer 1

我不明白你到底在找什么，但下面的查询将为你提供你正在寻找的确切结果:

SELECT MAX(user_id) AS user_id, 
  start_date,
  MAX(type) AS type, 
  MAX(name) as name
FROM data AS d1  
GROUP BY start_date;

Results :

| user_id |           start_date | type | name |
|---------|----------------------|------|------|
|       1 | 2017-01-01T00:00:00Z |    1 | Jack |
|       1 | 2017-02-01T00:00:00Z |    0 | Jack |
|       1 | 2017-03-01T00:00:00Z |    0 | Jack |
|       1 | 2017-04-01T00:00:00Z |    0 | Jack |
|       1 | 2017-05-01T00:00:00Z |    0 | Jack |