601. 體育館的人流量

X 市建了一個(gè)新的體育館，每日人流量信息被記錄在這三列信息中：序號(hào) (id)、日期 (date)、 人流量 (people)。

請(qǐng)編寫一個(gè)查詢語句，找出高峰期時(shí)段，要求連續(xù)三天及以上，并且每天人流量均不少于100。

例如，表 stadium：

+------+------------+-----------+ | id | date | people | +------+------------+-----------+ | 1 | 2017-01-01 | 10 | | 2 | 2017-01-02 | 109 | | 3 | 2017-01-03 | 150 | | 4 | 2017-01-04 | 99 | | 5 | 2017-01-05 | 145 | | 6 | 2017-01-06 | 1455 | | 7 | 2017-01-07 | 199 | | 8 | 2017-01-08 | 188 | +------+------------+-----------+

對(duì)于上面的示例數(shù)據(jù)，輸出為：

+------+------------+-----------+ | id | date | people | +------+------------+-----------+ | 5 | 2017-01-05 | 145 | | 6 | 2017-01-06 | 1455 | | 7 | 2017-01-07 | 199 | | 8 | 2017-01-08 | 188 | +------+------------+-----------+

Note:
每天只有一行記錄，日期隨著 id 的增加而增加。

思路/筆記：自連接的應(yīng)用，這個(gè)語句其實(shí)有點(diǎn)取巧，因?yàn)槿掌诙际沁B續(xù)的，而Id屬于INT類型，比TO_DATE(date, 'yyyymmdd')/TO_CHAR(DATE, 'yyyymmdd')這樣的運(yùn)算快多了。

select distinct t1.*from stadium t1, stadium t2, stadium t3where t1.people >= 100 and t2.people >= 100 and t3.people >= 100and ((t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1)or (t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1)or (t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2))order by t1.id

262. 行程和用戶

Trips 表中存所有出租車的行程信息。每段行程有唯一健 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外鍵。Status 是枚舉類型，枚舉成員為 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。

+----+-----------+-----------+---------+--------------------+----------+ | Id | Client_Id | Driver_Id | City_Id | Status |Request_at| +----+-----------+-----------+---------+--------------------+----------+ | 1 | 1 | 10 | 1 | completed |2013-10-01| | 2 | 2 | 11 | 1 | cancelled_by_driver|2013-10-01| | 3 | 3 | 12 | 6 | completed |2013-10-01| | 4 | 4 | 13 | 6 | cancelled_by_client|2013-10-01| | 5 | 1 | 10 | 1 | completed |2013-10-02| | 6 | 2 | 11 | 6 | completed |2013-10-02| | 7 | 3 | 12 | 6 | completed |2013-10-02| | 8 | 2 | 12 | 12 | completed |2013-10-03| | 9 | 3 | 10 | 12 | completed |2013-10-03| | 10 | 4 | 13 | 12 | cancelled_by_driver|2013-10-03| +----+-----------+-----------+---------+--------------------+----------+

Users 表存所有用戶。每個(gè)用戶有唯一鍵 Users_Id。Banned 表示這個(gè)用戶是否被禁止，Role 則是一個(gè)表示（‘client’, ‘driver’, ‘partner’）的枚舉類型。

+----------+--------+--------+ | Users_Id | Banned | Role | +----------+--------+--------+ | 1 | No | client | | 2 | Yes | client | | 3 | No | client | | 4 | No | client | | 10 | No | driver | | 11 | No | driver | | 12 | No | driver | | 13 | No | driver | +----------+--------+--------+

寫一段 SQL 語句查出 2013年10月1日 至 2013年10月3日 期間非禁止用戶的取消率?；谏媳?#xff0c;你的 SQL 語句應(yīng)返回如下結(jié)果，取消率（Cancellation Rate）保留兩位小數(shù)。

+------------+-------------------+ | Day | Cancellation Rate | +------------+-------------------+ | 2013-10-01 | 0.33 | | 2013-10-02 | 0.00 | | 2013-10-03 | 0.50 | +------------+-------------------+

思路/筆記：常用操作，手動(dòng)滑稽

select t.Request_at AS "Day",round(sum(case when t.Status = 'completed' then 0else 1end) / count(0),2) AS "Cancellation Rate"from trips t where EXISTS (select u.Users_Idfrom users uwhere u.Users_Id = t.Client_Idand u.Banned = 'No')and t.Request_at between '2013-10-01' and '2013-10-03' group by Day

185. 部門工資前三高的員工

Employee 表包含所有員工信息，每個(gè)員工有其對(duì)應(yīng)的 Id, salary 和 department Id 。

+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | | 5 | Janet | 69000 | 1 | | 6 | Randy | 85000 | 1 | +----+-------+--------+--------------+

Department 表包含公司所有部門的信息。

+----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+

編寫一個(gè) SQL 查詢，找出每個(gè)部門工資前三高的員工。例如，根據(jù)上述給定的表格，查詢結(jié)果應(yīng)返回：

+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Randy | 85000 | | IT | Joe | 70000 | | Sales | Henry | 80000 | | Sales | Sam | 60000 | +------------+----------+--------+

思路/筆記：

通過自連接排定次序

join部門表

select d.Name Department, t.Name Employee, Salaryfrom (select Name,Salary,DepartmentId,(select count(distinct e1.Salary)from Employee e1where e1.DepartmentId = e2.DepartmentIdand e1.Salary > e2.Salary) rankfrom Employee e2)tleft join Department d on d.Id = t.DepartmentIdwhere rank < 3and d.Id is not nullorder by DepartmentId,Salary desc

總結(jié)

數(shù)據(jù)庫操作的效率差別，需要在百萬/千萬/億/十億級(jí)別，才會(huì)有明顯的差異，比如BAT的數(shù)據(jù)級(jí)別，只能自行研發(fā)數(shù)據(jù)庫。所以這里只涉及到了很多基礎(chǔ)的操作，比如case的用法，自連接的用法等。具體在項(xiàng)目常用到的操作有根據(jù)范式來設(shè)計(jì)表、根據(jù)常用到的字段來建立索引、考慮根據(jù)數(shù)據(jù)量按日/周/月分區(qū)、如何通過存儲(chǔ)過程/函數(shù)高效轉(zhuǎn)移數(shù)據(jù)、如何設(shè)置UNDO表空間保證數(shù)據(jù)庫性能、怎樣查看常用視圖與統(tǒng)計(jì)表、如何備份、還原與清理數(shù)據(jù)...都還是需要在實(shí)際項(xiàng)目里去學(xué)習(xí)。

總結(jié)

以上是生活随笔為你收集整理的leetCode数据查询笔记（困难）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。