数据库学习记录

SQL语句学习

函数

group by

select 
	from_id as person1, 
	to_id as person2, 
	count(duration) call_count, 
	sum(duration) total_duration from Calls 
group by least(from_id, to_id), greatest(from_id, to_id);

least

求最小值：least()函数返回在该组输入参数(N1，N2，N3，等等)的最小值

greatest

求最大值：greatest()函数返回在该组输入参数(N1，N2，N3，等等)的最大值

count

统计次数：统计某个字段的出现次数

sum

求和： 对某个字段求和
可以通过sum配合if来判断某个字段的出现次数。如下，统计表中product_name出现过的次数

sum(if(product_name = 'A', 1, 0)) 

sum和count的区别

count() does not care about the content inside it, as long as the content is NOT NULL. Because count(1) and count(0) are still values and both get counted.
count 只关心当下的括号内的值是不是null，不关心是0还是1，0和1都一样都不是null。
sum是会关心是0还是1，因为需要做累加。
以下三种写法均可以：

#统计表中 order_date 和 customer_pref_delivery_date相同的条数

sum(if(order_date = customer_pref_delivery_date, 1, 0))
count(case when order_date = customer_pref_delivery_date then 1 else NULL end)
count(if(order_date = customer_pref_delivery_date, 1, NULL)
</