04 2023 档案
摘要：樹節點 select id, 'Root' as Type from tree where p_id is null union select id, 'Inner' as Type from tree where id in ( select distinct p_id from tree whe
阅读全文
摘要：銷售員 select s.`name` from salesperson s left join orders o on s.sales_id=o.sales_id left join company c on o.com_id=c.com_id and c.name='RED' group by
阅读全文
摘要：大的國家 select `name`, population, area from World where area >= 3000000 or population >= 25000000
阅读全文
摘要：訂單最多的客戶 select customer_number from Orders group by customer_number having count(order_number) = ( select count(order_number) from Orders group by cus
阅读全文
摘要：尋找用戶推薦人 select name from customer where referee_id <> 2 or referee_id is null
阅读全文
摘要：至少有5名直接下屬的經理 子查詢 select `name` from Employee where id in ( select managerId from Employee group by managerId having count(managerId) >= 5 ) 自連接 select
阅读全文
摘要：游戏玩法分析 select round(avg(a.event_date is not null), 2) as fraction from (select player_id, min(event_date) as event_date from activity group by player_
阅读全文
摘要：游戏玩法分析 select player_id , min(event_date) as first_login from Activity group by player_id order by player_id asc ==
阅读全文
摘要：行程和用戶 SELECT t.`request_at` AS `Day`, ROUND(SUM(IF(t.status = 'completed', 0, 1))/COUNT(t.status) ,2) AS `Cancellation Rate` FROM Trips AS t LEFT JOIN
阅读全文
摘要：上升的温度 date_add（interval expr type） 使用日期相加函数 select w1.id from Weather w1 left join Weather w2 on w1.recordDate = date_add(w2.recordDate, interval 1 da
阅读全文
摘要：删除重复的电子邮箱 mysql 来说，inner join 是在做笛卡尔积 delete p1 from Person p1 inner join Person p2 on p1.email = p2.email and p1.Id > p2.Id delete p1 FROM person p1
阅读全文
摘要：部门工资前三高的所有员工 select d.name as Department, e.name as Employee, e.salary as Salary from Employee e left join Department d on e.departmentId = d.Id where
阅读全文
摘要：部门工资最高的员工 select d.name as Department, e.name as Employee, e.salary as Salary from Employee e left join Department d on e.departmentId = d.id where (e
阅读全文
摘要：从不订购的客户 select c.Name as Customers from Customers c left join Orders o on c.Id = o.CustomerId where o.CustomerId is null select customers.name custome
阅读全文
摘要：查找重复的电子邮箱 对于mysql来说 inner join 就是在做笛卡尔积 select email as Email from Person group by email having count(email) > 1 select email as Email from ( select e
阅读全文
摘要：超过经理收入的员工 select e1.name as Employee from Employee e1, Employee e2 where e1.managerId = e2.id and e1.salary > e2.salary select e1.name as Employee fro
阅读全文
摘要：连续出现的数字 select distinct l1.num as ConsecutiveNums from Logs l1, Logs l2, Logs l3 where l1.id = l2.id - 1 and l2.id = l3.id - 1 and l1.num = l2.num and
阅读全文
摘要：分数排名 select s1.score, count(distinct s2.score) as `rank` from Scores as s1, Scores as s2 where s1.score <= s2.score group by s1.id order by s1.score d
阅读全文
摘要：第N高的薪水 CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN declare T int default 0; SET T = N-1; RETURN ( # Write your MySQL query statement
阅读全文
浙公网安备 33010602011771号