LeetCode(sql)-0723

聚合函数

620

select * 
from cinema
where mod(id,2)=1 and description <> 'boring'
order by rating desc

1251

select p.product_id,
Round(sum(price*units)/sum(units),2)as average_price
from UnitsSold u left join Prices p
using(product_id)
where purchase_date <= end_date and purchase_date >= start_date
group by product_id

1075

round 函数保留小数位

select project_id,
Round(sum(experience_years)/count(*),2) as average_years
from Project left join Employee
using(employee_id)
group by project_id

排序和分组

2356

select teacher_id,count(DISTINCT subject_id)as cnt
from Teacher
group by teacher_id 

1141

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

1084

SELECT product_id, product_name
FROM Product
WHERE product_id NOT IN (SELECT product_idFROM SalesWHERE '2019-01-01' > sale_date OR sale_date > '2019-03-31'
)
AND product_id IN (SELECT product_idFROM SalesWHERE '2019-01-01' <= sale_date AND sale_date <= '2019-03-31'
);

高级查询和连接

1731

SELECT employee_id, name, reports_count, average_age
FROM (SELECT reports_to,COUNT(DISTINCT employee_id) AS reports_count,ROUND(AVG(age), 0) AS average_ageFROM EmployeesWHERE reports_to IS NOT NULLGROUP BY reports_to
) r
LEFT JOIN Employees e ON e.employee_id = r.reports_to;

子查询

1978

SELECT employee_id
FROM Employees
WHERE manager_id NOT IN (SELECT employee_idFROM Employees
)
AND salary < 30000
ORDER BY employee_id ASC;

高级字符串函数 / 正则表达式 / 子句

1667

concat函数

select user_id,
Concat(Upper(Left(name,1)),Lower(Right(name,Length(name)-1))) as name
from Users
order by user_id

1527

select patient_id,patient_name,conditions from Patients
where conditions like 'DIAB1%' or conditions like '% DIAB1%'