how to pairing data from 1 source database query myql-CodePudding

I have data like this : with the data divided into role id , role id 1 is the participant and role id 2 is the evaluator,

Each evaluator will get the same number of participants

id | Name | roleid
-----------------
 1 |  a   |  1   |
-------------------
 2 |  b   |  1   |
-------------------
 3 |  c   |  1   |
-------------------
 4 |  d   |  1   |
-------------------
 5 |  e   |  2  |
-------------------
 6 |  f   |  2  |

how to evenly match the data based on the roleid

so I will insert this data into a table and I expect results like this :

   id | participant | evaluator
  -----------------------------
   1  |  1          |  5   |
  ----------------------------
   2  |  2          |  5   |
  -----------------------------
   3  |  3         |  6   |
  -----------------------------
   4  |  4         |  6   |
  -----------------------------

as you can see , evaluator id 5 will get participants 1 & 2 , while evaluator 6 will get participants 3 & 4 , and so on if it has 3 evaluator then it will be divided by 3.

CodePudding user response：

An approach is to select participants and evaluators with row_number, and do mod calculation for each participant row_number against the total number of evaluators, it gives you 1,2...n-1,0,1,2...n-1,..., which you can match against the row_number for evaluators

http://sqlfiddle.com/#!9/9f71cb/3

CREATE TABLE users (
  id INT,
  name VARCHAR(10),
  roleid INT
);

INSERT INTO users VALUES ( 1, 'a', 1 );
INSERT INTO users VALUES ( 2, 'b', 1 );
INSERT INTO users VALUES ( 3, 'c', 1 );
INSERT INTO users VALUES ( 4, 'd', 1 );
INSERT INTO users VALUES ( 5, 'e', 2 );
INSERT INTO users VALUES ( 6, 'f', 2 );

SELECT a.id participant, b.id evaluator
FROM (
  SELECT id, @RN := @RN   1 AS RN
  FROM users
  JOIN (SELECT @RN := 0) AS r
  WHERE roleid = 1
) a
JOIN (
  SELECT id, @RN2 := @RN2   1 AS RN
  FROM users
  JOIN (SELECT @RN2 := 0) AS r
  WHERE roleid = 2
) b
JOIN (
  SELECT COUNT(*) AS counts
  FROM users
  WHERE roleid = 2
) c
WHERE a.RN % c.counts = b.RN - 1