mysql - 为每个值选择两行

我的桌子看起来像这样：

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

最佳答案

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

关于mysql - 为每个值选择两行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34674838/