mysql - 使用表连接检索可变数量的行

Question

这是我在这里提出的另一个问题的复杂性的额外层:Using GROUP BY and ORDER BY in same MySQL query

相同的表结构和问题，只不过这次假设过去的选举表现在设置为...

| election_ID | Date       | jurisdiction   | Race          | Seats |
|-------------|------------|----------------|---------------|-------|
| 1           | 2016-11-08 | federal        | president     | 1     |
| 2           | 2016-11-08 | state_district | state senator | 2     |

(最后一条记录的席位设置为 2 而不是 1。)

我想使用席位数来获取每个组的不同数量的记录，并按票数排序。因此，在这种情况下，使用以下附加表...

候选人

| Candidate_ID | FirstName | LastName | MiddleName |
|--------------|-----------|----------|------------|
| 1            | Aladdin   | Arabia   | A.         |
| 2            | Long      | Silver   | John       |
| 3            | Thor      | Odinson  | NULL       |
| 4            | Baba      | Yaga     | NULL       |
| 5            | Robin     | Hood     | Locksley   |
| 6            | Sherlock  | Holmes   | J.         |
| 7            | King      | Kong     | Null       |

过去的选举候选人

| ID | PastElection | Candidate | Votes |
|----|--------------|-----------|-------|
| 1  | 1            | 1         | 200   |
| 2  | 1            | 2         | 100   |
| 3  | 1            | 6         | 50    |
| 4  | 2            | 3         | 75    |
| 5  | 2            | 4         | 25    |
| 6  | 2            | 5         | 150   |
| 7  | 2            | 7         | 100   |

我期望以下输出:

| election_ID | FirstName | LastName | votes | percent |
|-------------|-----------|----------|-------|---------|
| 1           | Aladdin   | Arabia   | 200   | 0.5714  |
| 2           | Robin     | Hood     | 150   | 0.4286  |
| 2           | King      | Kong     | 100   | 0.2857  |

我尝试设置一个变量并将其与 LIMIT 语句一起使用，但变量在限制内不起作用。我也尝试过使用 ROW_NUMBER() (我没有使用 MySQL 8.0，所以这行不通，但如果可以的话我愿意升级)或类似 @row_number := IF ... 的相关解决方法根据行号进行过滤，但没有任何效果。

上次尝试的查询:

SELECT pe.election_ID as elec,
       pe.Seats as s,
       pecs.row_num,
       c.FirstName,
       c.LastName,
       pecs.max_votes AS votes,
       pecs.max_votes / pecs.total_votes AS percent
FROM past_elections pe
JOIN `past_elections-candidates` pec ON pec.PastElection = pe.election_ID
JOIN (SELECT PastElection, 
             Candidate,
             @row_num := IF(PastElection = @current_election, @current_election + 1, 1) as row_num,
             MAX(Votes) AS max_votes,
             SUM(Votes) AS total_votes,
             @current_election := PastElection
      FROM `past_elections-candidates`
      GROUP BY PastElection) pecs ON pecs.PastElection = pec.PastElection AND pecs.row_num <= pe.Seats
JOIN candidates c ON c.Candidate_ID = pec.Candidate

Answer 1

无论如何都使用 MySQL 8 ;)

使用 ROW_NUMBER 对过去的选举进行排序:

SELECT *, ROW_NUMBER() OVER(PARTITION BY pastelection ORDER BY votes DESC) as rown 
FROM `past_elections-candidates`

将其作为子查询加入到 Past_elections (这只是您坚持“使用 pe.seats 改变每次选举返回的行数”的部分，不包括百分比位:

SELECT * 
FROM
  past_elections pe 
  INNER JOIN
  (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY pastelection ORDER BY votes DESC) as rown 
    FROM `past_elections-candidates`
  ) pecr 
  ON pecr.pastelection = pe.electionid AND
     pecr.rown <= pe.seats 

如果您想在升级之前在 8 上进行测试，大量 db fiddle 站点都支持 v8

附: %-y 的内容可以与 ROW_NUMBER 同时完成，例如:

votes/SUM(votes) OVER(PARTITION BY past_election)

例如，对于选举 ID 1，总和将为 200+100+50，即 200/350 = ~57%

SELECT *, votes/SUM(votes) OVER(PARTITION BY past_election) as pcnt, ROW_NUMBER() OVER(PARTITION BY pastelection ORDER BY votes DESC) as rown 
FROM `past_elections-candidates`

过滤前需要计算