MySQL按id在每个组中获取倒数第二行

Question

我正在尝试从每个分组中获取表中第二低的行。我已经得到了最低的，但现在我需要第二低的。抱歉我的英语不好

有人知道解决办法吗？

SELECT bestlellangaben 
FROM bestellangaben
JOIN
(
    SELECT MIN(ID) AS newid 
    FROM bestellangaben
    WHERE SeitenID = 185
    GROUP BY grouping
) AS temp ON ID = temp.newid

The Table

Answer 1

这很复杂吗？我真的不知道 - 但速度相当快......

DROP TABLE IF EXISTS bestellangaben;

CREATE TABLE bestellangaben
(ID INT NOT NULL PRIMARY KEY
,SeitenId INT NOT NULL
,bestlellangaben VARCHAR(50) NOT NULL
,grouping TINYINT NOT NULL
);

INSERT INTO bestellangaben VALUES
(863 ,185, "CAN I/O & CAN PLC",0),
(864 ,185, "6 analog inputs. 8 I/Os",0),
(865 ,185, "G7" ,0),
(866 ,185, "10333000E" ,0),
(867 ,185, "CAN I/O & CAN PLC - RPM" ,1),
(868 ,185, "6 analog inputs. 8 I/Os (2x frequency input)" ,1),
(869 ,185, "G7" ,1),
(870 ,185, "103130100E" ,1),
(871 ,185, "CAN I/O & CAN PLC - Current In" ,2),
(872 ,185, "6 analog inputs. 8 I/Os (3x current input)" ,2),
(873 ,185, "G7",2),
(874 ,185, "103130100E", 2),
(875 ,185, "CAN I/O & CAN PLC - PWM Out",3),
(876 ,185, "6 analog inputs. 8 I/Os (6x PWM)", 3),
(877 ,185, "G7" ,3);

SELECT a.id
     , a.seitenId
     , a.bestlellangaben
     , a.grouping
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev=grouping THEN @i:=@i+1 ELSE @i:=1 END rank
            , @prev:=grouping 
         FROM bestellangaben x
            , ( SELECT @prev:=null,@i:=0 ) vars 
        ORDER 
           BY grouping
            , id
     ) a
 WHERE rank = 2;

+-----+----------+----------------------------------------------+----------+
| id  | seitenId | bestlellangaben                              | grouping |
+-----+----------+----------------------------------------------+----------+
| 864 |      185 | 6 analog inputs. 8 I/Os                      |        0 |
| 868 |      185 | 6 analog inputs. 8 I/Os (2x frequency input) |        1 |
| 872 |      185 | 6 analog inputs. 8 I/Os (3x current input)   |        2 |
| 876 |      185 | 6 analog inputs. 8 I/Os (6x PWM)             |        3 |
+-----+----------+----------------------------------------------+----------+