2018年04月_betty1121

转载 578. Get Highest Answer Rate Question

Get the highest answer rate question from a table survey_log with these columns: uid, action, question_id, answer_id, q_num, timestamp.uid means user id; action has these kind of values: "show", "

2018-04-25 08:23:16 544

转载 580. Count Student Number in Departments(必看)

group by中 count(*) 与 count(student_id) 不一定相等，count(*) 计算该组有多少条目，一定会>0, 但是count(student_id)计算每组group中有多少student_id，如果student个数为0，则count(student_id)可能为0。A university uses 2 data tables, student and ...

2018-04-25 07:46:34 572

原创 585. Investments in 2016

Write a query to print the sum of all total investment values in 2016 (TIV_2016), to a scale of 2 decimal places, for all policy holders who meet the following criteria:Have the sameTIV_2015value as...

2018-04-21 15:52:26 318

原创 612. Shortest Distance in a Plane

Table point_2d holds the coordinates (x,y) of some unique points (more than two) in a plane.Write a query to find the shortest distance between these points rounded to 2 decimals.| x | y ||----|---...

2018-04-21 06:54:26 595

原创 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 587

转载 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 185

原创 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 2754

转载 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 267

原创 176. Second Highest Salary（必看）

考察limit offset，ifnull, 记得distinct① selete * from testtable limit 2,1;② selete * from testtable limit 2 offset 1;注意：1.数据库数据计算是从0开始的2.offsetX是跳过X个数据，limitY是选取Y个数据3.limit X,Y中X表示跳过X个数据，读取Y个数据这两个都是能完...

2018-04-19 05:40:05 308

原创 619. Biggest Single Number（必看）

Null, max()/min() 用法Tablenumbercontains many numbers in columnnumincluding duplicated ones.Can you write a SQL query to find the biggest number, which only appears once.+---+|num|+---+| 8 || 8...

2018-04-19 05:13:59 395

原创 197. Rising Temperature(必看)

Datediff 用法SELECT DATEDIFF('2011-08-08','2011-08-17'); -- "-9 days"SELECT DATEDIFF('2011-08-17','2011-08-08'); -- "9 days"Given a Weather table, write a SQL query to find all dates' Ids with higher t...

2018-04-18 07:55:38 310

原创 577. Employee Bonus

null 不能直接和数值比较，如要选择null对应的row应该特别注明where col is nullSelect all employee's name and bonus whose bonus is < 1000.Table:Employee+-------+--------+-----------+--------+| empId | name | supervisor| s...

2018-04-18 05:41:55 212

原创 607. Sales Person

DescriptionGiven three tables: salesperson, company, orders.Output all the names in the table salesperson, who didn’t have sales to company 'RED'.ExampleInputTable: salesperson+----------+------+-----...

2018-04-18 04:52:10 451

转载 603. Consecutive Available Seats（必看）

Several friends at a cinema ticket office would like to reserve consecutive available seats.Can you help to query all the consecutive available seats order by the seat_id using the following cinematab...

2018-04-17 07:58:22 798

原创 610. Triangle Judgement

思路：case when 的使用A pupil Tim gets homework to identify whether three line segments could possibly form a triangle.However, this assignment is very heavy because there are hundreds of records to calcula...

2018-04-17 07:41:50 316

原创 586. Customer Placing the Largest Number of Orders

思路：原题只有exactly one 最大订单数量，group by之后用订单量降序排列，并limit取第一个值如果不止一个最大订单数，找出最大订单值，然后找出订单量等于这个值的就可以了。Query thecustomer_numberfrom theorderstable for the customer who has placed the largest number of orde...

2018-04-17 07:07:13 787

原创 leetcode 584. Find Customer Referee

Given a tablecustomerholding customers information and the referee.+------+------+-----------+| id | name | referee_id|+------+------+-----------+| 1 | Will | NULL || 2 | Jane | ...

2018-04-17 06:45:26 525 2

原创 613. Shortest Distance in a Line--ABS() and MIN()

Table point holds the x coordinate of some points on x-axis in a plane, which are all integers.Write a query to find the shortest distance between two points in these points.| x ||-----|| -1 || ...

2018-04-17 06:39:58 215

转载 LeetCode 597: Friend Requests I: Overall Acceptance Rate

https://hzhou.me/LeetCode/LeetCode-Friend-Requests-I-Overall-Acceptance-Rate.html

2018-04-17 04:56:35 843

转载 88. Merge Sorted Array

Given two sorted integer arrays nums1 and nums2, merge nums2 into nums1 as one sorted array.Note:You may assume that nums1 has enough space (size that is greater or equal to m + n) to hold additional ...

2018-04-06 07:56:30 78

转载 83. Remove Duplicates from Sorted List

Given a sorted linked list, delete all duplicates such that each element appear only once.For example,Given 1->1->2, return 1->2.Given 1->1->2->3->3, return 1->2->3.# Defini...

2018-04-06 05:06:17 66

转载 70. Climbing Stairs

You are climbing a stair case. It takes n steps to reach to the top.Each time you can either climb 1 or 2 steps. In how many distinct ways can you climb to the top?Note: Given n will be a positive int...

2018-04-06 04:36:28 130

转载 GBDT的正则化及与XGBOOST区别

https://blog.csdn.net/hlang8160/article/details/78575060

2018-04-05 12:38:00 776

betty1121的博客