题目1:
编写一个SQL查询来重新排列Products表,以便每行都有(product\ id、store、price)。如果某个产品在商店中不可用,请不要在结果表中包含具有该产品标识和商店组合的行。
解题思路:这是一个行列转换,在做这道题之前我们需要学习union和union all的操作。
(1) UNION
UNION操作符用于合并两个或多个SELECT语句的结果集。
注:UNION内部的SELECT语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条SELECT语句中的列的顺序必须相同.
UNION语法:
SELECT column name(s)FROM table namel
UNION
SELECT column_name(s)FROM table name2
注:默认地,UNION操作符选取不同的值。如果允许重复的值,请使用UNION ALL
UNION ALL语法:
SELECT column name(s)FROM table namei
UNION ALL
SELECT column_name(s)FROM table name2
注:UNION结果集中的列名总是等于UNION中第一个SELECT语句中的列名。
因此这道题的是实现代码如下:
select product_id, "store1" as store, store1 as price
from Products
where store1 is not null
union
select product_id, "store2" as store, store2 as price
from Products
where store2 is not null
union
select product_id, "store3" as store, store3 as price
from Products
where store3 is not null;
结果:
题目2:
编写一个 SQL 查询,按产品 id product_id 来统计每个产品的销售总量。
解题思路:这是一个分组求和连接的问题。
代码实现:
select s.product_id as product_id ,sum(s.quantity) as total_quantity
from Sales s
left join Product p
on s.product_id =p.product_id
group by s.product_id ;
结果:
题目3:
请你编写一个 SQL 查询,来查找这些顾客的 ID ,以及他们只光顾不交易的次数。
解题思路:
(1)连接,以visit-id为准则选择visits表建立left join保留所有visit-id
(2)visits表中同一个customer每次visit都会有新的visit-id,则需要count customer-id而不是visit-id。
(3)找no-transaction的则用where或者having建立筛选条件
代码实现:
select v.customer_id, count(*) count_no_trans
from visits v
left join transactions t
on v.visit_id = t.visit_id
where t.visit_id is null
group by v.customer_id;
结果:
题目4:
编写一个 SQL 查询,以求得每个员工所在团队的总人数。
解题思路:
(1)字段获取
结果需要两个字段,employee_id已有,team_size,除了group by,字段的分组count还可以用自连接
(2)需求表创建
除了方法一种通过count(*)创建的一个新表,还有一种方法是自连接,发现很多时候用自连接会比较简洁。
核心还是通过team_id连接,只不过计算每次where条件下,每个team_id数值下,对应的count数量是多少。
select employee_id,(
select count(*)
from employee e2
where e1.team_id = e2.team_id)as team_size
from employee e1
结果: