MySQL查询在两个单独的条件下选择一个计数?
为此使用 CASE 语句。让我们首先创建一个表 -
mysql> create table DemoTable-> (
-> StudentMarks int,
-> isValid tinyint(1)
-> );
使用插入命令在表中插入一些记录 -
mysql> insert into DemoTable values(45,0);mysql> insert into DemoTable values(78,1);
mysql> insert into DemoTable values(45,1);
mysql> insert into DemoTable values(78,1);
mysql> insert into DemoTable values(45,0);
mysql> insert into DemoTable values(82,1);
mysql> insert into DemoTable values(62,1);
使用 select 语句显示表中的所有记录 -
mysql> select *from DemoTable;输出结果
+--------------+---------+| StudentMarks | isValid |
+--------------+---------+
| 45 | 0 |
| 78 | 1 |
| 45 | 1 |
| 78 | 1 |
| 45 | 0 |
| 82 | 1 |
| 62 | 1 |
+--------------+---------+
7 rows in set (0.00 sec)
这是在两个不同条件下选择计数的查询 -
mysql> select StudentMarks,输出结果-> sum(case
-> when StudentMarks=45
-> then case when isValid = 1 then 1 else 0 end
-> else 1 end
-> ) AS Freq
-> from DemoTable
-> group by StudentMarks;
+--------------+------+| StudentMarks | Freq |
+--------------+------+
| 45 | 1 |
| 78 | 2 |
| 82 | 1 |
| 62 | 1 |
+--------------+------+
4 rows in set (0.00 sec)
以上是 MySQL查询在两个单独的条件下选择一个计数? 的全部内容, 来源链接: utcz.com/z/311386.html