sql基础day01

sql基础day01

1、1699两人之间的通话次数

示例

难点

怎么找出表中相互通话的数据。

解题思路

分析

表中的数据无非就是由from_id > to_id 和 from_id < to_id的两类数据构成，最终结果只是对一方进行统计，所以可以让上述两种情况的数据统一使用较小的一列数据作为person1；然后再对合并之后的数据进行分组计算。

方式：使用 UNION ALL

答案

SELECT tmp.person1, tmp.person2, COUNT(1) call_count, SUM(duration) total_duration
FROM (
         SELECT from_id person1, to_id person2, duration
         FROM calls
         WHERE from_id < to_id
         UNION ALL
         SELECT to_id person1, from_id person2, duration
         FROM calls
         WHERE from_id > to_id
     ) tmp
GROUP BY tmp.person1, tmp.person2;

方式2：使用if()

select person1, person2, count(1) as call_count, sum(duration) as total_duration
from (
         select if(from_id > to_id, to_id, from_id) as person1,
                if(from_id > to_id, from_id, to_id) as person2, duration
         from Calls) c
group by person1, person2

2、1251. 平均售价

select u.product_id, round(sum(units * p.price) / sum(units), 2) as average_price
from unitssold u,
     prices p
where u.product_id = p.product_id
  and u.purchase_date between p.start_date and p.end_date
group by u.product_id;

3、1571. 仓库经理

select w.name "WAREHOUSE_NAME",sum(Width*Length*Height*w.units) "VOLUME"
from products p
         ,warehouse w where p.product_id = w.product_id
group by w.name;

4、1445. 苹果和桔子

题解一

select date1 as 'sale_date',(num1 - num2) as 'diff'
from (
         select s1.fruit fruit1,s1.sold_num num1,s1.sale_date date1,
                s2.sold_num num2
         from sales s1,
              sales s2
         where s1.sale_date = s2.sale_date
           and s1.fruit != s2.fruit
     ) t where t.fruit1 = 'apples';

题解二

SELECT sale_date, SUM(case when fruit='apples' then sold_num else -sold_num end) as diff
FROM Sales
GROUP BY sale_date
ORDER BY sale_date

5、176. 第二高的薪水

select ifNull((
select distinct Salary
from Employee
order by Salary desc limit 1,1),null) as SecondHighestSalary;