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

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

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。）最早的日期

最佳答案

这是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 |
+---------+-------------+-------------+------------+