使用表联接检索可变数量的行

Question

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

相同的表结构和问题，除了这一次假设past_election表现在设置为...

| 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语句一起使用，但变量在limits中不起作用。我也尝试过使用ROW_NUMBER() (我没有使用MySQL 8.0，所以它不能工作，但如果它能工作，我愿意升级)，或者像@row_number :=这样的相关变通方法，如果...然后根据行号进行过滤，但都不起作用。

上次尝试的查询：

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小提琴站点都支持v8。

percent 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`

需要先计算后再过滤

Answer 2

我没有列出正确的字段，但这是我今晚可能得到的最接近的字段……我已经获得了所需的行，但需要连接候选表以获得名称...

使用Dense_Rank似乎可以解决这个问题……

SELECT * FROM (
  SELECT pec.PastElection,
         c.FirstName,
         c.LastName,
         pec.Votes,
         pecs.totalVotes,
         pe.Seats as s,
         DENSE_RANK() OVER(PARTITION BY PastElection ORDER BY Votes DESC) as rank_votes
  FROM `past_elections-candidates` pec
  JOIN (SELECT PastElection,
               Max(Votes) as maxVotes,
               Sum(Votes) as totalVotes
        FROM `past_elections-candidates`
        GROUP BY PastElection) pecs ON pecs.PastElection = pec.PastElection
  JOIN `past_elections` pe ON pec.PastElection = pe.election_ID
  JOIN candidates c ON c.Candidate_ID = pec.Candidate
) t WHERE rank_votes <= s;

这会导致

| PastElection | FirstName | LastName | Votes | totalVotes | s | rank_votes |
|--------------|-----------|----------|-------|------------|---|------------|
| 1            | Aladdin   | Arabia   | 200   | 350        | 1 | 1          |
| 2            | Robin     | Hood     | 150   | 350        | 2 | 1          |
| 2            | King      | Kong     | 100   | 350        | 2 | 2          |

我猜在数据中包含rank_votes和s列可能有点麻烦，但是如果它能得到我需要的结果，那就没问题了。