关于考勤情况统计的数据库问题

Question

本帖最后由 zzxxjj 于 2014-07-17 09:38:30 编辑

Answer 1

case when  1='#' then 1 else 0 end+
case when  2='#' then 1 else 0 end+
... 以此类推 可以实现 不过每种情况都得写31条...

Answer 2

现把考勤数据合并（unpivot），在用case when 查询。一句也能搞定select name ,
sum(case when data=' √' then 1 else  0 end )as [出勤],
sum(case when data=' #' then 1 else  0 end )as [公休]
from 
(
select * from kaoqin 
unpivot 
(
  v for c in ([1],[2],[3,[4])
) u) t group by t.name

Answer 3

use tempdb
go
if (object_id('tempdb..#1') Is not null) Drop Table #1create table #1(ID int identity,name nvarchar(50),[year] char(4),[month] char(2),[1]nchar(1),[2]nchar(1),[3]nchar(1),[4]nchar(1),[5]nchar(1),[6]nchar(1),[7]nchar(1),[8]nchar(1),[9]nchar(1),[10]nchar(1),[11]nchar(1),[12]nchar(1),[13]nchar(1),[14]nchar(1),[15]nchar(1),[16]nchar(1),[17]nchar(1),[18]nchar(1),[19]nchar(1),[20]nchar(1),[21]nchar(1),[22]nchar(1),[23]nchar(1),[24]nchar(1),[25]nchar(1),[26]nchar(1),[27]nchar(1),[28]nchar(1),[29]nchar(1),[30]nchar(1),[31]nchar(1))insert into #1 ( name,[year],[month],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31] )
select N'张三','2014','7',N'√',N'√',N'√',N'∫',N'∫',N'#',N'#',N'√',N'√',N'√',N'√',N'√',N'#',N'#',N'√',N'√',N'√',N'√',N'√',N'√',N'#',N'√',N'√',N'√',N'√',N'√',N'#',N'#',N'√',N'√',N'√'
union all
select N'李四','2014','7',N'√',N'√',N'√',N'∫',N'∫',N'#',N'#',N'∫',N'∫',N'∫',N'∫',N'∫',N'#',N'#',N'√',N'√',N'√',N'Δ',N'Δ',N'#',N'#',N'√',N'√',N'√',N'√',N'√',N'#',N'#',N'√',N'√',N'√'------------------------
declare @tb_sys as table(name nvarchar(50),[sign] nchar(1))
insert into @tb_sys ( name,[sign] )
select N'出勤',N'√' union all
select N'迟到',N'∞' union all
select N'早退',N'~' union all
select N'倒休',N'∧' union all
select N'公休',N'#' union all
select N'病假',N'∑' union all
select N'带薪年假',N'∫' union all
select N'事假',N'Δ' 
;with cte_unpivot as 
(      
select name,[year],[month],[sign] 
from #1 a 
unpivot( [sign] for [Date] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31] ))b
),cte_count as 
(
select a.name,a.[year],a.[month],b.name as [sign] ,count(b.name) as sign_count
from cte_unpivot a
inner join @tb_sys b on b.[sign]=a.[sign]
group by a.name,a.[year],a.[month],b.name 
)
select * 
From cte_count as a
pivot(max(sign_count) for [sign] in ([出勤],[迟到],[早退],[倒休],[公休],[病假],[带薪年假],[事假]))b
go
/*
name year month 出勤 迟到 早退 倒休 公休 病假 带薪年假 事假
李四 2014 7  14 NULL NULL NULL 8 NULL 7 2
张三 2014 7  22 NULL NULL NULL 7 NULL 2 NULL
*/

Answer 4

这是设计问题，非SQL问题
可能很多人没细想或低估了考勤的细节

Answer 5

下面回答中关于CTE的使用，cte_count ，cte_unpivot这两个结果集，还有],[sign] 定义的是数组吗，
没有看明白 ,能给解释下吗？最好加个注释declare @tb_sys as table(name nvarchar(50),[sign] nchar(1))    
insert into @tb_sys ( name,[sign] )
    select N'出勤',N'√' union all
    select N'迟到',N'∞' union all
    select N'早退',N'~' union all
    select N'倒休',N'∧' union all
    select N'公休',N'#' union all
    select N'病假',N'∑' union all
    select N'带薪年假',N'∫' union all
    select N'事假',N'Δ' 
     
 
