DataBase -- Count & Group by
SQL Count()函数:
- SQL COUNT(column_name)语法:COUNT(column_name)函数返回指定列的值得数目(NULL不计入)
select count(column_name) from table_name
- SQL COUNT(DISTINCT column_name)语法:COUNT(DISTINCT column_name)函数返回指定列的不同值得数目
select count(distinct column_name) from table_name
- SQL COUNT(*)语法:COUNT(*)函数返回表中的记录数
select count(*) from table_name
SQL GROUP BY 语句:
合计函数如SUM函数,常常需要添加GROUP BY语句。
语法:
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
实例:
Question:
Write a SQL query to find all duplicate emails in a table named Person
.
+----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+
For example, your query should return the following for the above table:
+---------+ | Email | +---------+ | a@b.com | +---------+
Note: All emails are in lowercase.
Analysis:
写一个SQL语句,找出Person表中所有重复的email
Answer:
select p1.Email from Person p1 group by p1.Email having count(*) > 1;