weixin_43859562的博客

SQL题：求每个自然周的金额汇总和同一种商品被购买的间隔

求过去半年内连续30天以上每天都有1000元以上成交的商铺

每日sql - 判断+聚合现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0datadrop table if exists `user_profile`;drop table if exists `question_practice_detail`;drop table if exists `question_detail`;CREATE TABLE `user_profile` (`id`

题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。drop table if exists user_profile;CREATE TABLE `user_profile` (`id` int NOT NULL,`device_id` int NOT NULL,`gender` varchar(14) NOT NULL,`age` int ,`university` varchar(32) NOT NULL,`gpa` float,`active_day

用户两天留存率数据列：id device_id quest_id result dateselect count(date2) / count(date1) as avg_retfrom(select distinct d1.device_id, d1.date as date1, d2.date as date2from question_practice_detail d1left join(select distinct device_id, datefrom question_

每日sql 1：求好友申请通过率和拥有好友最多的用户背景模拟两张表。1为好友申请表，2为申请通过表问题1：好友申请通过率ddlCreate table If Not Exists friend_request ( sender_id INT NOT NULL, send_to_id INT NULL, request_date DATE NULL);Create table If Not Exists request_accepted ( requester_id INT NOT NULL, ac

每日sql-求2016年成功的投资总和背景2016年成功的投资条件：1.2015年投保额至少与另外一个投保人在2015年投保的额度相同2.此次投保的坐标必须是独一无二的。DDLCREATE TABLE IF NOT EXISTS insurance (PID INTEGER(11), TIV_2015 NUMERIC(15,2), TIV_2016 NUMERIC(15,2), LAT NUMERIC(5,2), LON NUMERIC(5,2) );insert into insurance

每日sql-统计各个专业人数（包括专业人数为0的）DDLCREATE TABLE IF NOT EXISTS student (student_id INT,student_name VARCHAR(45), gender VARCHAR(6), dept_id INT);CREATE TABLE IF NOT EXISTS department (dept_id INT, dept_name VARCHAR(255));insert into student (student_id, student_

每日sql–统计员工近三个月的总薪水（不包括最新一个月）DDLCreate table If Not Exists Employee (Id int, Month int, Salary int);insert into Employee (Id, Month, Salary) values (1, 1, 20);insert into Employee (Id, Month, Salary) values (2, 1, 20);insert into Employee (Id, Month, Sala

每日sql 1-员工奖金求出奖金少于1000的员工名字和奖金DDLCreate table If Not Exists Employee (EmpId int, Name varchar(255), Supervisor int, Salary int);Create table If Not Exists Bonus (EmpId int, Bonus int);insert into Employee (EmpId, Name, Supervisor, Salary) values (3, 'B

每日sql -查询至少有5名下属的经理背景需求查询至少有5名下属的经理DDLCreate table If Not Exists Employee (Id int, Name varchar(255), Department varchar(255), ManagerId int);insert into Employee (Id, Name, Department, ManagerId) values (101, 'John', 'A', null);insert into Employee (

背景有一张用户购买记录表.现在我们需要找出所有的特殊用户.特殊用户的定义如下:在当前购买时间的近7天内(含当天)购买次数超过3次(含),且近7天的购买金额超过1000的用户即为特殊用户.数据--创建测试表create table aaa001(user_id int,buy_date varchar(20),amount int);--插入测试数据(102和104为特殊用户)insert into aaa001 (user_id,buy_date,amount) values

不同公司员工工资中位数源数据插入数据Create table If Not Exists Employee (Id int, Company varchar(255), Salary int);insert into Employee (Id, Company, Salary) values (1, 'A', 2341);insert into Employee (Id, Company, Salary) values (2, 'A', 341);insert into Employee (Id,

用户分析有一张游戏用户表：用户id，设备id，登录时间、登录游戏数量建表语句和数据：Create table If Not Exists Activity (player_id int, device_id int, event_date date, games_played int);insert into Activity (player_id, device_id, event_date, games_played) values (1, 2, '2016-03-01', 5);insert

求出租车取消率有两张表，一张Trips表是所有出租车行程的信息，包括行程id、乘客id、司机id、行程状态、日期。一张Users表是所有用户包括司机、乘客的信息表，包括用户id、是否被禁止、身份。现求：‘2019-10-01’ 到 '2019-10-03’这段时间内，非禁止用户（包括乘客、司机）的取消率。取消率 = （非禁止用户的取消订单量）/非禁止用户的所有订单量表和数据生成语句：Create table If Not Exists Trips (Id int, Client_Id int

每日sql --求今天比昨天气温高的ID建表语句Create table If Not Exists Weather (Id int, RecordDate date, Temperature int);insert into Weather (Id, RecordDate, Temperature) values (1, '2015-01-01', 10);insert into Weather (Id, RecordDate, Temperature) values (2, '2015-01-02'

每日sql --删除一个表中重复的邮箱地址，只保留ID最小的建表语句如下Create table If Not Exists Person (Id int, email varchar(255));insert into Person (Id, email) values (1, 'john@example.com');insert into Person (Id, email) values (2, 'bob@example.com');insert into Person (Id, email) v

部门最高工资两个表，一张Employee一张Department建表语句：Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int);Create table If Not Exists Department (Id int, Name varchar(255));insert into Employee (Id, Name, Salary, DepartmentId) va

找重复邮箱和没下过单的顾客一、找重复邮箱二、没下过单的顾客一、找重复邮箱写sql找出Person表重复的邮箱。select Email from Person group by Email having count(*)>1;二、没下过单的顾客网站一张customers表放顾客信息， 一张orders订单信息，求从未下过单的顾客。Create table If Not Exists Customers (Id int, Name varchar(255));Create tab

每日sql-连续出现的数字和薪水超过经理的员工一、写sql找出连续出现三次的数字select * from Logs i1,Logs i2, Logs i3 where i3.Id = i2.Id+1 andi3.Num = i2.Num and i2.Id = i1.Id +1 and i2.Num=i1.Num;二、薪水超过经理的员工select a.name from employee_5 a join employee_5 b on a.managerid=b.id whe

分数排名用sql实现分数排名。如果两个分数相同则相同排名，下个为排名连续值，无间隔。一、用子查询

第二高薪水和第n高薪水一、不用row_number开窗函数1.1求第二高的薪水1.使用order by xx desc +limit 1,1分页查询select salary from employee order by salary desc limit 1,1;2.使用max函数select max(salary) from employee where salary < (select max(salary) from employee);1.2 求第n高的薪水1.使用or

sum(math) over() as sum1以所有的行的math进行sum计算sum(math) over(order by math)按math排序后，把小于和等于math当前行数值的全部sum计算sum(math) over(order by math RANGE BETWEEN unbounded preceding AND CURRENT ROW)–按math排序后，把小于和等于math当前行数值的全部sum计算sum(math) over(order by math ROWS BE

1.构建临时表 id， 登录日期，注册日期2.求登录日期-注册日期大于7with t1(select id,login_date, b.resgister_date,date_diff(login,resgister_date) as rkfrom (select distinct id, login_Date from a1) aleft join (select id,min(date) as resgister_date from b1 group by id)b) on a.id=.

1.筛选出七天内数据,group by id,date2.rank() over() as rk 根据id排序id，date3.date -rk 为同一个数，select date -rk .group by id date-rk having count(*)4.group id具体代码：select t4.id from(select id, date_add(t2.dt-t2.rk) as rk2from(select t1.id, t1.dt, rank() over(p.

实习日志，今天在写一个子查询集的时候忘记写别名了系统没提醒具体错误只提醒末尾有错误，耽误了半小时故写下来提醒自己，写sql千万要记得写别名，不要怕麻烦

最近在学Oracle的时候看到有一条是这样的：update student.stuinfo t set (age, idnumber) = (select age, idnumber from student.stuinfo_2018 b where b.stuid = t.stuid) where exists (select 1 from student.stuinfo_2018 b where b.stuid = t.stuid

1.修改密码// 8以前 set password for 用户名@localhost = password('新密码'); //8以后 ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';2.赋权操作//8以前：grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;//8以后create user root@'%' ide