PROSAGA码农传奇-MySQL-MySQL排名前n的排名和其他同组的总和


    
      DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(ts INT NOT NULL
,name VARCHAR(12) NOT NULL
,count INT NOT NULL
,PRIMARY KEY(ts,name)
);
INSERT INTO my_table VALUES
(1552286160,’Apple’ , 7),
(1552286160,’Orange’, 8),
(1552286160,’Grape’ , 8),
(1552286160,’Pear’  , 9),
(1552286160,’Kiwi’  ,10),
(1552286100,’Apple’ ,10),
(1552286100,’Orange’,12),
(1552286100,’Grape’ ,14),
(1552286100,’Pear’  ,16),
(1552286100,’Kiwi’  , 9),
(1552286040,’Apple’ , 4),
(1552286040,’Orange’, 2),
(1552286040,’Grape’ , 3),
(1552286040,’Pear’  , 7),
(1552286040,’Kiwi’  , 9);
SELECT ts
     , CASE WHEN i>3 THEN ‘other’ ELSE name END name
     , SUM(count) count
  FROM
     ( SELECT x.*
            , CASE WHEN @prev=ts THEN @i:=@i+1 ELSE @i:=1 END i
            , @prev:=ts
         FROM my_table x
            , (SELECT @prev:=null,@i:=0) vars
        ORDER
           BY ts
            , count DESC
            , name
     ) a
 GROUP
    BY ts
     , CASE WHEN i>3 THEN ‘other’ ELSE name END;
+——————+————+———-+
| ts         | name   | count |
+——————+————+———-+
| 1552286040 | Apple  |     4 |
| 1552286040 | Kiwi   |     9 |
| 1552286040 | other  |     5 |
| 1552286040 | Pear   |     7 |
| 1552286100 | Grape  |    14 |
| 1552286100 | Orange |    12 |
| 1552286100 | other  |    19 |
| 1552286100 | Pear   |    16 |
| 1552286160 | Grape  |     8 |
| 1552286160 | Kiwi   |    10 |
| 1552286160 | other  |    15 |
| 1552286160 | Pear   |     9 |
+——————+————+———-+
</code>