我想用一条是不大可能啦 你还要检索,还要处理 还要写入 起码得分三步 1,检索,处理 founction getholiday(id as integer,search as sring)'id 要查询人的id,search为事假,病假等等 rs1=cn.execute("select * from tab1 where id="+id+" and am='"+search+"'") rs2=cn.execute("select * from tab1 where id="+id+" and pm='"+search+"'") getholiday=rs1.count+rs2.count end founction 调用getholiday 函数获得请假次数getholiday,在乘于0.5就得到你要往事假天数字段加的数据 3.写入 个人意见 仅供参考
SELECT 考勤号, SUM(CONVERT(Numeric(12, 2), SJ)) / 2 AS 事假天数, SUM(CONVERT(Numeric(12, 2), BJ)) / 2 AS 病假天数, SUM(CONVERT(Numeric(12, 2), GS)) / 2 AS 工伤天数 FROM (SELECT 考勤号, COUNT(*) AS SJ, 0 AS BJ, 0 AS GS FROM aa WHERE 结果AM = '事假' GROUP BY 考勤号 UNION ALL SELECT 考勤号, COUNT(*) AS SJ, 0 AS BJ, 0 AS GS FROM aa WHERE 结果PM = '事假' GROUP BY 考勤号 UNION ALL SELECT 考勤号, 0 AS SJ, COUNT(*) AS BJ, 0 AS GS FROM aa WHERE 结果AM = '病假' GROUP BY 考勤号 UNION ALL SELECT 考勤号, 0 AS SJ, COUNT(*) AS BJ, 0 AS GS FROM aa WHERE 结果PM = '病假' GROUP BY 考勤号 UNION ALL SELECT 考勤号, 0 AS SJ, 0 AS BJ, COUNT(*) AS GS FROM aa WHERE 结果AM = '工伤' GROUP BY 考勤号 UNION ALL SELECT 考勤号, 0 AS SJ, 0 AS BJ, COUNT(*) AS GS FROM aa WHERE 结果PM = '工伤' GROUP BY 考勤号) z GROUP BY 考勤号
可以在精簡的 表名為test ,數值a,b,c 可以是妳自己指定,例如a='事假',b='病假' 等,妳替換就可以了. select tt.no, sum(tt.a) as A ,sum(tt.b)as B,sum(c) as C from ( select No,count(am)as A ,'' as B, '' as C from test where am='a' or pm='a' group by no union select No,''as A ,count(am) as B, '' as C from test where am='b' or pm='b' group by no union select No,''as A ,'' as B, count(am) as C from test where am='c' or pm='c' group by no ) as tt group by tt.no order by tt.no
Select 语句的from后不可直接用SELECT语句 (环境用ADO对SQL Server)
zeng_zhh(紫河)很不錯,容易閱讀
上述語句在 MS SQL Query Analyzer 測試通過.
想起来了,以前我是通过视图(VIEW)解决问题的
to zeng_zhh(紫河) 你写的SQL语句是正确的,我刚才测试过了 原来(Select .....)后面加个‘ as 别名’就可以了
to zeng_zhh(紫河) 你写的SQL语句是正确的,我刚才测试过了 原来(Select .....)后面加个‘ as 别名’就可以了
To zeng_zhh(紫河): 你的结果集正确吗??
To zeng_zhh(紫河): 如果一天的出勤情况相同的话,就会少统计半天!
哦,忘了除去2, 應該是這樣 select tt.no, sum(tt.a)/2.0 as A ,sum(tt.b)/2.0 as B,sum(c)/2.0 as C from ( select No,count(am)as A ,'' as B, '' as C from test where am='a' or pm='a' group by no union select No,''as A ,count(am) as B, '' as C from test where am='b' or pm='b' group by no union select No,''as A ,'' as B, count(am) as C from test where am='c' or pm='c' group by no ) as tt group by tt.no order by tt.no
哎呀,還是錯了.沒有考慮到ganzhiruogy(方竹) 所說的.再看看先
To zeng_zhh(紫河): 我是认为下句有错 where 处 select No,count(am)as A ,'' as B, '' as C from test where am='a' or pm='a' group by no
你还要检索,还要处理
还要写入
起码得分三步
1,检索,处理
founction getholiday(id as integer,search as sring)'id 要查询人的id,search为事假,病假等等
rs1=cn.execute("select * from tab1 where id="+id+" and am='"+search+"'")
rs2=cn.execute("select * from tab1 where id="+id+" and pm='"+search+"'")
getholiday=rs1.count+rs2.count
end founction
调用getholiday 函数获得请假次数getholiday,在乘于0.5就得到你要往事假天数字段加的数据
3.写入
个人意见 仅供参考
1 人员库 (编号,...其他内容)
2 事件库 (编号、名称《如:病假》、...)
3 考勤库 (人员编号、事件编号、考勤时间《如:am,pm》)
这样写sql语句就很方便
SUM(CONVERT(Numeric(12, 2), BJ)) / 2 AS 病假天数, SUM(CONVERT(Numeric(12, 2),
GS)) / 2 AS 工伤天数
FROM (SELECT 考勤号, COUNT(*) AS SJ, 0 AS BJ, 0 AS GS
FROM aa
WHERE 结果AM = '事假'
GROUP BY 考勤号
UNION ALL
SELECT 考勤号, COUNT(*) AS SJ, 0 AS BJ, 0 AS GS
FROM aa
WHERE 结果PM = '事假'
GROUP BY 考勤号
UNION ALL
SELECT 考勤号, 0 AS SJ, COUNT(*) AS BJ, 0 AS GS
FROM aa
WHERE 结果AM = '病假'
GROUP BY 考勤号
UNION ALL
SELECT 考勤号, 0 AS SJ, COUNT(*) AS BJ, 0 AS GS
FROM aa
WHERE 结果PM = '病假'
GROUP BY 考勤号
UNION ALL
SELECT 考勤号, 0 AS SJ, 0 AS BJ, COUNT(*) AS GS
FROM aa
WHERE 结果AM = '工伤'
GROUP BY 考勤号
UNION ALL
SELECT 考勤号, 0 AS SJ, 0 AS BJ, COUNT(*) AS GS
FROM aa
WHERE 结果PM = '工伤'
GROUP BY 考勤号) z
GROUP BY 考勤号
表名為test ,數值a,b,c 可以是妳自己指定,例如a='事假',b='病假' 等,妳替換就可以了.
select tt.no, sum(tt.a) as A ,sum(tt.b)as B,sum(c) as C from (
select No,count(am)as A ,'' as B, '' as C from test where am='a' or pm='a' group by no
union
select No,''as A ,count(am) as B, '' as C from test where am='b' or pm='b' group by no
union
select No,''as A ,'' as B, count(am) as C from test where am='c' or pm='c' group by no ) as tt
group by tt.no order by tt.no
(环境用ADO对SQL Server)
你写的SQL语句是正确的,我刚才测试过了
原来(Select .....)后面加个‘ as 别名’就可以了
你写的SQL语句是正确的,我刚才测试过了
原来(Select .....)后面加个‘ as 别名’就可以了
你的结果集正确吗??
如果一天的出勤情况相同的话,就会少统计半天!
select tt.no, sum(tt.a)/2.0 as A ,sum(tt.b)/2.0 as B,sum(c)/2.0 as C from (
select No,count(am)as A ,'' as B, '' as C from test where am='a' or pm='a' group by no
union
select No,''as A ,count(am) as B, '' as C from test where am='b' or pm='b' group by no
union
select No,''as A ,'' as B, count(am) as C from test where am='c' or pm='c' group by no ) as tt
group by tt.no order by tt.no
我是认为下句有错 where 处
select No,count(am)as A ,'' as B, '' as C from test where am='a' or pm='a' group by no