;with cte_unpivot as 
(      
select name,[year],[month],[sign] 
    from #1 a 
    unpivot( [sign] for [Date] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31] ))b    
),cte_count as 
(
select a.name,a.[year],a.[month],b.name as [sign] ,count(b.name) as sign_count
    from cte_unpivot a
        inner join @tb_sys b on b.[sign]=a.[sign]
    group by a.name,a.[year],a.[month],b.name 
)
select * 
    From cte_count as a
    pivot(max(sign_count) for [sign] in ([出勤],[迟到],[早退],[倒休],[公休],[病假],[带薪年假],[事假]))b

Answer 6

use tempdb
go
if (object_id('tempdb..#1') Is not null) Drop Table #1
 
create table #1(ID int identity,name nvarchar(50),[year] char(4),[month] char(2),[1]nchar(1),[2]nchar(1),[3]nchar(1),[4]nchar(1),[5]nchar(1),[6]nchar(1),[7]nchar(1),[8]nchar(1),[9]nchar(1),[10]nchar(1),[11]nchar(1),[12]nchar(1),[13]nchar(1),[14]nchar(1),[15]nchar(1),[16]nchar(1),[17]nchar(1),[18]nchar(1),[19]nchar(1),[20]nchar(1),[21]nchar(1),[22]nchar(1),[23]nchar(1),[24]nchar(1),[25]nchar(1),[26]nchar(1),[27]nchar(1),[28]nchar(1),[29]nchar(1),[30]nchar(1),[31]nchar(1))
 
insert into #1 ( name,[year],[month],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31] )
    select N'张三','2014','7',N'√',N'√',N'√',N'∫',N'∫',N'#',N'#',N'√',N'√',N'√',N'√',N'√',N'#',N'#',N'√',N'√',N'√',N'√',N'√',N'√',N'#',N'√',N'√',N'√',N'√',N'√',N'#',N'#',N'√',N'√',N'√'
    union all
    select N'李四','2014','7',N'√',N'√',N'√',N'∫',N'∫',N'#',N'#',N'∫',N'∫',N'∫',N'∫',N'∫',N'#',N'#',N'√',N'√',N'√',N'Δ',N'Δ',N'#',N'#',N'√',N'√',N'√',N'√',N'√',N'#',N'#',N'√',N'√',N'√'
 
 
-- 1. 使用表变量@tb_sys,描述各种考勤结果符号与名称的关系declare @tb_sys as table(name nvarchar(50),[sign] nchar(1))    
insert into @tb_sys ( name,[sign] )
    select N'出勤',N'√' union all
    select N'迟到',N'∞' union all
    select N'早退',N'~' union all
    select N'倒休',N'∧' union all
    select N'公休',N'#' union all
    select N'病假',N'∑' union all
    select N'带薪年假',N'∫' union all
    select N'事假',N'Δ' 
     /* 
2.计算过程
------------------------------------------------------------------------------------------
   2.1 把1-31号的考勤记录进行列转换成行，方便统计各种考勤记录. 对应位置cte_unpivot 分解代码出来如下:
   
*/ 
select name,[year],[month],[sign] 
from #1 a 
unpivot( [sign] for [Date] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31] ))b    /*    
   2.2 把2.1的转换结果按[出勤],[迟到],[早退]...[事假] 进行分类汇总，对应位置cte_count
     分解代码出来如下：
*/  ;with cte_unpivot as
(      
select name,[year],[month],[sign] 
from #1 a 
unpivot( [sign] for [Date] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31] ))b    
)
select a.name,a.[year],a.[month],b.name as [sign] ,count(b.name) as sign_count
from cte_unpivot a
inner join @tb_sys b on b.[sign]=a.[sign]
group by a.name,a.[year],a.[month],b.name 
         /*    
   2.3 为了满足原始的需求，[出勤],[迟到],[早退]...[事假]的数据要分列显示，那么对2.2的结果进行行列转换，对应最后的CTE的最后select部分
     最终代码如下：
*/ ;with cte_unpivot as --2.1
(      
select name,[year],[month],[sign] 
    from #1 a 
    unpivot( [sign] for [Date] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31] ))b    
),cte_count as --2.2
(
select a.name,a.[year],a.[month],b.name as [sign] ,count(b.name) as sign_count
    from cte_unpivot a
        inner join @tb_sys b on b.[sign]=a.[sign]
    group by a.name,a.[year],a.[month],b.name 
)
select *  --2.3
    From cte_count as a
    pivot(max(sign_count) for [sign] in ([出勤],[迟到],[早退],[倒休],[公休],[病假],[带薪年假],[事假]))b

Answer 7

语句中with语句，不知道怎么讲，相关知识点应看哪方面资料。

Answer 8

公用表表达式 
with cte as
(
........
）

Answer 9

在sql server查询分析器中，按F1查询联机帮助即可，搜CTE