链接1
select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100;
CASE WHEN age < 18 THEN ‘未成年人’ WHEN age < 60 THEN ‘成年人’ ELSE ‘老年人’ END
MySQL中ISNULL只是用来判断是否为空,不能实现替换功能,所以用IFNULL代替,语法和上面的ISNULL一样。
SELECT * FROM table WHERE RAND() <= .3,这样可以抽出约30%的数据,然后再用TOP或者LIMIT子句。
插入
INSERT INTO table_name (col1, col2, ...) VALUES (value1, value2, ...)
INSERT INTO table_name (col1, col2, ...) SELECT col1, col2, ... FROM table_name WHERE search_condition
UPDATE table_name SET col1 = value1 [, col2 = value2] ...
WHERE search_condition
链接2
知乎链接 2
学生表:tb_student(name:学生姓名,id:学号,class:班级,in_time:入学时间,age:年龄,sex:性别,major:专业)
解析:写一个子查询,从score表中得到以学号分组的学生各科平均分。命名为aaa,将其与学生表内联结。再以class分组,得到以class分组的各班学生的平均分,最后通过case语句,sum聚合函数得到平均分>80分的计数,和所占各班总人数比例。
SELECT a.class,
sum(case when aaa.x > 80 then 1 else 0 end)as num_80 ,
(sum(case when aaa.x > 80 then 1 else 0 end)/count(*))as proportion
from tb_student a
INNER JOIN (SELECT id,avg(score)as x from tb_score GROUP BY id)as aaa
on a.id = aaa.id
GROUP BY class;
case语句也可以用if语句来代替
写法2:
SELECT a.class,count(if(aaa.avg>80,true,null)) as numover80,
count(if(aaa.avg>80,true,null))/count(a.id) as total
from tb_student a
INNER JOIN (SELECT id,avg(score)as x from tb_score GROUP BY id)as aaa
on a.id = aaa.id
GROUP BY class;
滴滴 4
编写一个 SQL 查询,找出每个部门工资第二高的员工。
select Departmentid,Name,Salary,rn
from(select *,
row_number()over(PARTITION by Departmentid
ORDER BY Salary desc) as rn
from Employee)as a
WHERE rn=2
自己未经测试的伪代码:
select Departmentid,id,name,Salary from Employee group by Departmentid order by Salary desc limit 1,1;
5.4
(4)假设4张表存放在 MySQL 数据库中,查询“北京佰初数据有限公司”过去7天每天的新入职员工数量,按入职日期(date_of_entry)倒序排列.
(4)这个题需要用到日期函数 dateadd()和getdate()
select date_of_entry,count(staff_id) as num
from table_staff
where date_of_entry>=dateadd(day,-7,getdate())
and company_id in
(select company id from table_company where company_name ='北京佰初数据有限公司')
group by date of entry
order by date of entry desc;
牛客网(筛选)
【1】考察日期函数
select *
from BORROW
where datediff(dd,RDATE,getdate())=0
datediff(dd,RDDATE,getdate())==0含义,即返回以日为单位(dd),和当前日期(getdate)相差为0日的RDDATE
这里用这个函数的意义在于,RDDATE包括日期和时间,这里因为有时间,和getdate不能直接比较,所以用datediff转换为范围。
2 有一张学生成绩表sc(sno 学号,class 课程,score 成绩),请查询出每个学生的英语、数学的成绩(行转列,一个学生只有一行记录)。
select sno,
sum(if(class='english',score,0)) as english,
sum( if(class='math',score,0) ) as math
from sc
where class in('english','math')
group by sno
leetcode
2
DATEDIFF 函数,可以计算两者的日期差
DATEDIFF(‘2007-12-31’,‘2007-12-30’); # 1
DATEDIFF(‘2010-12-30’,‘2010-12-31’); # -1
select a.Id
from weather a, weather b
where a.Temperature > b.Temperature
and datediff(a.RecordDate,b.RecordDate) = 1
3
写法一:
update salary set
sex =if (sex=‘f’,‘m’,‘f’);
写法二:
update salary set
sex = case sex when “m” then “f” else “m” end;
找出第n高的薪水,是不是可以这样写:
select distinct salary, ranking
from(select salary, rank() over(order by salary desc) as ranking
from employee)
where ranking = n