做题记录-力扣 LeetCode-SQL_if(rating < 3, 1, 0)

select name,population,area
from World
where area>=3000000 or population >=25000000
 
------------------------
 
select name,population,area
from World
where area>=3000000
UNION
select name,population,area
from World
where population >=25000000

select product_id
from Products
where low_fats='Y' and recyclable ='Y'

select name
from customer
where referee_id!=2 or referee_id is null
 
--------------------------
 
select name
from customer
where referee_id<>2 or referee_id is null
 
--------------------------
 
#先把null转为0
 
select name
from customer
where ifnull(referee_id,0)!=2;

select customers.name as customers
from customers
where id not in 
    (select customers.id
    from customers,orders
    where customers.id=orders.customerid)
 
--------------------------
 
select Name as Customers
from Customers
where Id not in(select CustomerId
                from Orders)

select employee_id,salary as  bonus
from Employees
where (employee_id%2)!=0 and name not like "M%" 
union
select employee_id,salary=0 as  bonus
from Employees
where (employee_id%2)=0 or name like "M%" 
order by employee_id

update salary
set sex = 
(
    case sex when 'm' then 'f' else 'm' end
);

delete a
from Person a , Person b
where a.id > b.id and a.email = b.email

concat(upper(left(name,1)),lower(substr(name,2)))

select user_id,
        concat(upper(left(name,1)),lower(substr(name,2))) as name
from Users
order by user_id 

select sell_date,
        count(distinct(product)) as num_sold, 
        group_concat( distinct product separator ',') as products
from  Activities
group by sell_date
order by sell_date

select * 
from Patients
where conditions like "DIAB1%" or conditions like "% DIAB1%"

select employee_id from employees
where employee_id not in (select employee_id from salaries)
union
select employee_id from salaries
where employee_id not in (select employee_id from employees)
order by employee_id
 
------------------
 
select e.employee_id
from Employees e
left join Salaries s
on e.employee_id=s.employee_id 
where salary is null
union 
select s.employee_id
from Employees e
right join Salaries s
on e.employee_id=s.employee_id 
where name  is null
order by employee_id

select product_id,'store1' store, store1 as price from Products where store1 is not null
union all
select product_id,'store2' store, store2 as price from Products where store2 is not null
union all
select product_id,'store3' store, store3 as price from Products where store3 is not null

select id, 
    case 
        when  id = (select id from tree where p_id is null) then 'Root' 
        when id in (select p_id from tree) then 'Inner'
        else 'Leaf'
    end as Type
from tree
order by id
 
--------------------------
 
# 根节点：如果这个节点是整棵树的根，即没有父节点。
select id, 'Root'  as type from tree where p_id is null
union all 
# 内部节点：如果这个节点既不是叶子节点也不是根节点。
select id, 'Inner'  as type 
            from tree 
            where p_id is not null and
            id in (select  p_id from tree where p_id is not null) 
union all 
# 叶子节点：如果这个节点没有任何孩子节点。
select id, 'Leaf'  as type 
            from tree 
            where p_id is not null and
            id not in (select  p_id from tree where p_id is not null) 
order by id
 

select ifnull((select distinct salary
                from Employee
                order by salary desc
                limit 1 offset 1) ,null)
as SecondHighestSalary
 
----------------------
 
select ifnull((select distinct Salary 
                from Employee 
                order by Salary desc 
                limit 1,1),null) 
as SecondHighestSalary
 
----------------------
 
select b.salary as SecondHighestSalary
from Employee a
left join Employee b
on a.salary>b.salary
order by a.salary desc,b.salary desc
limit 1

select firstName,lastName,city,state  
from Person p
left join Address a 
on p.personId = a.personId 

select  customer_id,count(customer_id) as count_no_trans
from Visits
where visit_id not in (select visit_id from Transactions)
group by customer_id

select distinct author_id as id 
from Views
where author_id=viewer_id
order by id

#截至 2019-07-27（含）的 30 天内
datediff('2019-07-27',activity_date)<30
activity_date BETWEEN '2019-06-28' AND '2019-07-27'

select b.id as id
from Weather a,Weather b 
where datediff(b.recordDate, a.recordDate) = 1 and b.Temperature>a.Temperature

select name
from SalesPerson
where sales_id not in (select sales_id
                        from Company,Orders
                        where Orders.com_id = Company.com_id 
                        and Company.name ='RED')
 
-------------------------------------
 
select name
from SalesPerson
where  sales_id not in (select sales_id 
                        from Orders
                        where com_id =(select com_id
                                        from Company 
                                        where name='RED'))

select activity_date as day,count(distinct user_id) as active_users
from Activity
where activity_date between '2019-06-28' and '2019-07-27'  
group by  activity_date
 
-----------------------------
 
#注意datediff(expr1,expr2)的顺序，为expr1 − expr2
#数据中可能还有大于 7.27 号的数据，第十三组测试样例就有 2019-8-25 的数据，所以还要保证日期差大于等于0
 
select activity_date as day,count(distinct user_id) as active_users
from Activity
where datediff('2019-07-27',activity_date)<30 and datediff('2019-07-27',activity_date)>=0
group by  activity_date

select date_id,make_name,count(distinct lead_id) as  unique_leads,count(distinct partner_id) as  unique_partners
from DailySales
group by date_id, make_name

select user_id,count(distinct follower_id) as followers_count
from Followers
group by user_id
order by user_id

select  customer_number
from Orders
group by customer_number
order by count(order_number) desc 
limit 1

select  player_id,min(event_date) as first_login 
from Activity
group by player_id

select user_id,max(time_stamp) as last_stamp
from Logins 
where year(time_stamp)=2020
group by user_id
 
-------------------------------
 
select user_id,max(time_stamp) as last_stamp  
from Logins
where time_stamp between'2020-01-01 00:00:00' and '2020-12-31 23:59:59'
group by  user_id

select event_day as day,emp_id,sum(out_time-in_time) as total_time 
from Employees
group by emp_id,event_day

select stock_name,
sum(case when operation='buy' then -price
                              else  price  end ) as 'capital_gain_loss'
from Stocks
group by stock_name

select  name,sum(ifnull(distance,0)) as travelled_distance 
from Users
left join Rides
on Rides.user_id=Users.id 
group by user_id 
order by travelled_distance desc,name 

select u.user_id as buyer_id ,u.join_date,ifnull(num,0) as orders_in_2019
from Users u
left join (select buyer_id,count(buyer_id) as num
            from Orders 
            where year(order_date)=2019
            group by buyer_id) as b
on u. user_id=b.buyer_id

select email as Email 
from Person
group by email
having count(id)>1

select  actor_id,director_id 
from ActorDirector
group by actor_id,director_id
having count(timestamp)>=3

select name,sum(amount) as balance 
from Transactions t
left join Users u
on u.account = t.account
group by u.account
having balance >10000

select product_id,product_name
from Product
where product_id in(select product_id 
                    from Sales
                    where sale_date between '2019-01-01' and '2019-03-31')
and product_id not in(select product_id 
                    from Sales
                    where sale_date not between '2019-01-01' and '2019-03-31')
 
-----------------------------------
 
select p.product_id, product_name
from product p 
inner join sales s
on p.product_id = s.product_id
group by product_id
having min(sale_date) >= '2019-01-01' and max(sale_date) <= '2019-03-31';