mysql：选择前n个候选

jxct1oxe 于 2021-06-23 发布在 Mysql

关注(0)|答案(3)|浏览(322)

我有以下db大学选举计划：

对于每个部门，我都有以下职位：
1名厨师（这是 candidate_position = 1)
&6名成员（这是 candidate_position = 2)
我想在每个部门获得选举的获胜者。
为了获得“信息”部门厨师职位的优胜者，我做了以下查询：

SELECT doctor.firstname, doctor.lastname, votes

FROM (SELECT COUNT(*) AS votes FROM candidate_votes WHERE candidate_votes.candidate_position = 1 GROUP BY candidate_votes.candidate_id) AS votes, doctor 

INNER JOIN department_candidates ON department_candidates.doctor_id = doctor.id 

INNER JOIN department ON department.id = department_candidates.department_id AND department.name = 'Informatique' 

INNER JOIN candidate_votes ON candidate_votes.candidate_id = doctor.id AND candidate_votes.candidate_position = 1 

GROUP BY candidates_votes.candidate_id

请注意我没有使用 LIMIT 1 因为多个候选人之间的票数可能是平局（或平局）
根据结果，我认为我的查询选择的优胜者 Chef 这个职位是对的，但我想得到一些帮助，知道如何选择前6名候选人 Member 位置？
数据集：

--
-- Table structure for table `candidate_votes`
--

DROP TABLE IF EXISTS `candidate_votes`;
CREATE TABLE IF NOT EXISTS `candidate_votes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `candidate_id` int(11) NOT NULL,
  `voter_id` int(11) NOT NULL,
  `candidate_position` tinyint(1) NOT NULL COMMENT '1: chef, 2: member',
  `date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk-candidate_votes-voter_id` (`voter_id`),
  KEY `fk-candidate_votes-candidate_id_idx` (`candidate_id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `candidate_votes`
--

INSERT INTO `candidate_votes` (`id`, `candidate_id`, `voter_id`, `candidate_position`, `date`) VALUES
(24, 2, 1, 1, '2018-05-26'),
(25, 1, 1, 2, '2018-05-26'),
(26, 6, 1, 2, '2018-05-26'),
(27, 5, 1, 2, '2018-05-26'),
(28, 7, 1, 2, '2018-05-26'),
(29, 8, 1, 2, '2018-05-26'),
(30, 9, 1, 2, '2018-05-26'),
(31, 2, 2, 1, '2018-05-16'),
(32, 3, 7, 1, '2018-05-22'),
(33, 3, 8, 1, '2018-05-22'),
(34, 4, 6, 2, '2018-05-29'),
(35, 7, 6, 2, '2018-05-29');

-- --------------------------------------------------------

--
-- Table structure for table `department`
--

DROP TABLE IF EXISTS `department`;
CREATE TABLE IF NOT EXISTS `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `department-name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `department`
--

INSERT INTO `department` (`id`, `name`) VALUES
(1, 'Informatique'),
(2, 'Mathematique'),
(4, 'physique');

-- --------------------------------------------------------

--
-- Table structure for table `department_candidates`
--

