我有这样的数据:数据分为角色id,角色id 1为参与者角色id 2是求值者
每个评价者将得到相同数量的参与者
id | Name | roleid
-----------------
1 | a | 1 |
-------------------
2 | b | 1 |
-------------------
3 | c | 1 |
-------------------
4 | d | 1 |
-------------------
5 | e | 2 |
-------------------
6 | f | 2 |
如何基于roleid均匀匹配数据
所以我将这个数据插入到一个表中,我希望得到这样的结果:
id | participant | evaluator
-----------------------------
1 | 1 | 5 |
----------------------------
2 | 2 | 5 |
-----------------------------
3 | 3 | 6 |
-----------------------------
4 | 4 | 6 |
-----------------------------
如你所见,评价者id 5将得到参与者1 &2,评估者6将获得参与者3 &4,以此类推如果它有3个求值器那么它将被3除以
一种方法是选择具有row_number
的参与者和评价者,并根据评价者总数对每个参与者row_number
进行mod
计算,它会给你1,2…n-1,0,1,2…n-1,…,您可以将其与用于求值器
row_number
相匹配。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