主页 / dba / 问题 / 175358
Accepted
Billy Watsy
Asked: 2017-06-04 22:23:42 +0800 CST

MYSQL 根据同一列中的条件进行计数

  • 772

我有以下数据样本

CREATE TABLE IF NOT EXISTS `tbl_result` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` varchar(10) NOT NULL,
  `subject_id` int(5) NOT NULL,
  `score` int(2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;


INSERT INTO `tbl_result` (`id`, `student_id`, `subject_id`, `score`) VALUES
(3, 'R135722F', 7112, 43),
(4, 'R135722F', 6118, 55),
(5, 'R135722F', 2076, 45),
(6, 'R135722F', 4402, 76),
(7, 'R135722F', 2234, 34),
(8, 'R134567Y', 6118, 65),
(9, 'R134567Y', 2076, 87),
(10, 'R134567Y', 4402, 43),
(11, 'R134567Y', 2234, 65),
(12, 'R134567Y', 1111, 34),
(13, 'R134567Y', 3454, 65),
(14, 'R134567Y', 3456, 87),
(15, 'R137634H', 7112, 98),
(16, 'R137634H', 6118, 54),
(17, 'R137634H', 2076, 45),
(18, 'R137634H', 4402, 33),
(19, 'R137634H', 2234, 65),
(20, 'R137634H', 2234, 65),
(21, 'R137634H', 1111, 12),
(22, 'R137634H', 3454, 54);

我如何检索以下有多少学生通过了以下科目即

科目代码7112、6118等3科或科目及格者 科目代码1111、3454等
4科及以上者

我曾尝试使用 in 函数，但没有产生预期的结果

    SELECT COUNT( * ) 
FROM  `tbl_result` 
WHERE  `subject_id` 
IN (
 '7112',  '6118'
)

和以下但失败了。也试过这个

SELECT * 
FROM  `tbl_result` 
WHERE  `subject_id` =  '7112'
OR  `subject_id` =  '6118'
AND  `score` >49
GROUP BY  `student_id
mysql

3 个回答

  1. 至于您没有向我们展示您想要的结果，让我展示一些解决您问题的方法。

    你的第一个例子：

    SELECT COUNT(student_id) count
FROM   tbl_result
WHERE  subject_id IN ('7112',  '6118');

    给出结果 5，使用COUNT(*)or都没关系COUNT(student_id)。

    | count |
|:-----:|
|   5   |

    如果你想知道哪些学生那么你可以使用GROUP BYstudent_id，但是你应该添加student_id到SELECT语句中：

    SELECT subject_id, COUNT(id) count
FROM   tbl_result
WHERE  subject_id IN ('7112',  '6118')
GROUP BY subject_id;

    结果：

    | subject_id | count |
|:----------:|:-----:|
|    6118    |   3   |
|    7112    |   2   |

    对于第二个，使用相同的语法添加更多条件：

    SELECT subject_id, COUNT(id) count
FROM   tbl_result
WHERE  subject_id IN ('7112',  '6118')
AND    score > 49
GROUP BY subject_id;

    结果：

    | subject_id | count |
|:----------:|:-----:|
|    6118    |   3   |
|    7112    |   1   |

SELECT COUNT(*) count
FROM   tbl_result
WHERE  subject_id IN ('7112',  '6118')
AND    score > 49;

