mysql - 为每个值选择两行

Question

我的表格如下所示:

ID - USER  - LOCATION
1  - Steve - home
2  - Steve - work
3  - Steve - school
4  - Eve   - home
5  - Eve   - work
6  - Eve   - school
7  - RJ    - school
etc.

我怎样才能从所有用户那里得到两个位置？

编辑

输出应该是这样的:

ID - USER  - LOCATION
1  - Steve - home
2  - Steve - work
4  - Eve   - home
5  - Eve   - work
7  - RJ    - school

Answer 1

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,USER  VARCHAR(12) NOT NULL
,LOCATION VARCHAR(12)
);

INSERT INTO my_table VALUES
(1  ,'Steve','home'),
(2  ,'Steve','work'),
(3  ,'Steve','school'),
(4  ,'Eve','home'),
(5  ,'Eve','work'),
(6  ,'Eve','school'),
(7  ,'RJ','school');

例如:

SELECT x.*
  FROM my_table x 
  JOIN my_table y 
    ON y.user = x.user 
   AND y.id <= x.id 
 GROUP 
    BY x.id 
HAVING COUNT(*) <=2;
+----+-------+----------+
| ID | USER  | LOCATION |
+----+-------+----------+
|  1 | Steve | home     |
|  2 | Steve | work     |
|  4 | Eve   | home     |
|  5 | Eve   | work     |
|  7 | RJ    | school   | 
+----+-------+----------+