209.12.3.LEETCODE SQL

614 二级关注者

这道题很坑爹，一直不知道哪里错了
然后看评论里有重复项要加DISTINCT

select 
f1.follower,count(distinct(f2.follower)) num
from
follow f1,follow f2
where
f1.follower=f2.followee
group by
f1.follower

1045 买下所有产品的客户

我一直开始太天真没有想到重复购买的问题…

SELECT c.customer_id
From Customer c
Join Product p
On c.product_key = p.product_key
Group by c.customer_id
Having count(c.customer_id) = (
    Select count(distinct Product.product_key) 
    From Product
)

后来改进版

Select c.customer_id
From Customer c
Group by c.customer_id
Having count(distinct c.product_key) =  (
    Select count(distinct Product.product_key) 
    From Product
)

1070 产品销售分析

为什么这个不行！！！
我编译通过了，小规模实验也通过了呀。。。

Select distinct s.product_id,s.year as first_year,s.quantity,s.price
From Sales s 
Group by s.product_id
Having min(year)

这是借鉴写法。。。还是不懂，郁闷

select product_id, year as first_year, quantity,price
from Sales
where (product_id , year) in(
select product_id ,min(year)from Sales
group by product_id )

作者：nai-7
链接：https://leetcode-cn.com/problems/product-sales-analysis-iii/solution/zhi-xing-yong-shi-1163-ms-zai-suo-you-mysql-ti-jia/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

1077 项目员工

我好困惑，因为第一个项目经验最丰富的有两个人
MAX 显示的只有一个

Select s.project_id,s.employee_id
From (
Select p.project_id,p.employee_id,e.experience_years
From Project p 
Join Employee e 
on e.employee_id = p.employee_id
) s 
group by s.project_id
Having max(s.experience_years)

改进了通过编译，测试正确，但是还是不能同通过

# Write your MySQL query statement below
Select s.project_id,s.employee_id
From (
Select p.project_id,p.employee_id,e.experience_years
From Project p 
Join Employee e 
on e.employee_id = p.employee_id
) s 
Where s.experience_years in (
SELECT MAX(s2.experience_years)
    From (
        Select p.project_id,p.employee_id,e.experience_years
        From Project p 
        Join Employee e 
        on e.employee_id = p.employee_id
        Group by p.project_id ) s2)

为什么他就通过了了呢。。。玄学，感觉明明一样的

select project_id,project.employee_id
from project join employee on project.employee_id=employee.employee_id
where (project_id,experience_years) in(
    select project_id,max(experience_years)
    from project join employee on project.employee_id=employee.employee_id
    group by project_id
)

作者：deathknight
链接：https://leetcode-cn.com/problems/project-employees-iii/solution/mysql-beats-9888-by-deathknight/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

1098 小众书籍

第一次提交答案不对
哪里错了呢…

Select s.book_id,s.name 
From (
Select Orders.book_id,sum(Orders.quantity) as quantity,Books.name
from Orders 
LEFT Join Books
on Books.book_id = Orders.book_id 
Where datediff(Books.available_from,'2019-06-23') <= 30
GROUP BY book_id
 ) s 
 Where s.quantity <10

没有来得及测试，LEETCODE server error

# Write your MySQL query statement below
#Select s.book_id,s.name 
#From (
Select Orders.book_id,sum(Orders.quantity) as quantity,Books.name
from Orders 
OUTER LEFT Join Books
on Books.book_id = Orders.book_id 
#Where datediff('2019-06-23',Books.available_from) > 30
GROUP BY book_id
# ) s 
#Where s.quantity <10