sql的一句语句，求教！！(100分)

是不是有点长呀，呵呵,说明一下设计思路：我是按某年，某月，某一个员工，也可增加部门，统计某个员工的考勤情况，得出的值，再通过上表的员工ID（唯一）查找员工固定表设置的值，两个表结合然后实时插入到员工工资表里，再通过员工工资计算出，某一个员工一天的工资，祝位还有什么好的意见，望指教。欢迎高手交流指正Q237189068

一个月31列肯定有问题,不符合范式,表应该分为三张表
表1
员工ID，部门ID， 日期， 班次，考勤，记录日期
77 1 2006-01-04 1 1 2006-06-18
77 1 2006-01-04 2 1 2006-06-18
77 1 2006-01-04 3 2 2006-06-18
77 1 2006-01-05 1 3 2006-06-19
77 1 2006-01-05 2 4 2006-06-19
77 1 2006-01-05 3 2 2006-06-19

表2
部门ID 部门名称
1 人力资源部

表3 考勤类型表
考勤ID 考勤类型
1 加白
2 加夜
3 矿工
4 事假

这样设计可能数据库运行会慢一些,但符合范式
好处是数据冗余少,最大限度地减少出错的机会,可扩展性好
毕竟程序稳定才是最重要的

另外.楼主对数据库的理解还停留在Excel阶段
Excel跟数据库根本就是两码事
建议先熟悉数据库再进行数据库开发
按月查询也很简单DatePart()函数可以从日期里提取出月份,
直观去显示一个月的每一天的考勤也可以轻松地用程序在客户端实现

来自：free_366, 时间：2006-6-21 22:21:13, ID：3477781
select id,sum(a) as 加白班次数 from
(select id,count(id)as a from table where a='加白' and (日期段) group by id
union
select id,count(id)as a from table where b='加白' and (日期段) group by id
union
select id,count(id)as a from table where c='加白' and (日期段) group by id
)
group by id
我运行提示group附近有语法错误，看不出来?


加括号试试：
select id,sum(a) as 加白班次数 from
((select id,count(id)as a from table where a='加白' and (日期段) group by id)
union
(select id,count(id)as a from table where b='加白' and (日期段) group by id)
union
(select id,count(id)as a from table where c='加白' and (日期段) group by id)
)
group by id

一条大鱼,谢谢你的提醒，表2，表3我设计和你一样，表1我原也是这样设计，但是客户录入数据嫌麻烦，一个月的记录不在同一条记录，看起来不直观；
按月查询也很简单DatePart()函数可以从日期里提取出月份,我的客户要求考勤表在下个月的第五天前都可报，这样DatePart()函数可以从日期里提取出月份？
直观去显示一个月的每一天的考勤也可以轻松地用程序在客户端实现。你的如何实现，录的数据在下个月的第五天前可实进修改？谢谢

jenhon，报错还是一样，谢谢

把jenhon 说的()后面随便加个别名就可以了,

把jenhon 说的()后面随便加个别名就可以了,
OK,但统计结果不对

已记分，因分数有限，不成敬意，谢谢祝位！谢谢热心的网友！

多人接受答案了。

create procedure p_ff
(
@year int,
@month int,
@id int
@s varchar(20)
)
as
begin
declare @ss varchar(4000)
select @ss='select Mark1+Mark2+Mark3+Mark4+Mark5+Mark6+Mark7+Mark8+Mark9+Mark10+Mark11+Mark12+Mark13+Mark14+Mark15+'+
'Mark16+Mark17+Mark18+Mark19+Mark20+Mark21+Mark22+Mark23+Mark24+Mark25+Mark26+Mark27+Mark28+Mark28+Mark30+Mark31'+
'from (SELECT Mark1= CASE Mark1 WHEN '''+@s+''' THEN 1 ELSE 0 end,'+
'Mark2 = CASE Mark2 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark3 = CASE Mark3 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark4 = CASE Mark4 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark5 = CASE Mark5 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark6 = CASE Mark6 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark7 = CASE Mark7 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark8 = CASE Mark8 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark9 = CASE Mark9 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark10 = CASE Mark10 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark11 = CASE Mark11 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark12 = CASE Mark12 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark13 = CASE Mark13 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark14 = CASE Mark14 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark15 = CASE Mark15 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark16 = CASE Mark16 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark17 = CASE Mark17 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark18 = CASE Mark18 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark19 = CASE Mark19 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark20 = CASE Mark20 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark21 = CASE Mark21 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark22 = CASE Mark22 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark23 = CASE Mark23 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark24 = CASE Mark24 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark25 = CASE Mark25 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark26 = CASE Mark26 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark27 = CASE Mark27 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark28 = CASE Mark28 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark29 = CASE Mark29 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark30 = CASE Mark30 WHEN '''+@s+''' THEN 1 ELSE 0 end,'
+'Mark31= CASE Mark31 WHEN '''+@s+''' THEN 1 ELSE 0 end '+
' FROM Work_Work where Years='+convert(varchar,@year)'
+' and Months='+convert(varchar,@month)'
+' and PersonnelID='+convert(varchar,@id)+') tt '

exec(@ss)

在陈辰的基础上加的,大致思路就是写个存储过程了,然后传四个参数,里面的引号肯定不对,你自己在调调吧.前三个不用说了,第四个就是字符串'加白','加夜'什么的,

谢谢,已调试好,用sum,语句不是很长,呵呵