SQL语句笔录

SQL语句查找每一位员工,每一天打卡的次数,并按照日期排序

select 员工姓名,count(*) 每天打卡次数,convert(varchar(10),员工打卡时间,120) 当天时间 
from tableC 
group by 员工ID,员工姓名,convert(varchar(10),员工打卡时间,120) 
order by convert(varchar(10),员工打卡时间,120) desc

 

每天打卡两次的员工才算正常,查找出有异常的员工

select 员工姓名 as 异常员工姓名,count(*) as 当天打卡次数,convert(varchar(10),员工打卡时间,120) as 员工打卡时间 
from tableC 
group by 员工姓名,convert(varchar(10),员工打卡时间,120) 
having count(*)<2

 

执行一条删除语句,当名称列名有相同时,只保留ID值最小的行

delete from tableA where ID not in (select minid from (select min(ID) as minid from tableA group by name) b)

同一访问时间里的记录条数大于5的IP地址插入另一表中

insert into table_B(IP地址,插入时间,状态)
select 访问IP,getdate(),1 from table_A group by 访问IP,访问时间  having count(*)>=5

四张表关联,取每个考核项目全三名的员工信息

select 员工姓名,上级部门,b.部门 as 所在部门,考核项目,分数 
from(
(select * from 
(select row_number() over (partition by 项目ID order by 分数 desc) 排名,* from 考核成绩表) t 
where 排名<=3) tt 
left join 考核项目表 a on tt.项目ID=a.项目ID
left join 员工表 b on b.员工ID=tt.员工ID
left join 部门表 c on c.部门=b.部门)