| count |
|:-----:|
|   4   |

    此处为 Rextester

    • 1
  2. Best Answer

    对您的问题有不止一种可能的解释，并且没有迹象表明哪一种是“及格分数”。我以为是50

    第一个问题

    第一解读

     -- Those who passed 3 or subjects including subject code 7112 , 6118
 -- My interpretation:
 --    student passed 3 or more subjects
 --    student passed *both* subject 7112 and 6118
 SELECT DISTINCT
      student_id
 FROM 
      tbl_result
 WHERE
     student_id IN
     (
         SELECT student_id 
         FROM tbl_result
         WHERE score >= 50  -- I assume this is how you check student passed
         GROUP BY student_id
         HAVING count(*) >= 3
     )
     AND student_id IN
     (
        SELECT student_id 
        FROM tbl_result
        WHERE subject_id = 7112 AND score >= 50
     )
     AND student_id IN
     (
        SELECT student_id 
        FROM tbl_result
        WHERE subject_id = 6118 AND score >= 50
     ) ;


    | 学生编号 |
 | :-------- |
 | R137634H |

    二次解读

     -- Those who passed 3 or subjects including subject code 7112 , 6118
 -- Second interpretation:
 --    student passed 3 or more subjects
 --    student passed *either* subject 7112 or 6118
 SELECT DISTINCT
      student_id
 FROM 
      tbl_result
 WHERE
     student_id IN
     (
         SELECT student_id 
         FROM tbl_result
         WHERE score >= 50
         GROUP BY student_id
         HAVING count(*) >= 3
     )
     AND 
     student_id IN
     (
        SELECT student_id 
        FROM tbl_result
        WHERE subject_id IN (7112, 6118) AND score >= 50
     ) ;


    | 学生编号 |
 | :-------- |
 | R134567Y |
 | R137634H |

    第二个问题

    第一解读

     -- Those who passed 4 or more including subject code 1111 , 3454
 --  First interpretation
 --    student passed 4 or more subjects
 --    student passed *both* subject 1111 and 3454
 SELECT DISTINCT
      student_id
 FROM 
      tbl_result
 WHERE
     student_id IN
     (
         SELECT student_id 
         FROM tbl_result
         WHERE score >= 50
         GROUP BY student_id
         HAVING count(*) >= 4
     )
     AND student_id IN
     (
        SELECT student_id 
        FROM tbl_result
        WHERE subject_id = 1111 AND score >= 50
     )
     AND student_id IN
     (
        SELECT student_id 
        FROM tbl_result
        WHERE subject_id = 3454 AND score >= 50
     ) ;


    | 学生编号 |
 | :-------- |

    二次解读

     -- Those who passed 4 or more including subject code 1111 , 3454
 --  2nd interpretation
 --    student passed 4 or more subjects
 --    student passed *either* subject 1111 or 3454
 SELECT DISTINCT
      student_id
 FROM 
      tbl_result
 WHERE
     student_id IN
     (
         SELECT student_id 
         FROM tbl_result
         WHERE score >= 50
         GROUP BY student_id
         HAVING count(*) >= 4
     )
     AND student_id IN
     (
        SELECT student_id 
        FROM tbl_result
        WHERE subject_id IN (1111, 3454) AND score >= 50
     ) ;


    | 学生编号 |
 | :-------- |
 | R134567Y |
 | R137634H |

    只是算数

    如果您不想要所有学生的列表，请SELECT DISTINCT student_id通过以下方式更改所有查询SELECT count(DISTINCT student_id)：

     SELECT 
      count(DISTINCT student_id) AS my_count
 FROM 
      tbl_result
 WHERE
     student_id IN
     (
         SELECT student_id 
         FROM tbl_result
         WHERE score >= 50
         GROUP BY student_id
         HAVING count(*) >= 4
     )
     AND student_id IN
     (
        SELECT student_id 
        FROM tbl_result
        WHERE subject_id IN (1111, 3454) AND score >= 50
     ) ;


    | 我的计数 |
 | ------: |
 | 2 |

    dbfiddle在这里

    • 0

  3. 对于“那些通过了 3 个或科目，包括科目代码 7112、6118 的人”，我假设您的意思是他们应该同时通过 7112 和 6118 以及至少 1 个其他科目，对吗？同时通过 7112 , 6118 的学生是：

    select student_id 
from tbl_result 
where score > 49 
  and subject_id in (7112 , 6118) 
group by student_id 
having count(distinct subject_id = 2); -- means that both subjects are passed

    至少通过 7112 , 6118 旁边科目的学生是

    select student_id 
from tbl_result 
where score > 49 
  and subject_id not in (7112 , 6118) 
group by student_id 
having count(distinct subject_id > 0);

    最后一个可以简化为：

    select distinct student_id      
from tbl_result      
where score > 49        
  and subject_id not in (7112 , 6118);

    通过取这两组之间的交集，最终目标就实现了。不幸的是 intersect 似乎没有在 mysql 中实现。我们可以使用EXISTS谓词来处理这个问题，但我将展示另一种可能性：

    select student_id
from tbl_result
where score > 49 
group by student_id 
having count(distinct case when subject_id in ('7112',  '6118') 
                      then subject_id end) = 2 
   and count(distinct case when subject_id not in ('7112', '6118')
                      then subject_id end) > 0;
    • 0

相关问题