时间戳

首页 > 解决方案 > MYSQL：选择前 N 个候选者

mysql - MYSQL：选择前 N 个候选者

问题描述

我有以下用于大学选举的数据库方案：

数据库方案

对于每个部门，我都有以下职位：

1名厨师（即candidate_position= 1）

& 6 个成员（即candidate_position= 2）

我想获得每个部门的选举获胜者。

为了获得“Informatique”部门的CHEF职位获得者，我做了以下查询：

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职位获胜者的查询是正确的，但我需要一些帮助以了解如何选择Member职位的前 6 名候选人？

数据集：

--
-- 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 演示

标签： mysqlsqlcountmaxinner-join

解决方案

考虑以下：

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代表排名。对于位置 1，我们可以看到两个候选人并列第一。对于第 2 名，有一位完全获胜，其余所有候选人并列第二名。

推荐阅读