DROP TABLE IF EXISTS `department_candidates`;
CREATE TABLE IF NOT EXISTS `department_candidates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `department_id` int(11) NOT NULL,
  `doctor_id` int(11) NOT NULL,
  `candidate_position` tinyint(1) NOT NULL COMMENT '1: chef, 2: member',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `department_candidates`
--

INSERT INTO `department_candidates` (`id`, `department_id`, `doctor_id`, `candidate_position`) VALUES
(5, 1, 3, 1),
(7, 1, 4, 2),
(8, 1, 1, 2),
(9, 1, 2, 1),
(10, 1, 6, 2),
(11, 1, 5, 2),
(12, 1, 7, 2),
(13, 1, 8, 2),
(14, 1, 9, 2);

-- --------------------------------------------------------

--
-- Table structure for table `doctor`
--

DROP TABLE IF EXISTS `doctor`;
CREATE TABLE IF NOT EXISTS `doctor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `department_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `doctor`
--

INSERT INTO `doctor` (`id`, `firstname`, `lastname`, `department_id`) VALUES
(1, 'doc1_fn', 'doc1_ln', 1),
(2, 'doc2_fn', 'doc2_ln', 1),
(3, 'doc3_fn', 'doc3_ln', 1),
(4, 'doc4_fn', 'doc4_ln', 1),
(5, 'doc5_fn', 'doc5_ln', 1),
(6, 'doc6_fn', 'doc6_ln', 1),
(7, 'doc7_fn', 'doc7_ln', 1),
(8, 'doc8_fn', 'doc8_ln', 1),
(9, 'doc9_fn', 'doc9_ln', 1);

-- --------------------------------------------------------

sqlfiddle演示

sql mysql count Max inner-join

来源：https://stackoverflow.com/questions/50548015/mysql-select-the-top-n-candidates

3条答案

按热度按时间

hjzp0vay1#

此查询将为您提供成为给定职位的获胜者所需的票数（注意，我已使用变量将其参数化）：

SET @position = 2;
SET @numwinners = 3;
SELECT @rank := @rank+1 AS rank, votes
FROM (SELECT COUNT(candidate_id) AS votes 
      FROM candidate_votes 
      WHERE candidate_position = @position
      GROUP BY candidate_id
      ORDER BY votes DESC) vr
JOIN (select @rank := 0) r
GROUP BY rank
HAVING rank = @numwinners

数据的输出 @position=2 以及 @numwinners=3 :

rank    votes
3       1

在计算出一个胜利者需要多少票之后，我们只需要找到所有拥有所需票数的候选人。由于结果是基于票数的，所以平局将自动处理。此查询将生成以下输出：

SET @position = 2;
SET @numwinners = 3;
SELECT doc.firstname, doc.lastname, v.votes
FROM department_candidates dc
JOIN department dept ON dept.id=dc.department_id AND dept.name='Informatique'
JOIN doctor doc ON doc.id=dc.doctor_id
JOIN (SELECT candidate_id AS cid, count(candidate_id) AS votes
      FROM candidate_votes
      WHERE candidate_position = @position
      GROUP BY cid) v
  ON v.cid=dc.id
WHERE v.votes >= (SELECT votes
                  FROM (SELECT @rank := @rank+1 AS rank, votes
                        FROM (SELECT COUNT(candidate_id) AS votes 
                              FROM candidate_votes 
                              WHERE candidate_position = @position
                              GROUP BY candidate_id
                              ORDER BY votes DESC) vr
                        JOIN (select @rank := 0) r
                        GROUP BY rank
                        HAVING rank = @numwinners
                       ) vt
                  )
ORDER BY v.votes DESC

数据的输出 @position=2 以及 @numwinners=3 :

firstname   lastname    votes
doc7_fn     doc7_ln     2
doc4_fn     doc4_ln     1
doc1_fn     doc1_ln     1
doc6_fn     doc6_ln     1
doc5_fn     doc5_ln     1
doc8_fn     doc8_ln     1
doc9_fn     doc9_ln     1

数据的输出 @position=1 以及 @numwinners=1 :

firstname   lastname    votes
doc3_fn     doc3_ln     2
doc2_fn     doc2_ln     2

如果要确保即使 @numwinners 大于候选人数，更改：

HAVING rank = @numwinners

到

HAVING rank = LEAST(@numwinners, (SELECT COUNT(DISTINCT candidate_id)
                                  FROM candidate_votes
                                  WHERE candidate_position = @position))

演示

赞(0）回复(0）举报 2021-06-23

7xzttuei2#

显然，我试图在我的另一个答案太聪明，你可以实现一个简单的排名表如下：

SELECT cast(dc.candidate_position AS UNSIGNED) AS position, dc.doctor_id, doc.firstname, doc.lastname, v.votes
FROM department_candidates dc
JOIN department dept ON dept.id=dc.department_id AND dept.name='Informatique'
JOIN doctor doc ON doc.id=dc.doctor_id
JOIN (SELECT candidate_position AS cp, candidate_id AS cid, count(candidate_id) AS votes
      FROM candidate_votes
      GROUP BY cid) v
  ON v.cid=doc.id AND v.cp = dc.candidate_position
ORDER BY position, v.votes DESC

输出：

position    doctor_id   firstname   lastname    votes
1           3           doc3_fn     doc3_ln     2
1           2           doc2_fn     doc2_ln     2

2           7           doc7_fn     doc7_ln     2
2           4           doc4_fn     doc4_ln     1
2           1           doc1_fn     doc1_ln     1
2           6           doc6_fn     doc6_ln     1
2           5           doc5_fn     doc5_ln     1
2           8           doc8_fn     doc8_ln     1
2           9           doc9_fn     doc9_ln     1

演示

赞(0）回复(0）举报 2021-06-23

chhkpiq43#

考虑以下几点：

SELECT x.*
     , CASE WHEN @prev_position = candidate_position THEN CASE WHEN @prev_total = total THEN @i:=@i ELSE @i:=@i+1 END ELSE @i:=1 END i
     , @prev_position := candidate_position prev_position
     , @prev_total := total prev_total
  FROM
     (
SELECT candidate_id
     , candidate_position
     , COUNT(*) total 
  FROM candidate_votes  
 GROUP 
    BY candidate_id
     , candidate_position
     ) x
  JOIN 
     ( SELECT @prev_position := null,@prev_total:=null,@i:=0) vars
 ORDER
    BY candidate_position
     , total DESC;

+--------------+--------------------+-------+------+---------------+------------+
| candidate_id | candidate_position | total | i    | prev_position | prev_total |
+--------------+--------------------+-------+------+---------------+------------+
|            2 |                  1 |     2 |    1 |             1 |          2 |
|            3 |                  1 |     2 |    1 |             1 |          2 |

|            7 |                  2 |     2 |    1 |             2 |          2 |

|            8 |                  2 |     1 |    2 |             2 |          1 |
|            9 |                  2 |     1 |    2 |             2 |          1 |
|            1 |                  2 |     1 |    2 |             2 |          1 |
|            4 |                  2 |     1 |    2 |             2 |          1 |
|            5 |                  2 |     1 |    2 |             2 |          1 |
|            6 |                  2 |     1 |    2 |             2 |          1 |
+--------------+--------------------+-------+------+---------------+------------+

在这个例子中， i 代表等级。对于第一位，我们可以看到两位候选人并列第一。对于第二位，有一位完全胜出，其余所有候选人并列第二。

赞(0）回复(0）举报 2021-06-23

我来回答

mysql：选择前n个候选

3条答案

相关问题

热门标签

最新问答