day10 SQL基础-数值处理函数

1699. 两人之间的通话次数

编写 SQL 语句，查询每一对用户 ==(person1, person2) ==之间的通话次数和通话总时长，其中 person1 < person2 。

以 任意顺序 返回结果表。

查询结果格式如下示例所示。

方法一：
select
	t.person1,
	t.person2,
	count(t.person1) as call_count,
	sum(t.duration)  as total_duration
from 
	(
		select 
			if(c.from_id<c.to_id,c.from_id,c.to_id) as person1,
			if(c.from_id<c.to_id,c.to_id,c.from_id) as person2,
			c.duration
		from
			Calls as c
	) as t 
group by 
	t.person1,t.person2
方法二：
select 
	if(c.from_id<c.to_id,c.from_id,c.to_id) as person1,
	if(c.from_id<c.to_id,c.to_id,c.from_id) as person2,
    count(1) as call_count,
    sum(c.duration)  as total_duration
from
	Calls as c
group by 
	person1,person2

1251. 平均售价

编写SQL查询以查找每种产品的平均售价。
average_price 应该四舍五入到小数点后两位。
查询结果格式如下例所示：

select 
	p.product_id,
	round(sum(p.price * u.units)/sum(u.units),2) as average_price
from 
	Prices as p 
	inner join 
	UnitsSold as u 
	on u.purchase_date between p.start_date and p.end_date and p.product_id=u.product_id
group by 
	p.product_id

1571. 仓库经理

写一个 SQL 查询来报告, 每个仓库的存货量是多少立方英尺.

返回结果没有顺序要求.

查询结果如下例所示.

select 
	w.name as WAREHOUSE_NAME,
	sum(w.units * p.Width * p.Length * p.Height) as VOLUME
from 
	Warehouse as w left join Products as p on p.product_id=w.product_id
group by 
	w.name

1445. 苹果和桔子

写一个 SQL 查询, 报告每一天 苹果 和 桔子 销售的数目的差异.

返回的结果表, 按照格式为 (‘YYYY-MM-DD’) 的 sale_date 排序.

查询结果表如下例所示:

select 
	s.sale_date,
	# sum(case when s.fruit='apples' then s.sold_num else 0 end) - sum(case when s.fruit='oranges' then s.sold_num else 0 end) as diff
     SUM(case when fruit='apples' then sold_num else -sold_num end) as diff
from 
	Sales as s
group by 
	s.sale_date
order by
    s.sale_date