牧码的博客

原创 LeetCode（数据库）- The Category of Each Member in the Store

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码WITH t AS(SELECT member_id, COUNT(IFNULL(charged_amount, NULL)) * 100 / COUNT(*) scoreFROM Visits LEFT JOIN Purchases USING(visit_id)GROUP BY 1)SELECT m.member_id, m.name, CASE WHEN score IS NULL THEN 'Bron

原创 LeetCode（数据库）- 面试中被录取的候选人

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码SELECT candidate_idFROM Candidates c JOIN Rounds r ON years_of_exp > 1 AND c.interview_id = r.interview_idGROUP BY candidate_idHAVING SUM(score) > 15...

原创 LeetCode（数据库）- 账户余额

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码SELECT account_id, day, SUM(IF(type = 'Withdraw', -amount, amount)) OVER(PARTITION BY account_id ORDER BY account_id, day) balanceFROM Transactions

原创 Java - 类变量（static）初始化过程案例分析

案例一class SingleTon { private static SingleTon singleTon = new SingleTon(); public static int count1; public static int count2 = 0; private SingleTon() { // 下面 ++ 之前, count1、count2 都是零值(默认)【准备阶段】 count1++; count2++; // 此时 coun

原创 LeetCode（数据库）- 上级经理已离职的公司员工

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码-- 解决方案(1)select a.employee_id from Employees a where a.salary < 30000 AND a.manager_id is not null AND not exists (select 1 from Employees b where a.manager_id = b.employee_id)ORDER BY a.employee

原创 LeetCode（数据库）- First and Last Call On the Same Day

题目链接：点击打开链接题目大意：略。解题思路：注意是‘每天’的第一个和最后一个电话。AC 代码with a as ( SELECT caller_id, recipient_id, call_time FROM Calls UNION ALL SELECT recipient_id caller_id, caller_id recipient_id, call_time FROM Calls)SELECT DISTINCT a.ca

原创 LeetCode（数据库）- Drop Type 1 Orders for Customers With Type 0 Orders

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码-- 解决方案(1)select distinct a.order_id, a.customer_id, a.order_type from Orders as a left join Orders as bon a.customer_id = b.customer_id and a.order_type <> b.order_typewhere b.order_type is null or b.o

原创 LeetCode（数据库）- 丢失信息的雇员

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码-- 解决方案(1)with t as (select employee_id from Employeesunion allselect employee_id from Salaries)select employee_id from tgroup by employee_idhaving count(employee_id)=1order by employee_id-- 解决方案(2

原创 LeetCode（数据库）- 查询具有最多共同关注者的所有两两结对组

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码-- 解决方案(1)select a.user1_id,a.user2_id from (select a.user_id user1_id, b.user_id user2_id, rank() over(order by COUNT(1) desc) rn from Relations a, Relations b

原创 LeetCode（数据库）- Low-Quality Problems

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码SELECT problem_idFROM ProblemsWHERE likes / (likes + dislikes) < 0.6ORDER BY 1

原创 LeetCode（数据库）- 统计实验的数量

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码WITH t1 AS(SELECT 'Android' platformUNION ALLSELECT 'IOS'UNION ALLSELECT 'Web'),t2 AS(SELECT 'Reading' experiment_nameUNION ALLSELECT 'Sports'UNION ALLSELECT 'Programming'),t3 AS(SELECT platform, exp

原创 LeetCode（数据库）- The Winner University

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码WITH t1 AS(SELECT COUNT(*) s1 FROM NewYork WHERE score >= 90),t2 AS(SELECT COUNT(*) s2 FROM California WHERE score >= 90)SELECT CASE WHEN s1 > s2 THEN 'New York University'WHEN s1 < s2 THEN 'C

原创 LeetCode（数据库）- 富有客户的数量

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码-- 解决方案(1)WITH t AS(SELECT customer_id FROM Store GROUP BY customer_id HAVING MAX(amount) > 500)SELECT COUNT(*) rich_countFROM t-- 解决方案(2)SELECT COUNT(DISTINCT customer_id) rich_countFROM StoreWHERE

原创 LeetCode（数据库）- Leetcodify Friends Recommendations

题目链接：点击打开链接题目大意：题目当中测试用例描述是有问题的，这个注意下，但是不影响最终判断，判断里的数据是正确的。解题思路：奇怪为啥我注释的这条代码 NOT IN 数据 (2, 3) 的情况被过滤掉，不科学，函望大佬指点下，目前只能怀疑题目语法解析问题。AC 代码WITH t AS(SELECT * FROM Listens GROUP BY 1, 2, 3),tt AS(SELECT user1_id, user2_id FROM Friendship UNION AL

原创 LeetCode（数据库）- Strong Friendship

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码WITH t AS(SELECT * FROM Friendship UNION SELECT user2_id, user1_id FROM Friendship)SELECT f1.user1_id, f1.user2_id, COUNT(*) common_friendFROM t f1, t f2, t f3WHERE f1.user1_id = f2.user1_id AND f1.user2_id

原创 LeetCode（数据库）- 兴趣相同的朋友

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码-- 解决方案(1)select distinct a.user1_id, a.user2_id from Friendship a, Listens b, Listens c where a.user1_id = b.user_id AND a.user2_id = c.user_id AND b.song_id = c.song_id AND b.day = c.day group by

原创 LeetCode（数据库）- Confirmation Rate

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码SELECT s.user_id, IFNULL(confirmation_rate, 0) confirmation_rateFROM Signups s LEFT JOIN (SELECT user_id, ROUND(COUNT(IF(action = 'confirmed', 1, NULL)) / COUNT(*), 2) confirmation_rate FROM Confirmations GROUP

原创 LeetCode（数据库）- Users That Actively Request Confirmation Messages

题目链接：点击打开链接题目大意：略。解题思路：注意 TIMESTAMPDIFF 函数。AC 代码SELECT DISTINCT c1.user_idFROM Confirmations c1 JOIN Confirmations c2 ON c1.user_id = c2.user_id AND c1.time_stamp < c2.time_stampWHERE TIMESTAMPDIFF(SECOND, c1.time_stamp, c2.time_stamp)

原创 LeetCode（数据库）- 按分类统计薪水

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码WITH t AS(SELECT 'Low Salary' categoryUNION ALLSELECT 'Average Salary'UNION ALLSELECT 'High Salary'),tt AS(SELECT CASE WHEN income < 20000 THEN 'Low Salary'WHEN income > 50000 THEN 'High Salary'EL

原创 APP - 查询全国医院各科室排行榜（二）

原创 LeetCode（数据库）- 页面推荐Ⅱ

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码WITH t AS(SELECT user1_id user_id FROM FriendshipUNION SELECT user2_id FROM Friendship),tt AS(SELECT user_id, user2_id friend_id FROM t JOIN Friendship ON user_id = user1_id UNIONSELECT user_id, user1_id F

原创 LeetCode（数据库）- 将工资相同的雇员分组

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码-- 解决方案(1)select A.employee_id, A.name, A.salary, dense_rank() over(order by A.salary) team_id from (select a.*, COUNT(1) over(partition by a.salary) cn from Employees a) A WHERE A.cn &g

原创 LeetCode（数据库）- 2020年最后一次登录

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码-- 解决方案(1)SELECT user_id AS 'user_id', MAX(time_stamp) AS 'last_stamp'FROM LoginsWHERE YEAR(time_stamp) = '2020'GROUP BY user_id;-- 解决方案(2)SELECT user_id, time_stamp last_stampFROM (SELECT user_id, t

原创 LeetCode（数据库）- 计算特殊奖金

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码-- 解决方案(1)SELECT employee_id, CASEWHEN MOD(employee_id, 2) = 1 AND name NOT LIKE 'M%' THEN salaryELSE 0END AS bonus FROM Employees ORDER BY employee_id;-- 解决方案(2)SELECT employee_id, IF(LEFT(na

原创 LeetCode（数据库）- Orders With Maximum Quantity Above Average

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码SELECT order_idFROM OrdersDetailsGROUP BY order_idHAVING MAX(quantity) > ALL(SELECT AVG(quantity) FROM OrdersDetails GROUP BY order_id)

原创 LeetCode（数据库）- 转换日期格式

题目链接：点击打开链接题目大意：略。解题思路：日期格式化函数：DATE_FORMAT()%a 缩写星期名 %b 缩写月名 %c 月，数值 %D 带有英文前缀的月中的天 %d 月的天，数值(00-31) %e 月的天，数值(0-31) %f 微秒 %H 小时 (00-23) %h 小时 (01-12) %I 小时 (01-12) %i 分钟，数值(00-59) %j 年的天 (001-366) %k 小时 (0-23) %l 小时 (1-12) %M 月名 %

原创 LeetCode（数据库）- 可疑银行账户

题目链接：点击打开链接题目大意：略。解题思路：略。AC 代码WITH t AS(SELECT t.account_id, SUM(amount) total, MONTH(day) mon, max_incomeFROM Transactions t JOIN Accounts a USING(account_id)WHERE type = 'Creditor'GROUP BY 1, 3HAVING total > max_incomeORDER BY acco