随笔分类 -  数据库

LC 1667. Fix Names in a Table (字符串)
摘要：select user_id, concat(upper(substring(u.name,1,1)),lower(substring(u.name,2))) as name from users u order by user_id -- CONCAT(str1, str2)：字符连接函数 -- 阅读全文
posted @ 2020-12-09 09:34 feibilun 阅读(163) 评论(0) 推荐(0)
1527. Patients With a Condition (like/REGEXP)
摘要：link select * from patients where conditions REGEXP BINARY '^DIAB1|\\sDIAB1' select * from patients where conditions like BINARY 'DIAB1%' or condition 阅读全文
posted @ 2020-10-01 09:56 feibilun 阅读(107) 评论(0) 推荐(0)
LC 1517. Find Users With Valid E-Mails (正则)
摘要：link select * from users where mail regexp '^[a-zA-Z][a-zA-Z0-9_/\\.\\-]*(@leetcode.com)' 阅读全文
posted @ 2020-09-23 10:16 feibilun 阅读(186) 评论(0) 推荐(0)
LC 1501. 可以放心投资的国家 (left的运用)
摘要：link select name as country from ( select name,sum(duration)/count(duration) as avg from ( select t.name,t.id, c1.caller_id, c1.callee_id, c1.duration 阅读全文
posted @ 2020-09-18 09:39 feibilun 阅读(126) 评论(0) 推荐(0)
LC 1485. 按日期分组销售产品 (group_concat 用法)
摘要：link select sell_date, count(distinct product) as num_sold, group_concat(distinct product order by product asc separator ',') as products from activit 阅读全文
posted @ 2020-09-17 11:04 feibilun 阅读(147) 评论(0) 推荐(0)
LC 1479. 周内每天的销售情况
摘要：link select i.item_category as category, sum(if(date_format(order_date,'%W')='Monday',quantity,0)) as 'Monday', sum(if(date_format(order_date,'%W')='T 阅读全文
posted @ 2020-09-16 09:21 feibilun 阅读(125) 评论(0) 推荐(0)
LC 1384. Total Sales Amount by Year
摘要：link # Write your MySQL query statement below select t3.product_id,p.product_name,year as report_year,amount as total_amount from ( select product_id, 阅读全文
posted @ 2020-05-01 10:57 feibilun 阅读(336) 评论(0) 推荐(0)
LC 1369. Get the Second Most Recent Activity
摘要：link Solution1: 自定义变量，union all # Write your MySQL query statement below select username,activity,startDate,endDate from ( select t1.*, @rk:=if(@name= 阅读全文
posted @ 2020-04-06 22:26 feibilun 阅读(280) 评论(0) 推荐(0)
LC 1336. Number of Transactions per Visit
摘要：link select t3.id as transactions_count, if(t5.cnt is null,0,t5.vcnt) as visits_count from ( select t0.id from ( select @num:=@num+1 as id from (selec 阅读全文
posted @ 2020-03-05 18:53 feibilun 阅读(319) 评论(0) 推荐(0)
LC1127. 用户购买平台
摘要：题目链接 题解： 这道题太复杂了。。。主要是前两列最开始比较难想怎么做。 # Write your MySQL query statement below select t4.spend_date, t4.platform, if(t6.spend_date is null,0,t6.amount) 阅读全文
posted @ 2020-02-06 20:05 feibilun 阅读(297) 评论(0) 推荐(0)
LC 1308. Running Total for Different Genders
摘要：题目链接 题解1： 先排序，然后用自定义变量记录累计的score。 select gender,day,total from( select day, @s:=(if(gender=@g,@s+score_points,score_points)) as total, @g:=gender as g 阅读全文
posted @ 2020-02-06 18:04 feibilun 阅读(284) 评论(0) 推荐(0)