SQL
flyersong_bupt
这个作者很懒,什么都没留下…
展开
-
【LeetCode】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.解答:关于时间的Mysql的函数:1、TO_DAYS(date):给定一个日期data,返回从年份0开始的天数2转载 2017-09-28 10:39:47 · 265 阅读 · 0 评论 -
【LeetCode】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.解答:# Write your MySQL query statement belowDELETE p1转载 2017-09-28 10:07:15 · 226 阅读 · 0 评论 -
【LeetCode】183、Customers Who Never Order
题目要求:Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything.解答:1) LEFT JOIN 左联结:SELECT Name AS Custo转载 2017-09-27 21:22:13 · 189 阅读 · 0 评论 -
【LeetCode】182. Duplicate Emails
题目要求:Write a SQL query to find all duplicate emails in a table named Person.解答:比较顺的思路就是用GROUP BY、HAVING ;但是还可以用DISTINCT 联表查询的方法。1) SELECT Email FROM PersonGROUP BY Email HAVING COUNT(*) >= 22) 摘转载 2017-09-26 22:17:24 · 191 阅读 · 0 评论 -
【LeetCode】181. Employees Earning More Than Their Managers
1、题目要求:Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.转载 2017-09-23 19:27:26 · 162 阅读 · 0 评论 -
【LeetCode】175、combine two tables ; 176. Second Highest Salary
1、题目要求(175、combine two tables):Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those peopl转载 2017-09-21 19:29:37 · 226 阅读 · 0 评论 -
【SQL】SQL语法复习
1、SQL语句的语法顺序为 SELECT[DISTINCT] 、FROM 、WHERE 、GROUP BY 、 HAVING 、UNION 、ORDER BY2、FROM语句FROM a,b 这句FROM语句的输出是一张联合报,联合了表a和表b,这个联合表里的数据是a*b,即a和b的笛卡尔积。FROM输出的结果被WHERE语句筛选后要经过GROUP BY语转载 2017-09-20 16:24:51 · 921 阅读 · 0 评论 -
【LeetCode】627、Swap Salary
解答:1)使用IF UPDATE Salary SET sex = IF(sex='m','f','m')或者使用 CASE WHEN... THEN ...ELSE...END要注意语法问题UPDATE Salary SET sex = (CASE WHEN sex='m' THEN 'f' ELSE 'm' END)转载 2017-09-28 16:47:01 · 284 阅读 · 0 评论 -
【LeetCode】596、Classes More Than 5 Students
题目要求:There is a table courses with columns: student and classPlease list out all classes which have more than or equal to 5 students.解答:COUNT与 DISTINCT组合使用,满足The students should not be count转载 2017-09-28 15:13:52 · 887 阅读 · 0 评论