leetcode
每天都要被自己菜醒
这个作者很懒,什么都没留下…
展开
-
1322 广告效果 sum (if ()) group by 进行判断
Create table If Not Exists Ads (ad_id int, user_id int, action ENUM('Clicked', 'Viewed', 'Ignored'))Truncate table Adsinsert into Ads (ad_id, user_id, action) values ('1', '1', 'Clicked')insert into Ads (ad_id, user_id, action) values ('2', '2', 'Clicke原创 2020-09-09 18:20:52 · 191 阅读 · 0 评论 -
1285 找到连续区间的开始和结束数字没有完成(中等)601 题联动利用id -减去行号 如果是连续的数字
Create table If Not Exists Logs (log_id int)Truncate table Logsinsert into Logs (log_id) values ('1')insert into Logs (log_id) values ('2')insert into Logs (log_id) values ('3')insert into Logs (log_id) values ('7')insert into Logs (log_id) values ('原创 2020-09-09 17:59:33 · 133 阅读 · 0 评论 -
1231餐馆营业额变换增长(中等)sum() over(order by visited_on rows 6 preceding)
Create table If Not Exists Customer (customer_id int, name varchar(20), visited_on date, amount int)Truncate table Customerinsert into Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', '2019-01-01', '100')insert into Customer (custom原创 2020-09-09 17:24:21 · 969 阅读 · 0 评论 -
1308 不同性别的每日分数统计 sum()over()
Create table If Not Exists Scores (player_name varchar(20), gender varchar(1), day date, score_points int)Truncate table Scoresinsert into Scores (player_name, gender, day, score_points) values ('Aron', 'F', '2020-01-01', '17')insert into Scores (player原创 2020-09-09 16:45:08 · 155 阅读 · 0 评论 -
1303 求团队人数(简单) count() over()
Create table If Not Exists Employee (employee_id int, team_id int)Truncate table Employeeinsert into Employee (employee_id, team_id) values ('1', '8')insert into Employee (employee_id, team_id) values ('2', '8')insert into Employee (employee_id, team_i原创 2020-09-09 16:29:54 · 404 阅读 · 0 评论 -
1294 不同国家的天气类型(简单)
SQL 架构:Create table If Not Exists Countries (country_id int, country_name varchar(20))Create table If Not Exists Weather (country_id int, weather_state int, day date)Truncate table Countriesinsert into Countries (country_id, country_name) values ('2',原创 2020-09-09 16:21:01 · 201 阅读 · 0 评论 -
1281 学生们参加各科测试的次数
Create table If Not Exists Students (student_id int, student_name varchar(20))Create table If Not Exists Subjects (subject_name varchar(20))Create table If Not Exists Examinations (student_id int, subject_name varchar(20))Truncate table Studentsinsert原创 2020-09-09 15:58:27 · 126 阅读 · 0 评论 -
1270 向公司ceo汇报工作的所有人(中等 ) 三表联立
Create table If Not Exists Employees (employee_id int, employee_name varchar(30), manager_id int)Truncate table Employeesinsert into Employees (employee_id, employee_name, manager_id) values ('1', 'Boss', '1')insert into Employees (employee_id, employee原创 2020-09-04 23:53:45 · 802 阅读 · 0 评论 -
1264页面推荐(中等 union all )稍微的难点在于找1的朋友
Create table If Not Exists Friendship (user1_id int, user2_id int)Create table If Not Exists Likes (user_id int, page_id int)Truncate table Friendshipinsert into Friendship (user1_id, user2_id) values ('1', '2')insert into Friendship (user1_id, user2_i原创 2020-09-04 23:40:22 · 120 阅读 · 0 评论 -
1251 平均售价(简单)
Create table If Not Exists Prices (product_id int, start_date date, end_date date, price int)Create table If Not Exists UnitsSold (product_id int, purchase_date date, units int)Truncate table Pricesinsert into Prices (product_id, start_date, end_date, p原创 2020-09-04 23:00:57 · 165 阅读 · 0 评论 -
1241 每个帖子的评论数(自己写出)
Create table If Not Exists Submissions (sub_id int, parent_id int)Truncate table Submissionsinsert into Submissions (sub_id, parent_id) values ('1', 'None')insert into Submissions (sub_id, parent_id) values ('2', 'None')insert into Submissions (sub_id,原创 2020-09-04 22:32:29 · 165 阅读 · 0 评论 -
1212 查询球队积分(中等自己写出)用到了union all
Create table If Not Exists Teams (team_id int, team_name varchar(30))Create table If Not Exists Matches (match_id int, host_team int, guest_team int, host_goals int, guest_goals int)Truncate table Teamsinsert into Teams (team_id, team_name) values ('10'原创 2020-09-04 22:14:07 · 124 阅读 · 0 评论 -
1211. 查询结果的质量和占比(自己写出 简单题)
sql 架构Create table If Not Exists Queries (query_name varchar(30), result varchar(50), position int, rating int)Truncate table Queriesinsert into Queries (query_name, result, position, rating) values ('Dog', 'Golden Retriever', '1', '5')insert into Quer原创 2020-09-02 16:17:32 · 187 阅读 · 0 评论 -
1205 每月交易II leetcode数据库刷题 (没写出来)
1205 每月交易IIsql 架构;create table if not exists Transactions (id int, country varchar(4), state enum('approved', 'declined'), amount int, trans_date date)create table if not exists Chargebacks (trans_id int, trans_date date)Truncate table Transactionsins原创 2020-09-02 16:02:03 · 158 阅读 · 0 评论 -
1204 最后一个进入电梯的人 leetcode 数据库刷题(自己写出)
1204sql 架构:Create table If Not Exists Queue (person_id int, person_name varchar(30), weight int, turn int)Truncate table Queueinsert into Queue (person_id, person_name, weight, turn) values ('5', 'George Washington', '250', '1')insert into Queue (pers原创 2020-09-02 12:17:52 · 178 阅读 · 0 评论 -
1193 每月的交易 leetcode 数据库刷题(自己写出)
1193 每月的交易SQL 架构:create table if not exists Transactions (id int, country varchar(4), state enum('approved', 'declined'), amount int, trans_date date)Truncate table Transactionsinsert into Transactions (id, country, state, amount, trans_date) values ('原创 2020-09-02 12:06:38 · 141 阅读 · 0 评论 -
1179 重新格式化部门表(列转行问题)(自己没写出来)
重新格式化部门表SQL 架构Create table If Not Exists Department (id int, revenue int, month varchar(5))Truncate table Departmentinsert into Department (id, revenue, month) values ('1', '8000', 'Jan')insert into Department (id, revenue, month) values ('2', '9000.原创 2020-09-02 11:46:42 · 102 阅读 · 0 评论 -
1174. leetcode 刷题即时食物配送 II
# Write your MySQL query statement below #错误写法,有点懵select round(sum(if(min_date=customer_pref_delivery_date,1,0)) /count(*)*100 ,2)as 'immediate_percentage'from (select distinct delivery_id,customer_id ,min(order_date) as 'min_date',customer_pref_del原创 2020-08-31 03:14:28 · 287 阅读 · 0 评论 -
1173 即时食物配送 I(简单题)
select round(sum(if(order_date=customer_pref_delivery_date,1,0)) /count(*) *100,2)as 'immediate_percentage'from Delivery原创 2020-08-31 02:22:06 · 177 阅读 · 0 评论 -
1164. 指定日期的产品价格(中等题)
select product_id,if(new_price>0,new_price,10) as ' price'FROM(select distinct product_id,10 as 'price'from Products) uleft join (SELECT product_id as 'nid',new_price FROM (select product_id, new_price,change_date, #对月份逆向排序 选择最大的.原创 2020-08-31 02:12:22 · 234 阅读 · 0 评论 -
leetcode 数据库刷题 1159. 市场分析 II
# Write your MySQL query statement belowSELECT distinct user_id as 'seller_id',case when favorite_brand = item_brand then 'yes' else 'no'end as '2nd_item_fav_brand'FROM #用户卖出列表 (select user_id,favorite_brand #,b.order_date,b.seller_id,原创 2020-08-31 01:17:23 · 195 阅读 · 0 评论