LeetCode 数据库 627、620、626、601、596、595、197、176、175

LeetCode 627

解题思路：

• if (sex = ‘m’,‘f’,‘m’);
• char(ascii(‘m’) + ascii(‘f’) - ascii(sex));

LeetCode 620

解题思路：

• select * from cinema where id%2 = 1 and description != ‘boring’ order by rating desc

LeetCode 626

解题思路：

• select
  (
  case
  when id % 2 = 1 and id = (select count(1) from seat) then id
  when id % 2 = 1 then id + 1
  else id - 1
  end
  ) as id, student
  from seat
  order by id

LeetCode 601

解题思路：

• select distinct a.* from
  stadium as a, stadium as b, stadium as c
  where a.people >= 100 and b.people >= 100 and c.people >= 100
  and (
  (a.id = b.id - 1 and b.id = c.id - 1) or
  (a.id = b.id - 1 and a.id = c.id + 1) or
  (a.id = b.id + 1 and b.id = c.id + 1)
  )
  order by a.id

LeetCode 596

解题思路：

• select class from courses
  group by class
  having count(distinct student) >= 5

LeetCode 595

解题思路：

• select name, population, area from World
  where area > 3000000 or population > 25000000

LeetCode 197

解题思路：

• select a.id as id from weather a,weather b
  where datediff(a.RecordDate,b.RecordDate)=1
  and a.temperature>b.temperature

LeetCode 176

• select (
  select DISTINCT Salary from Employee
  order by Salary desc
  limit 1,1) as SecondHighestSalary

LeetCode 175

解题思路：

• select p.FirstName, p.LastName, a.City, a.State
  from Person as p left join Address as a
  on p.PersonId = a.PersonId

LeetCode 180

解题思路：

• select DISTINCT l1.Num as ‘ConsecutiveNums’ from Logs as l1, Logs as l2, Logs as l3
  where l1.id = l2.id - 1 and l2.id = l3.id - 1 and l1.Num = l2.Num and l2.Num = l3.Num