查询统计A表 Numbers 字段 且A表ClassUP1,ClassNext1,ClassUP2,ClassNext2字段为空的数据有多少条记录 如果leave
字段有值则不统计,但同时统计leave的字段值为1,2,3各有多少条. 1是有薪假,2病假,3事假
字段有值则不统计,但同时统计leave的字段值为1,2,3各有多少条. 1是有薪假,2病假,3事假
解决方案 »
- 【求助】在java中访问SQL Server数据库,出现这样的错误,是怎么回事
- 为什么需要访问服务器的共享目录后,才能从客户端连接上服务器
- *******************请问一个关于游标里使用随机数的问题*******************
- INSERT EXEC 语句不能嵌套。
- 让返回结果增加一列递增ID号
- 默认为主表字段
- 请问这样的数据排序该如何进行?大侠进来。
- 存储过程中exec('SELECT COUNT(*) FROM '+@tablename),如何返回这个count(*)
- 如何获得sql数据库中某个表内容的更新日期(最近一次)
- 急!急!150个表应该放在几个SQL Server7.0 的数据库文件里????各位大哥请帮忙!!!!
- TSQL语句,不显示某列的写法
- 最离当前时间最近的一条记录。
declare @A表 table
(Numbers int,ClassUP1 int,ClassNext1 int,
ClassUP2 int,ClassNext2 int)
insert into @A表
select 1,null,4,7,null union all
select 2,4,null,8,null union all
select 3,5,null,null,2 union all
select 4,6,8,null,null union all
select 5,7,9,null,nullselect
sum(case when ClassUP1 is null then 1 else 0 end) as ClassUP1,
sum(case when ClassNext1 is null then 1 else 0 end) as ClassNext1,
sum(case when ClassUP2 is null then 1 else 0 end) as ClassUP2,
sum(case when ClassNext2 is null then 1 else 0 end) as ClassNext2
from @A表/*判断空的个数
ClassUP1 ClassNext1 ClassUP2 ClassNext2
----------- ----------- ----------- -----------
1 2 3 4
*/
declare @table table (leave int)
insert into @table
select 1 union all
select 1 union all
select 2 union all
select 2 union all
select 2 union all
select 2 union all
select 3 union all
select 3 union all
select 3select
sum(case leave when 1 then 1 else 0 end) as 薪假,
sum(case leave when 2 then 1 else 0 end) as 病假,
sum(case leave when 3 then 1 else 0 end) as 事假
from @table/*
薪假 病假 事假
----------- ----------- -----------
2 4 3
*/不明白你这两个问题有什么关系?
levae值说明
/*
0在职 1是有薪假,2病假,3事假
我想要的结果就是怎么查询谁旷工,谁上了多少天班,一条记录为一天.考勤表前面还有一个日期字段
谁请假多少天,病假多少天,谁事假多少天.
*/
create table #员工表(number varchar(50),levae int)
insert into #员工表 values('001',0)
insert into #员工表 values('002',1)
insert into #员工表 values('003',0)
insert into #员工表 values('004',2)
insert into #员工表 values('005',0)
insert into #员工表 values('006',3)
insert into #员工表 values('007',0)
create table #考勤表(number varchar(50),ClassUP1 datetime ,ClassNext1 datetime,ClassUP2 datetime,ClassNext2 datetime )
insert into #考勤表 values(001, '2011-05-06 08:16:17.000','2011-05-06 08:16:17.000','2011-05-06 08:16:17.000','2011-05-06 08:16:17.000')
insert into #考勤表 values(002, null,null,null,null)
insert into #考勤表 values(003, '2011-05-06 08:16:17.000','2011-05-06 08:16:17.000',null,null)
insert into #考勤表 values(004, null,null,null,null)
insert into #考勤表 values(005 , null,null,null,null)
insert into #考勤表 values(006, null,null,'2011-05-06 08:16:17.000','2011-05-06 08:16:17.000')
insert into #考勤表 values(007, null,null,'2011-05-06 08:16:17.000','2011-05-06 08:16:17.000')
a.number,
sum(case levae when 0 then 1 else 0 end) as '在职' ,
sum(case levae when 0 then 1 else 0 end) as '有薪假',
sum(case levae when 0 then 1 else 0 end) as '病假',
sum(case levae when 0 then 1 else 0 end) as '事假'
from
#员工表 a join #考勤表 b
on
a.number=b.number
group by
a.number
结果一个都没有./*
number 在职 有薪假 病假 事假
-------------------------------------------------- ----------- ----------- ----------- -----------(0 row(s) affected)
*/
0在职 1是有薪假,2病假,3事假
我想要的结果就是怎么查询谁旷工,谁上了多少天班,一条记录为一天.考勤表前面还有一个日期字段
谁请假多少天,病假多少天,谁事假多少天.
*/
create table #员工表(number varchar(50),levae int)
insert into #员工表 values('001',0)
insert into #员工表 values('002',1)
insert into #员工表 values('003',0)
insert into #员工表 values('004',2)
insert into #员工表 values('005',0)
insert into #员工表 values('006',3)
insert into #员工表 values('007',0)
create table #考勤表(number varchar(50),ClassUP1 datetime ,ClassNext1 datetime,ClassUP2 datetime,ClassNext2 datetime )
insert into #考勤表 values('001', '2011-05-06 08:16:17.000','2011-05-06 08:16:17.000','2011-05-06 08:16:17.000','2011-05-06 08:16:17.000')
insert into #考勤表 values('002', null,null,null,null)
insert into #考勤表 values('003', '2011-05-06 08:16:17.000','2011-05-06 08:16:17.000',null,null)
insert into #考勤表 values('004', null,null,null,null)
insert into #考勤表 values('005' , null,null,null,null)
insert into #考勤表 values('006', null,null,'2011-05-06 08:16:17.000','2011-05-06 08:16:17.000')
insert into #考勤表 values('007', null,null,'2011-05-06 08:16:17.000','2011-05-06 08:16:17.000')
select
a.number,
sum(case levae when 0 then 1 else 0 end) as '在职' ,
sum(case levae when 0 then 1 else 0 end) as '有薪假',
sum(case levae when 0 then 1 else 0 end) as '病假',
sum(case levae when 0 then 1 else 0 end) as '事假'
from
#员工表 a join #考勤表 b
on
a.number=b.number
group by
a.number
--select * from #员工表
--select * from #考勤表
/*number 在职 有薪假 病假 事假
-------------------------------------------------- ----------- ----------- ----------- -----------
001 1 1 1 1
002 0 0 0 0
003 1 1 1 1
004 0 0 0 0
005 1 1 1 1
006 0 0 0 0
007 1 1 1 1(7 行受影响)*/drop table #员工表, #考勤表
想要的结果.能做到不
/*
number 上班天数 有薪假 病假 事假 旷工
-------------------------------------------------- ----------- ----------- ----------- ----------- ------
001 1 0 0 0 0
002 0 1 0 0 0
003 0 0 0 0 0.5
004 0 0 1 0 0
005 0 0 0 0 1
006 0 0 0 0.5 0
007 0 0 0 0.5 0
*/
number 上班天数 有薪假 病假 事假 旷工
--------------- ----------- ----------- ----------- ----------- ------
001 1 0 0 0 0
002 0 1 0 0 0
003 0 0 0 0 0.5
004 0 0 1 0 0
005 0 0 0 0 1
006 0 0 0 0.5 0
007 0 0 0 0.5 0
的几个字段代表的是什么意思?
create table #员工表(number varchar(50),levae int)
insert into #员工表 values('001',0)
insert into #员工表 values('002',1)
insert into #员工表 values('003',0)
insert into #员工表 values('004',2)
insert into #员工表 values('005',0)
insert into #员工表 values('006',3)
insert into #员工表 values('007',0)create table #考勤表
(number varchar(50),
ClassUP1 datetime ,ClassNext1 datetime,
ClassUP2 datetime,ClassNext2 datetime )
insert into #考勤表 values('001', '2011-05-06 08:16:17.000','2011-05-06 08:16:17.000','2011-05-06 08:16:17.000','2011-05-06 08:16:17.000')
insert into #考勤表 values('002', null,null,null,null)
insert into #考勤表 values('003', '2011-05-06 08:16:17.000','2011-05-06 08:16:17.000',null,null)
insert into #考勤表 values('004', null,null,null,null)
insert into #考勤表 values('005' , null,null,null,null)
insert into #考勤表 values('006', null,null,'2011-05-06 08:16:17.000','2011-05-06 08:16:17.000')
insert into #考勤表 values('007', null,null,'2011-05-06 08:16:17.000','2011-05-06 08:16:17.000')select k.number,
sum(case when ClassUP1 is null then 0 else 0.25 end+
case when ClassNext1 is null then 0 else 0.25 end+
case when ClassUP2 is null then 0 else 0.25 end+
case when ClassNext2 is null then 0 else 0.25 end) as 上班天数,
sum(case when levae=1 then (
case when ClassUP1 is null then 0.25 else 0 end+
case when ClassNext1 is null then 0.25 else 0 end+
case when ClassUP2 is null then 0.25 else 0 end+
case when ClassNext2 is null then 0.25 else 0 end) else 0 end) as 有薪假,
sum(case when levae=2 then (
case when ClassUP1 is null then 0.25 else 0 end+
case when ClassNext1 is null then 0.25 else 0 end+
case when ClassUP2 is null then 0.25 else 0 end+
case when ClassNext2 is null then 0.25 else 0 end) else 0 end) as 病假,
sum(case when levae=3 then (
case when ClassUP1 is null then 0.25 else 0 end+
case when ClassNext1 is null then 0.25 else 0 end+
case when ClassUP2 is null then 0.25 else 0 end+
case when ClassNext2 is null then 0.25 else 0 end) else 0 end) as 事假,
sum(case when levae=0 then (
case when ClassUP1 is null then 0.25 else 0 end+
case when ClassNext1 is null then 0.25 else 0 end+
case when ClassUP2 is null then 0.25 else 0 end+
case when ClassNext2 is null then 0.25 else 0 end) else 0 end) as 旷工
from #考勤表 as k left join #员工表 as e on e.number=k.number group by k.number
楼主..貌似这样的设计有点问题哦~~
create table #员工表(number varchar(50))--员工表只记录员工的相关信息
insert into #员工表 values('001')
insert into #员工表 values('002')
insert into #员工表 values('003')
insert into #员工表 values('004')
insert into #员工表 values('005')
insert into #员工表 values('006')
insert into #员工表 values('007')-- 0全天在职
-- 1当天请了有薪假
-- 2当天请了病假
-- 3当天请了事假
-- 4当天不是全天在职,但又无请任何假(旷工)create table #考勤表
(number varchar(50),
ClassUP1 datetime ,ClassNext1 datetime,
ClassUP2 datetime,ClassNext2 datetime ,levae int) --把levae放这
--2011-05-06的考勤情况
insert into #考勤表 values('001', '2011-05-06 08:16:17.000','2011-05-06 08:16:17.000','2011-05-06 08:16:17.000','2011-05-06 08:16:17.000',0)
insert into #考勤表 values('002', null,null,null,null,1)
insert into #考勤表 values('003', '2011-05-06 08:16:17.000','2011-05-06 08:16:17.000',null,null,2)
insert into #考勤表 values('004', null,null,null,null,4)
insert into #考勤表 values('005' , null,null,null,null,2)
insert into #考勤表 values('006', null,null,'2011-05-06 08:16:17.000','2011-05-06 08:16:17.000',1)
insert into #考勤表 values('007', null,null,'2011-05-06 08:16:17.000','2011-05-06 08:16:17.000',3)
--2011-05-07的考勤情况
insert into #考勤表 values('001', null,null,'2011-05-07 08:16:17.000','2011-05-07 08:16:17.000',4)
insert into #考勤表 values('002', '2011-05-07 08:16:17.000','2011-05-07 08:16:17.000',null,null,3)
insert into #考勤表 values('003', null,null,null,null,2)
insert into #考勤表 values('004', null,null,null,null,2)
insert into #考勤表 values('005', null,null,null,null,3)
insert into #考勤表 values('006', '2011-05-07 08:16:17.000','2011-05-07 08:16:17.000','2011-05-07 08:16:17.000','2011-05-07 08:16:17.000',0)
insert into #考勤表 values('007', null,null,'2011-05-07 08:16:17.000','2011-05-07 08:16:17.000',1)
--2011-05-08的考勤情况
insert into #考勤表 values('001', null,null,'2011-05-08 08:16:17.000','2011-05-08 08:16:17.000',1)
insert into #考勤表 values('002', null,null,null,null,2)
insert into #考勤表 values('003', null,null,'2011-05-08 08:16:17.000','2011-05-08 08:16:17.000',4)
insert into #考勤表 values('004', null,null,null,null,1)
insert into #考勤表 values('005' ,null,null,null,null,1)
insert into #考勤表 values('006', '2011-05-08 08:16:17.000','2011-05-08 08:16:17.000',null,null,3)
insert into #考勤表 values('007','2011-05-08 08:16:17.000','2011-05-08 08:16:17.000','2011-05-08 08:16:17.000','2011-05-08 08:16:17.000',0)select number,
sum(case when ClassUP1 is not null then 0.5 else 0 end+
case when ClassUP2 is not null then 0.5 else 0 end) as 上班天数,
sum(case when levae=0 then (
case when ClassUP1 is not null and ClassUP2 is not null then 1 else 0 end) else 0 end) as 全日在职天数,
sum(case when levae=1 then (
case when ClassUP1 is null then 0.5 else 0 end+
case when ClassUP2 is null then 0.5 else 0 end) else 0 end) as 有薪假,
sum(case when levae=2 then (
case when ClassUP1 is null then 0.5 else 0 end+
case when ClassUP2 is null then 0.5 else 0 end) else 0 end) as 病假,
sum(case when levae=3 then (
case when ClassUP1 is null then 0.5 else 0 end+
case when ClassUP2 is null then 0.5 else 0 end) else 0 end) as 事假,
sum(case when levae=4 then (
case when ClassUP1 is null then 0.5 else 0 end+
case when ClassUP2 is null then 0.5 else 0 end) else 0 end) as 旷工
from #考勤表 group by number小弟的愚见~~
考虑每个员工每天的考勤情况都不同..可能今天全天上班了,明天又有请假的..