DB_dictionary_190102_MySQLDictionaryFromLeetCode

最新推荐文章于 2022-02-08 19:22:01 发布

llll-ine

最新推荐文章于 2022-02-08 19:22:01 发布

阅读量224

点赞数

分类专栏：字典

字典专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.Swap Salary

Given a table salary, such as the one below, that has m=male and f=female values. Swap all f and m values (i.e., change all f values to m and vice versa) with a single update query and no intermediate temp table.
For example:

id	name	sex	salary
1	A	m	2500
2	B	f	1500
3	C	m	5500
4	D	f	500

After running your query, the above salary table should have the following rows:

id	name	sex	salary
1	A	f	2500
2	B	m	1500
3	C	f	5500
4	D	m	500

Solution:

update salary
set
    sex= case sex
        when 'm' then 'f'
        else 'm'
    end;

2.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 students.
Can you write a SQL query to output the result for Mary?

id	student
1	Abbot
2	Doris
3	Emerson
4	Green
5	Jeames

For the sample input, the output is:

id	student
1	Doris
2	Abbot
3	Green
4	Emerson
5	Jeames

Note:
If the number of students is odd, there is no need to change the last one’s seat.

#TIPS
#case
#   when xx then yy
#   when xx then yy
#   else yy
# MOD(xx,num)

select
(
    case
            when MOD(id,2)!=0 and counts!=id then id+1
            when MOD(id,2)!=0 and counts=id then id
            else id-1
        end
) as id,student
from seat,
(
    select count(*) as counts from seat
) as seat_counts
#Every derived table must have its own alias
order by id asc

select
(
    case
            when id%2=1 and id!=maxV then id+1
            when id%2=1 and id=maxV then id
            else id-1
        end
) as id,student
from seat,(
select max(id) as maxV from seat)as maxs
order by id asc

选择id是经过调整的id，因为对应的另一列无法调整，再选择student，这两个变量都是从seat表出来的，后续的括号中计算的是前面选择id时用的中间变量

3.601. Human Traffic of Stadium

X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, date, people

Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).

For example, the table 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

For the sample data above, the output is:

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

通过笛卡儿积解决问题
my version

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

example version

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

4.197. Rising Temperature

Given a Weather table, write a SQL query to find all dates’ Ids with higher temperature compared to its previous (yesterday’s) dates.

Id(INT)	RecordDate(DATE)	Temperature(INT)
1	2015-01-01	10
2	2015-01-02	25
3	2015-01-03	20
4	2015-01-04	30

For example, return the following Ids for the above Weather table:

Id
2
4

my solution:

select t1.Id from
Weather t1,Weather t2
where
t1.id>t2.id and t1.Temperature>t2.Temperature
and t2.id = t1.id-1

leetcode standard solution:
DATEDIFF(A,B)=1 如果A-B=1即为真

SELECT
    weather.id AS 'Id'
FROM
    weather
        JOIN
    weather w ON DATEDIFF(weather.RecordDate, w.RecordDate) = 1
        AND weather.Temperature > w.Temperature
;

5.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 above Logs table, 1 is the only number that appears consecutively for at least three times.

ConsecutiveNums
1

my solution:

select Num as ConsecutiveNums from
(
    select t1.Num from
    logs t1,logs t2,logs t3
    where
    t1.num=t2.num and t1.num=t3.num and t2.num=t3.num
    and
    (
        t1.id-t2.id=-1 and t1.id-t3.id=-2 
        and t2.id-t3.id=-1 #t1 t2 t3
    )
) as cnt

6.196. Delete Duplicate Emails

Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.

Id	Email
1	john@example.com
2	bob@example.com
3	john@example.com

Id is the primary key column for this table.
For example, after running your query, the above Person table should have the following rows:

Id	Email
1	john@example.com
2	bob@example.com

Note:

Your output is the whole Person table after executing your sql. Use delete statement.
Solution:
Approach: Using DELETE and WHERE clause [Accepted]
Algorithm

By joining this table with itself on the Email column, we can get the following code.

SELECT p1.*
FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email
;

Then we need to find the bigger id having same email address with other records. So we can add a new condition to the WHERE clause like this.

SELECT p1.*
FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id
;

As we already get the records to be deleted, we can alter this statement to DELETE in the end.

MySQL

DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id

7.184. Department Highest Salary

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

Id	Name	Salary	DepartmentId
1	Joe	70000	1
2	Henry	80000	2
3	Sam	60000	2
4	Max	90000	1

The Department table holds all departments of the company.

Id	Name
1	IT
2	Sales

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

Department	Employee	Salary
IT	Max	90000
Sales	Henry	80000

Solution:

select department.name as Department,
employee.name as Employee,employee.salary as Salary
from employee
join department
on employee.departmentid=department.id
where
(employee.departmentid,employee.salary) 
in
(select departmentid,max(salary) from employee
group by departmentid)
order by Salary

8.185. Department Top Three Salaries

The Employee table holds all employees. Every employee has an Id, and there is also a column for the 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

The Department table holds all departments of the company.

Id	Name
1	IT
2	Sales

Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows.

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

Solution:
A top 3 salary in this company means there is no more than 3 salary bigger than itself in the company.

select e1.Name as 'Employee', e1.Salary
from Employee e1
where 3 >
(
    select count(distinct e2.Salary)
    from Employee e2
    where e2.Salary > e1.Salary
)
;

Then, we need to join the Employee table with Department in order to retrieve the department information.

SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        )
;

9.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.

Id	Score
1	3.50
2	3.65
3	4.00
4	3.85
5	4.00
6	3.65

For example, given the above Scores table, your query should generate the following report (order by highest score):

Score	Rank
4.00	1
4.00	1
3.85	2
3.65	3
3.65	3
3.50	4

Solution:

SELECT Scores.Score,COUNT(Ranking.Score) AS Rank FROM
Scores,(
            SELECT DISTINCT Score
            FROM Scores
        ) AS Ranking
WHERE Scores.Score<=Ranking.Score
GROUP BY Scores.Id,Scores.Score
ORDER BY Scores.Score DESC

SELECT
@rank
FROM
  (SELECT @rank := 0) init

update goods  
set price = (  
case   
  when price between 0 and 99 then price * 1.2  
  when price between 100 and 999 then price * 1.1  
  when price between 1000 and 1999 then price * 1.05  
  when price > 1999 then price * 1.02  
end);  
select * from goods;

更多关于Mysql设置变量问题
https://www.cnblogs.com/genialx/p/5932558.html

SELECT Score,
@rank:= @rank + (@prev !=(@prev:=Score)) Rank
from
Scores,(SELECT @rank := 0,@prev := -100) init
order by Score desc

10.177. Nth Highest Salary

Write a SQL query to get the nth highest salary from the Employee table.

Id	Salary
1	100
2	200
3	300

For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

getNthHighestSalary(2)
200

My solution:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT t1.Salary FROM Employee t1
      ,(
          SELECT DISTINCT Salary FROM
          Employee
        ) AS t2
      WHERE t1.Salary>=t2.Salary AND
      N=COUNT(t2.Salary)
      
      
  );
END

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    declare M int;
    set M = N-1;
  RETURN (
      
      # Write your MySQL query statement below.
      
      select distinct Salary from Employee
      order by Salary DESC limit 1 offset M
      
  );
END

Solution:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT Salary FROM Employee
      ORDER BY Salary DESC LIMIT M,1
      
      
  );
END