mysql - 在mysql中按引用值划分行

Question

请帮帮我。

这是原始数据:

uid | id | value | 
1   |  a |   389 |
2   |  b |   201 |
3   |  c |   170 |

如果....当引用值为'200'时

你是如何让它变成这样的？

mysql..

no| uid | id | value | cut 
1 |  1  |  a |   200 | 200
2 |  1  |  a |   189 | 200
3 |  2  |  b |   200 | 200
4 |  2  |  b |     1 | 200
5 |  3  |  c |   170 | 200

帮帮我!!!

Answer 1

如果您使用的是 MySQL 8 或更高版本，递归 CTE 可以提供帮助:

CREATE DATABASE test;
USE test;
CREATE TABLE TestData (uid INTEGER, id VARCHAR(8), value INTEGER);
INSERT INTO TestData VALUES (1, 'a', 389);
INSERT INTO TestData VALUES (2, 'b', 201);
INSERT INTO TestData VALUES (3, 'c', 170);
INSERT INTO TestData VALUES (4, 'd', 550);

-- Set up an auto-incrementing row number
SET @row_num = 0;
WITH RECURSIVE cte (uid, id, value, remainder) AS (
  -- start with a copy of the table, but adding another column for the value that is at most 200
  SELECT a.uid, a.id, LEAST(a.value, 200), a.value AS "remainder" FROM TestData a
  UNION
  -- repeatedly select from the previous result set, meanwhile decrementing the "remainder" column
  SELECT uid, id, LEAST(remainder - 200, 200), remainder - 200 FROM cte WHERE remainder > 200
)
-- select the actual data that we care about
SELECT (@row_num := @row_num + 1) AS no, uid, id, value, 200 AS "cut" FROM cte ORDER BY id, value DESC;

结果如下表:

no | uid | id | value | cut
1  |  1  |  a |  200  | 200
2  |  1  |  a |  189  | 200
3  |  2  |  b |  200  | 200
4  |  2  |  b |    1  | 200
5  |  3  |  c |  170  | 200
6  |  4  |  d |  200  | 200
7  |  4  |  d |  200  | 200
8  |  4  |  d |  150  | 200