‘壹’ 通过输入时间段，查询出勤率，迟到次数，早退次数，旷工次数，用的ssh集成，oracle数据库，怎么写查询方法
--建表sql
Create Table mhl.manualsign --考勤信息表
(ms_id int,--非空 种子，自增1 签卡Id
user_id Varchar(50) not null,--自增1 签卡Id
ms_time date not null,-- 非空 签卡时间
ms_desc Varchar(200) not null,-- 非空 签卡备注
ms_tag int not null-- 非空 签卡标记 （1,上班打卡，0，下班打卡）
);
create table mhl.tbl_worktime --上下班时间表
(wt_id int not null,-- 非空 种子，自增1 工作时间Id
wt_uptime Varchar(50) not null,-- 非空 上班时间
wt_downtime Varchar(50) not null-- 非空 下班时间
);
-- mhl.manualsign插入数据
truncate table mhl.manualsign;
insert into mhl.manualsign
select 1,'001', to_date('2012-01-01 08:04:54','yyyy-mm-dd hh24:mi:ss'),'001','1' from al
union
select 2,'001', to_date('2012-01-01 17:04:54','yyyy-mm-dd hh24:mi:ss'),'001','0' from al
union
select 3,'001', to_date('2012-01-02 08:04:54','yyyy-mm-dd hh24:mi:ss'),'001','1' from al
union
select 4,'001', to_date('2012-01-02 18:04:54','yyyy-mm-dd hh24:mi:ss'),'001','0' from al
union
select 5,'001', to_date('2012-01-03 09:04:54','yyyy-mm-dd hh24:mi:ss'),'001','1' from al
union
select 6,'001', to_date('2012-01-03 18:04:54','yyyy-mm-dd hh24:mi:ss'),'001','0' from al
union
select 7,'001', to_date('2012-01-05 08:04:54','yyyy-mm-dd hh24:mi:ss'),'001','1' from al
union
select 8,'001', to_date('2012-01-05 18:04:54','yyyy-mm-dd hh24:mi:ss'),'001','0' from al
union
select 9,'001', to_date('2012-01-07 08:04:54','yyyy-mm-dd hh24:mi:ss'),'001','1' from al
union
select 10,'001', to_date('2012-01-07 18:04:54','yyyy-mm-dd hh24:mi:ss'),'001','0' from al;
insert into mhl.manualsign
select 11,'002', to_date('2012-01-01 08:04:54','yyyy-mm-dd hh24:mi:ss'),'002','1' from al
union
select 12,'002', to_date('2012-01-01 18:04:54','yyyy-mm-dd hh24:mi:ss'),'002','0' from al
union
select 13,'002', to_date('2012-01-02 08:04:54','yyyy-mm-dd hh24:mi:ss'),'002','1' from al
union
select 14,'002', to_date('2012-01-02 18:04:54','yyyy-mm-dd hh24:mi:ss'),'002','0' from al
union
select 15,'002', to_date('2012-01-03 09:04:54','yyyy-mm-dd hh24:mi:ss'),'002','1' from al
union
select 16,'002', to_date('2012-01-03 18:04:54','yyyy-mm-dd hh24:mi:ss'),'002','0' from al
union
select 17,'002', to_date('2012-01-05 08:04:54','yyyy-mm-dd hh24:mi:ss'),'002','1' from al
union
select 18,'002', to_date('2012-01-05 18:04:54','yyyy-mm-dd hh24:mi:ss'),'002','0' from al
union
select 19,'002', to_date('2012-01-07 08:04:54','yyyy-mm-dd hh24:mi:ss'),'002','1' from al
union
select 20,'002', to_date('2012-01-07 18:04:54','yyyy-mm-dd hh24:mi:ss'),'002','0' from al;
--mhl.tbl_worktime插入数据
truncate table mhl.tbl_worktime;
insert into mhl.tbl_worktime
select 1,'2012-01-01 09:00:00','2012-01-01 18:00:00' from al;
----通过输入时间段，查询出勤率，迟到次数，早退次数，旷工次数
--设置入参：开始时间为：2012-01-01 结束时间为 2012-01-07
select a.user_id,
(select Count(1) / 2
From mhl.manualsign c
where c.user_id = a.user_id
and c.ms_time >= To_Date('2012-01-01', 'yyyy-mm-dd')
and c.ms_time <= To_Date('2012-01-07', 'yyyy-mm-dd')) /
(select To_Date('2012-01-07', 'yyyy-mm-dd') -
To_Date('2012-01-01', 'yyyy-mm-dd')
from al) 出勤率,
(Select Count(1)
From mhl.manualsign d
where a.user_id = d.user_id
and d.ms_tag = '1'
and To_char(d.ms_time, 'hh24:mi:ss') > To_char('09:00:00')
and d.ms_time >= To_Date('2012-01-01', 'yyyy-mm-dd')
and d.ms_time <= To_Date('2012-01-07', 'yyyy-mm-dd')
and d.user_id = a.user_id) 迟到次数,
(Select Count(1)
From mhl.manualsign d
where a.user_id = d.user_id
and d.ms_tag = '0'
and to_char(d.ms_time, 'hh24:mi:ss') < to_char('18:00:00')
and d.ms_time >= To_Date('2012-01-01', 'yyyy-mm-dd')
and d.ms_time <= To_Date('2012-02-01', 'yyyy-mm-dd')
and d.user_id = a.user_id) 早退次数,
round(round(1 + To_Date('2012-01-07', 'yyyy-mm-dd') -
To_Date('2012-01-01', 'yyyy-mm-dd')) -
(select count(1) / 2
from mhl.manualsign f
where f.user_id = '001'
and f.ms_time >= To_Date('2012-01-01', 'yyyy-mm-dd')
and f.ms_time <= 1 + To_Date('2012-01-07', 'yyyy-mm-dd'))) 旷工次数
from mhl.manualsign a
where a.ms_time between To_Date('2012-01-01', 'yyyy-mm-dd') and /*a.ms_time <=*/
To_Date('2012-01-07', 'yyyy-mm-dd')
group by a.user_id;
‘贰’ 求助高手啊，出勤率sql语句怎么写
你表结构是什么样的啊，现在只能给你个思路了，比如这个月全勤是26天，超过或等于26天的就100%，没超过的，就出勤天数/26就OK了
‘叁’ 员工考勤统计怎么写sql语句，查询出勤率，迟到次数，早退次数，旷工次数
你的给子段，给点模拟数据
‘肆’ sql server中查询每个班级的出勤率(出勤人数/班级人数),有学员信息表和出勤表(到校和没到校的都有记录)怎么
每个子查询，查询出来的结果都有可能是多个，因为你分组了。他会把每个班级的人数进行统计，结果就是多个
这样就可以了：
select rount(t1.实际出勤人数/t2.班级人数,2) from
(select count(goschool) 实际出勤人数 ,cclass from tx_scheck where goschool='到校' group by cclass) t1
inner join
(select count(*) 班级人数 , sclass from tx_xsxxb a where a.ifgoschool='在校' group by sclass) t2 on t1.cclass = t2.sclass
‘伍’ sql 单表查询语句 求高手帮忙
--总出席的天数
DECLARE @day_count int
select @day_count=count(*) from
(select substring(convert( varchar(10),signtime,120),1,10) a from sign group by substring(convert( varchar(10),signtime,120),1,10)) s;
--考勤表中出现的人
select m1.userid,m1.应考天数,m2.签到次数,m3.签退次数,ISNULL(m4.迟到次数,0) 迟到次数,ISNULL(m5.早退次数,0) 早退次数 from (
select userid,@day_count 应考天数 from sign group by userid) m1 left join (
--每人签到的次数
select count(signid) 签到次数, userid from sign where signflag=1 group by userid ) m2 on m1.userid=m2.userid left join(
--每人签退的次数
select count(signid) 签退次数, userid from sign where signflag=0 group by userid ) m3 on m1.userid=m3.userid left join (
--每人迟到次数
select count(signid) 迟到次数, userid from sign where signflag=1 and convert(varchar(5) ,signtime,114)>'08:00' group by userid ) m4 on m1.userid=m4.userid left join (
--每人早退次数
select count(signid) 早退次数, userid from sign where signflag=0 and convert(varchar(5) ,signtime,114)<'18:00' group by userid) m5 on m1.userid=m5.userid
--把这些语句再用left join 连在一起想要啥就有啥了,都到这步了计算你自己来吧
‘陆’ SQL 多表连接效率是否高
现在大多用的是基于成本的优化规则，对于数据的查询效率，主要依赖于：
总的数据量
索引和统计
检索的数据量
与内连接、左联接等等关系不是太大。
‘柒’ sql server 循环插入语句
--可以不用循环哦,先建好临时表,和table201401——table201412的表
--下面的代码是把满足table2014%这个条件的表分组统计后插入到临时表
execsp_MSforeachtable
@command1="insertinto#tempselect姓名,substring('?',13,6),count(日期)
from?groupby姓名",
@whereand="Ando.Namein(SelectnameFromsys.objectsWhereType='U'And
Namelike'table2014%'
)"
--当然循环也行
Declare@NameVarchar(100)
Declare@sqlVarchar(1000)
DECLARECurCURSORFOR
SelectnameFromsys.objectsWherenamelike'table2014%'
OPENCur
FETCHNEXTFROMCurINTO@Name
WHILE@@FETCH_STATUS=0
BEGIN
Set@sql='insertinto#tempselect姓名,right('''+@Name+''',6),count(日期)
from'+@Name+'groupby姓名'
Exec(@sql)
FETCHNEXTFROMCurINTO@Name
END
CLOSECur
DEALLOCATECur
select*from#temp
‘捌’ 谁能帮我把下面报表用SQL语句写出来谢谢了！必定重赏！
这个看不懂你写什么啊？你给表我们看看才知道怎么拿赏金啊！
‘玖’ 跪求哪位高手帮我用SQL语句解决下面的问题，用SQL语句写出来谢谢！
你的表结构呢？
难道说，是想要 select * from 表名;
‘拾’ sql的复杂查询问题：
no，date，attendance
12 2010-10-10 Y
13 2010-11-12 N
1)
思路：先查工资大于8000，再并列查询，小于5次
2010年小于5次的
select A.no count(A.no) from Attend A where attendance group by substring(date,0,4) having count(A.no)<5
故，综上得出结论
select E.no,E.name from Employee E left join Wage W on E.no=W.no left join （select A.no count(A.no) from Attend A where attendance group by substring(date,0,4) having count(A.no)<5）A on E.no=A.no where W.amount > 8000;
2)
思路：先查工资平均数，分别查不同的日期即可
select W.amount/sum(W.amoun),A.no,A.attendance/sum(A.attendance) from Attend A left join Wage W on W.no=A.no where substring(date,0,6)='201212' group by A.no
select W.amount/sum(W.amoun),A.no,A.attendance/sum(A.attendance) from Attend A left join Wage W on W.no=A.no where substring(date,0,6)='201201' group by A.no