mysql - 返回具有特定条件的最早记录 MYSQL

Question

我正在尝试从与此示例类似的数据中获取最早的记录:

user_id Criteria_1  Criteria_2  Date
1           1           1       1/1/2015
1           0           1       2/1/2015
1           1           0       3/1/2015
2           0           0       1/1/2015
2           0           1       2/1/2015
2           1           0       3/1/2015

期望的结果:

user_id Criteria_1  Criteria_2  Date
1           1           1       1/1/2015
2           0           1       2/1/2015

a.) user_id 应该是唯一的b.) 应满足 Criteria_1 和 Criteria_2 中的一个或两个c.) 最早日期

Answer 1

这是 mysql 标签下最常见的问题，但是嘿，今天过得很慢......

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(user_id INT NOT NULL
,Criterion_1  TINYINT NOT NULL
,Criterion_2  TINYINT NOT NULL
,Date DATE NOT NULL
,PRIMARY KEY(user_id,date)
);

INSERT INTO my_table VALUES
(1,1,1,'2015-01-01'),
(1,0,1,'2015-01-02'),
(1,1,0,'2015-01-03'),
(2,0,0,'2015-01-01'),
(2,0,1,'2015-01-02'),
(2,1,0,'2015-01-03');

SELECT * FROM my_table;
+---------+-------------+-------------+------------+
| user_id | Criterion_1 | Criterion_2 | Date       |
+---------+-------------+-------------+------------+
|       1 |           1 |           1 | 2015-01-01 |
|       1 |           0 |           1 | 2015-01-02 |
|       1 |           1 |           0 | 2015-01-03 |
|       2 |           0 |           0 | 2015-01-01 |
|       2 |           0 |           1 | 2015-01-02 |
|       2 |           1 |           0 | 2015-01-03 |
+---------+-------------+-------------+------------+

SELECT user_id
     , MIN(date) min_date 
  FROM my_table 
 WHERE 1 IN (Criterion_1,Criterion_2)
 GROUP 
    BY user_id;
+---------+------------+
| user_id | min_date   |
+---------+------------+
|       1 | 2015-01-01 |
|       2 | 2015-01-02 |
+---------+------------+

SELECT a.* 
  FROM my_table a
  JOIN
     ( SELECT user_id
            , MIN(date) min_date 
         FROM my_table 
        WHERE 1 IN (Criterion_1,Criterion_2)
        GROUP 
           BY user_id
     ) b
    ON b.user_id = a.user_id 
   AND b.min_date = a.date;
+---------+-------------+-------------+------------+
| user_id | Criterion_1 | Criterion_2 | Date       |
+---------+-------------+-------------+------------+
|       1 |           1 |           1 | 2015-01-01 |
|       2 |           0 |           1 | 2015-01-02 |
+---------+-------------+-------------+------------+