SQL刷题-3

贵公子范昨晚吃了凉的水果捞 腹泻一整晚酸爽无比

1刷题

2加跑通了基于ResNet模型的Caltech-101图像分类 的模型（一点点调试 但是还是不懂）

3英语听力

 

Leetcode607.销售员（简单）

SELECT * from salesperson  as sp
LEFT JOIN orders1 as o
on (sp.sales_id=o.sales_id)
LEFT JOIN company as c
on (c.com_id=o.com_id)
GROUP BY sp.sales_id
HAVING sum(if(c.`NAME`='red',1,0))=0

Leetcode610. 判断三角形（简单）

SELECT x,y,z,
if(x+y>z and x+z>y and y+z>x,'yes','no') as 'triangle'
from triangle

bit 限制0/1

Leetcode619.只出现一次的最大数字（简单）

SELECT MAX(num) from (
SELECT num   from number 
GROUP BY num 
 having count(*) =1
) as tmp

Leetcode1050.合作过至少三次的演员和导演（简单）

按actor_id, director_id 二值对进行分组,则可以按照两个数值相同为相同 ，统计超过3次的数量即可

SELECT actor_id,director_id 
from  ActorDirector 
GROUP BY actor_id , director_id 
HAVING count(* )>3

Leetcode1084. 销售分析III（简单）

-- 选出仅仅只在这个时间段出售的产品 有可能该产品在别的时间段出售过

笨方法
SELECT sales.product_id,Product .product_name  from sales left join  product on product.product_id=sales.product_id
 where sales.sale_date BETWEEN '2019-01-01' and '2019-03-31'
and sales.product_id not in (
SELECT sales.product_id  from sales left join 
product on product.product_id=sales.product_id
where sales.sale_date<'2019-01-01'or sales.sale_date> '2019-03-31'