![](https://img-blog.csdnimg.cn/20201014180756738.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
SQL
文章平均质量分 58
betty1121
这个作者很懒,什么都没留下…
展开
-
1225. Report Contiguous Dates----判断连续日期
Table:Failed+--------------+---------+| Column Name | Type |+--------------+---------+| fail_date | date |+--------------+---------+Primary key for this table is fail_date.Failed table contains the days of failed tasks.Table:Succeed..原创 2020-10-09 15:01:29 · 690 阅读 · 0 评论 -
1285. Find the Start and End Number of Continuous Ranges----判断连续数字
Table:Logs+---------------+---------+| Column Name | Type |+---------------+---------+| log_id | int |+---------------+---------+id is the primary key for this table.Each row of this table contains the ID in a log Table.Sinc...原创 2020-10-03 05:35:06 · 550 阅读 · 0 评论 -
1321. Restaurant Growth----利用窗口函数连续几天累加求和和均值(BETWEEN 6 PRECEDING AND CURRENT ROW)
Table:Customer+---------------+---------+| Column Name | Type |+---------------+---------+| customer_id | int || name | varchar || visited_on | date || amount | int |+---------------+---------+(customer_id, .翻译 2020-10-03 03:06:30 · 426 阅读 · 0 评论 -
1459. Rectangles Area----同列数据相减
Table:Points+---------------+---------+| Column Name | Type |+---------------+---------+| id | int || x_value | int || y_value | int |+---------------+---------+id is the primary key for this table.Each p.原创 2020-10-01 08:04:38 · 289 阅读 · 0 评论 -
1501. Countries You Can Safely Invest In----取一个字符串左边N个字母 left(string, N)
TablePerson:+----------------+---------+| Column Name | Type |+----------------+---------+| id | int || name | varchar || phone_number | varchar |+----------------+---------+id is the primary key for this table..原创 2020-09-30 15:04:17 · 285 阅读 · 0 评论 -
1527. Patients With a Condition----字母选择 like %...%
Table:Patients+--------------+---------+| Column Name | Type |+--------------+---------+| patient_id | int || patient_name | varchar || conditions | varchar |+--------------+---------+patient_id is the primary key for this table.'co.原创 2020-09-30 07:22:03 · 302 阅读 · 0 评论 -
1543. Fix Product Name Format----去掉字符串空格trim()
Table:Sales+--------------+---------+| Column Name | Type |+--------------+---------+| sale_id | int || product_name | varchar || sale_date | date |+--------------+---------+sale_id is the primary key for this table.Each row .原创 2020-09-30 02:44:51 · 145 阅读 · 0 评论 -
1107. New Users Daily Count----通过min()选择分组中的第一条数据
Table:Traffic+---------------+---------+| Column Name | Type |+---------------+---------+| user_id | int || activity | enum || activity_date | date |+---------------+---------+There is no primary key for this table, it .原创 2020-09-28 14:56:11 · 299 阅读 · 0 评论 -
1435. Create a Session Bar Chart----case when
Table:Sessions+---------------------+---------+| Column Name | Type |+---------------------+---------+| session_id | int || duration | int |+---------------------+---------+session_id is the primary key for .原创 2020-09-26 08:56:32 · 618 阅读 · 0 评论 -
1204. Last Person to Fit in the Elevator----参考No.534 窗口函数累计求和
Table:Queue+-------------+---------+| Column Name | Type |+-------------+---------+| person_id | int || person_name | varchar || weight | int || turn | int |+-------------+---------+person_id is the primary key colu.原创 2020-09-25 16:38:00 · 109 阅读 · 0 评论 -
1158. Market Analysis I
Table:Users+----------------+---------+| Column Name | Type |+----------------+---------+| user_id | int || join_date | date || favorite_brand | varchar |+----------------+---------+user_id is the primary key of this tab.原创 2020-09-25 13:27:13 · 137 阅读 · 0 评论 -
1164. Product Price at a Given Date----union /case when + null
Table:Products+---------------+---------+| Column Name | Type |+---------------+---------+| product_id | int || new_price | int || change_date | date |+---------------+---------+(product_id, change_date) is the primary k.原创 2020-09-24 15:33:09 · 218 阅读 · 0 评论 -
1174. Immediate Food Delivery II----row_number()/first_value(), case when
Table:Delivery+-----------------------------+---------+| Column Name | Type |+-----------------------------+---------+| delivery_id | int || customer_id | int || order_date .原创 2020-09-24 14:53:36 · 237 阅读 · 0 评论 -
1050. Actors and Directors Who Cooperated At Least Three Times----count() 可以放在having后面
Table:ActorDirector+-------------+---------+| Column Name | Type |+-------------+---------+| actor_id | int || director_id | int || timestamp | int |+-------------+---------+timestamp is the primary key column for this table..原创 2020-09-24 13:11:55 · 325 阅读 · 0 评论 -
534. Game Play Analysis III----window function sum() over partition by 统计分组累加的和
Table:Activity+--------------+---------+| Column Name | Type |+--------------+---------+| player_id | int || device_id | int || event_date | date || games_played | int |+--------------+---------+(player_id, event_date.原创 2020-09-24 06:56:16 · 191 阅读 · 0 评论 -
1084. Sales Analysis III----not between
Table:Product+--------------+---------+| Column Name | Type |+--------------+---------+| product_id | int || product_name | varchar || unit_price | int |+--------------+---------+product_id is the primary key of this table.Tab.原创 2020-09-23 15:35:19 · 120 阅读 · 0 评论 -
1194. Tournament Winners---取每组中的最大值所在的记录
Table:Players+-------------+-------+| Column Name | Type |+-------------+-------+| player_id | int || group_id | int |+-------------+-------+player_id is the primary key of this table.Each row of this table indicates the group of each .原创 2020-09-23 08:23:57 · 264 阅读 · 0 评论 -
1341. Movie Rating----union使用
Table:Movies+---------------+---------+| Column Name | Type |+---------------+---------+| movie_id | int || title | varchar |+---------------+---------+movie_id is the primary key for this table.title is the name of the mov.原创 2020-09-19 14:19:26 · 378 阅读 · 0 评论 -
1479. Sales by Day of the Week----case when条件检查
Table:Orders+---------------+---------+| Column Name | Type |+---------------+---------+| order_id | int || customer_id | int || order_date | date | | item_id | varchar || quantity | int |+--------------.原创 2020-09-19 08:09:06 · 151 阅读 · 0 评论 -
1445. Apples & Oranges----两者求差
Table:Sales+---------------+---------+| Column Name | Type |+---------------+---------+| sale_date | date || fruit | enum | | sold_num | int | +---------------+---------+(sale_date,fruit) is the primary key for th.原创 2020-09-19 08:02:44 · 271 阅读 · 0 评论 -
日期,ifnull等用法
MySql 里的IFNULL、NULLIF和ISNULL用法MySQL 日期与时间方面的函数转载 2020-09-18 08:59:07 · 378 阅读 · 0 评论 -
1454. Active Users----dense_rank()排序
TableAccounts:+---------------+---------+| Column Name | Type |+---------------+---------+| id | int || name | varchar |+---------------+---------+the id is the primary key for this table.This table contains the acco.原创 2020-09-17 17:12:54 · 440 阅读 · 0 评论 -
1127. User Purchase Platform----分组统计,通过产生新的join table来统计不存在的字段组合
Table:Spending+-------------+---------+| Column Name | Type |+-------------+---------+| user_id | int || spend_date | date || platform | enum | | amount | int |+-------------+---------+The table logs the spendings .原创 2020-09-17 16:36:06 · 253 阅读 · 0 评论 -
569. Median Employee Salary----通过分组排序来判断中位数
TheEmployeetable holds all employees. The employee table has three columns: Employee Id, Company Name, and Salary.+-----+------------+--------+|Id | Company | Salary |+-----+------------+--------+|1 | A | 2341 ||2 | A ..原创 2020-09-17 06:56:41 · 426 阅读 · 0 评论 -
1384. Total Sales Amount by Year----取日期中的年份year()和计算日期之间的差别datediff()
Table:Product+---------------+---------+| Column Name | Type |+---------------+---------+| product_id | int || product_name | varchar |+---------------+---------+product_id is the primary key for this table.product_name is the name .原创 2020-09-15 17:19:58 · 525 阅读 · 0 评论 -
1132. Reported Posts II
Table:Actions+---------------+---------+| Column Name | Type |+---------------+---------+| user_id | int || post_id | int || action_date | date || action | enum || extra | varchar |+--------------.原创 2020-09-13 08:13:15 · 160 阅读 · 0 评论 -
615. Average Salary: Departments VS Company----通过DATE_FORMAT( , “%Y-%m“)获得年和月
Given two tables as below, write a query to display the comparison result (higher/lower/same) of the average salary of employees in a department to the company's average salary.Table:salary| id | employee_id | amount | pay_date ||----|------------.原创 2020-09-05 07:04:31 · 231 阅读 · 0 评论 -
1336. Number of Transactions per Visit(重做)
Table:Visits+---------------+---------+| Column Name | Type |+---------------+---------+| user_id | int || visit_date | date |+---------------+---------+(user_id, visit_date) is the primary key for this table.Each row of thi.翻译 2020-09-02 16:07:10 · 296 阅读 · 0 评论 -
1098. Unpopular Books
+----------------+---------+| Column Name | Type |+----------------+---------+| book_id | int || name | varchar || available_from | date |+----------------+---------+book_id is the primary key of this table.Table:O..原创 2020-09-02 15:48:52 · 163 阅读 · 0 评论 -
1270. All People Report to the Given Manager
+---------------+---------+| Column Name | Type |+---------------+---------+| employee_id | int || employee_name | varchar || manager_id | int |+---------------+---------+employee_id is the primary key for this table.Each row of .原创 2020-08-31 15:58:29 · 388 阅读 · 0 评论 -
180. Consecutive Numbers
Write a SQL query to find all numbers that appear at least three times consecutively.+----+-----+| Id | Num |+----+-----+| 1 | 1 || 2 | 1 || 3 | 1 || 4 | 2 || 5 | 1 || 6 | 2 || 7 | 2 |+----+-----+For example, given the原创 2020-08-31 11:59:09 · 105 阅读 · 0 评论 -
185. Department Top Three Salaries
TheEmployeetable holds all employees. Every employee has an Id, and there is also a column for the department Id.+----+-------+--------+--------------+| Id | Name | Salary | DepartmentId |+----+-------+--------+--------------+| 1 | Joe | 85000 ..原创 2020-08-31 03:25:30 · 65 阅读 · 0 评论 -
178. Rank Scores
Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no "holes" between ranks原创 2020-08-29 06:25:26 · 133 阅读 · 0 评论 -
转:Sql 四大排名函数(ROW_NUMBER、RANK、DENSE_RANK、NTILE)简介
https://www.cnblogs.com/52xf/p/4209211.htmlCREATE TABLE [dbo].[Order]( [ID] [int] IDENTITY(1,1) NOT NULL, [UserId] [int] NOT NULL, [TotalPrice] [int] NOT NULL, [SubTime] [datetime] NOT NULL, CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED转载 2020-08-27 05:05:42 · 140 阅读 · 0 评论 -
177. Nth Highest Salary
Write a SQL query to get thenthhighest salary from theEmployeetable.+----+--------+| Id | Salary |+----+--------+| 1 | 100 || 2 | 200 || 3 | 300 |+----+--------+For example, given the above Employee table, thenthhighest salary...翻译 2020-08-26 16:29:38 · 65 阅读 · 0 评论 -
1179. Reformat Department Table
+---------------+---------+| Column Name | Type |+---------------+---------+| id | int || revenue | int || month | varchar |+---------------+---------+(id, month) is the primary key of this table.The table has.原创 2020-08-26 03:43:53 · 180 阅读 · 0 评论 -
570. Managers with at Least 5 Direct Reports
The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.+------+----------+-----------+----------+|Id |Name |Dep...原创 2018-04-21 06:02:07 · 579 阅读 · 0 评论 -
626. Exchange Seats
Mary is a teacher in a middle school and she has a table seat storing students' names and their corresponding seat ids.The column id is continuous increment.Mary wants to change seats for the adjacent...转载 2018-04-20 10:31:50 · 178 阅读 · 0 评论 -
597. Friend Requests I: Overall Acceptance Rate(必看)----计算一对字段出现的次数
Round() and isnull, round() can be used directly.In social network like Facebook or Twitter, people send friend requests and accept others’ requests as well. Now given two tables as below:Table:frie...原创 2018-04-19 08:02:25 · 2747 阅读 · 0 评论 -
196. Delete Duplicate Emails(必看)
max,min函数with group by是针对每组的数据求最大最小值Approach: Using DELETE and WHERE clauseWrite a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smal...转载 2018-04-19 06:30:12 · 252 阅读 · 0 评论