Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies在MySQL中使用 CASE WHEN 語句執行計數？
為此，您可以使用 CASE WHEN 語句。我們先建立一個表 -
mysql> create table DemoTable1910 ( FirstName varchar(20), Marks int ); Query OK, 0 rows affected (0.00 sec)
使用 insert 命令在表中插入一些記錄 -
mysql> insert into DemoTable1910 values('Chris',45);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1910 values('David',85);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1910 values('Chris',55);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1910 values('Chris',98);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1910 values('David',98);
Query OK, 1 row affected (0.00 sec)使用 select 語句顯示錶中的所有記錄 -
mysql> select * from DemoTable1910;
這將產生以下輸出 -
+-----------+-------+ | FirstName | Marks | +-----------+-------+ | Chris | 45 | | David | 85 | | Chris | 55 | | Chris | 98 | | David | 98 | +-----------+-------+ 5 rows in set (0.00 sec)
以下是對列值進行有效計數的查詢 -
mysql> select FirstName, sum(case when Marks=45 then 1 else 0 end), sum(case when Marks=98 then 1 else 0 end) from DemoTable1910 group by FirstName;
這將產生以下輸出 -
+-----------+-------------------------------------------+-------------------------------------------+ | FirstName | sum(case when Marks=45 then 1 else 0 end) | sum(case when Marks=98 then 1 else 0 end) | +-----------+-------------------------------------------+-------------------------------------------+ | Chris | 1 | 1 | | David | 0 | 1 | +-----------+-------------------------------------------+-------------------------------------------+ 2 rows in set (0.00 sec)
更新於： 30-Dec-2019
505 次瀏覽
廣告