SQL语言考勤打卡记录 求教关于提取考勤打卡记录的sql语句
\u95ee\u4e00\u4e2a\u8003\u52e4SQL\u8bed\u53e5\u7684\u95ee\u9898\uff0c\u60f3\u67e5\u8be2\u4e00\u6574\u6708\u8003\u52e4\u8bb0\u5f55select \u5458\u5de5ID,a.[1] \u4e0a\u73ed\u65f6\u95f4,b.[1] \u4e0b\u73ed\u65f6\u95f4,datediff(mm,b.[1] - a.[1]) \u7d2f\u8ba1\u65f6\u95f4,
a.[2] \u4e0a\u73ed\u65f6\u95f4,b.[2] \u4e0b\u73ed\u65f6\u95f4,datediff(mm,b.[2] - a.[2]) \u7d2f\u8ba1\u65f6\u95f4,.......
from
( select \u5458\u5de5ID,\u4e0a\u73ed\u65f6\u95f4 from \u8003\u52e4\u8868
pivot ( min( \u8003\u52e4\u65f6\u95f4) for datepart(dd, \u8003\u52e4\u65f6\u95f4) in ( [1],[2],[3],................... )) as pvt ) a,
( select \u5458\u5de5ID,\u4e0a\u73ed\u65f6\u95f4 from \u8003\u52e4\u8868
pivot ( mxn( \u8003\u52e4\u65f6\u95f4) for datepart(dd, \u8003\u52e4\u65f6\u95f4) in ( [1],[2],[3],................... )) as pvt ) b
where a.\u5458\u5de5ID = b.\u5458\u5de5ID
\u697c\u4e3b\u597d\uff0c\u6211\u8fd9\u51e0\u5929\u4e5f\u6b63\u5728\u505a\u8003\u52e4\u7684\u4e1c\u897f\u3002\u4f60\u60f3\u8981\u4e2a\u4ec0\u4e48\u6837\u5b50\u7684\u6570\u636e\uff0c\u6216\u8005\u6709\u4ec0\u4e48\u95ee\u9898\u5462\uff1f\u6211\u60f3\u8bf4\u7684\u662f\uff0cSQL\u4e2d\u5173\u4e8e\u65f6\u95f4\u7684\u4e1c\u897f\u90fd\u4e0d\u8981\u60f3\u7684\u592a\u7b80\u5355\uff0c\u4ed6\u662f\u4e2a\u590d\u6742\u7684\u4e1c\u897f\u3002\u8bf4\u660e\u4e0b\u4f60\u7684\u95ee\u9898\uff0c\u6211\u4f30\u8ba1\u53ef\u4ee5\u7ed9\u4f60\u89e3\u51b3\u3002
首先要有一个工厂日历的表，列出所有工作日,至少一个字段：工作日 varchar(10)。然后这样即可：
select id,
迟到次数=sum(case when timec>'08:00:00' tand timec<'09:00:00' then 1 else 0 end),
旷工次数=sum(case when timec>'09:00:00' or timec is null then 1 else 0 end),
打卡次数=sum(case when timec is null then 0 else 1 end)
from
(
SELECT * FROM 工厂日历 left join
(select id,
datec=convert(varchar(10),card_time,120),
timec=substring(convert(varchar,card_time,120),12,8)
from tablename
) a
on 工作日=DATEC
) b
group by ID
假设这个表的名称叫做 work，来存储打卡的时间信息
select ID,
sum(case when card_time>='%-%-% 08:00:00' and card_time<='%-%-% 08:59:59' then 1 else 0 end ) as '迟到次数',
sum(case when card_time>='%-%-% 09:00:00' or card_time='' then 1 else 0 end ) as '旷工次数',
sum(case when card_time<>'' then 1 else 0 end) as '总的打卡次数'
from work
group by ID
不知道时间这么表示对不对
绛旓細select 濮撳悕,缂栧彿,datepart(day,鎵撳崱鏃堕棿) from 琛 group by 濮撳悕,缂栧彿,datepart(day,鎵撳崱鏃堕棿)having count(*)<>2 杩欐牱灏卞ソ浜嗭紝鎴戜滑鍏徃鐜板湪鍋氱殑灏辨槸鑰冨嫟绯荤粺锛屽鏋滄湁闂鍙互HI鎴戠户缁氦娴
绛旓細SQlSever2008鏁版嵁搴擄紝缁熻鏌愪汉鍑哄嫟銆佺己鍕ょ殑澶╂暟鍙婃棩鏈熸 濡傛灉浣犺繖涓槸鏈绠鍗曠殑鑰冨嫟璁板綍锛屼笉娑夊強鍒颁笁鐝掑拰鍔犵彮涔嬬被鐨勶紝閭ｄ箞涓涓渶绠鍗曠殑琛ㄦ槸杩欐牱 鍛樺伐琛紙涓嶇敤璇翠簡鍚э紝鍩烘湰淇℃伅锛 璁板綍琛紙鏃ユ湡銆佸憳宸ュ彿銆佸紑濮嬫椂闂淬佺粨鏉熸椂闂淬佸嚭鍕ゆ儏鍐碉級 濡傛灉鑷姩鎵撳崱鏈猴紝鑾峰彇鐨勫氨鏄伐鍙枫佹棩鏈熴佹椂闂淬
绛旓細oracle锛歴elect 鑰冨嫟鍙,鏃ユ湡,wm_concat(鏃堕棿) from 琛 group by 鑰冨嫟鍙,鏃ユ湡 sql:select stuff((select ',' + 鏃堕棿 from 琛 where 鏃ユ湡 = a.鏃ユ湡 and 鑰冨嫟鍗″彿 = a.鑰冨嫟鍗″彿 for XML path('')),1,1,'') 宸ュ彿,鑰冨嫟鍗″彿,鏃ユ湡 From 琛 a group by 鑰冨嫟鍗″彿, 鏃ユ湡 ...
绛旓細棣栧厛浣犺鏈夊瓧娈垫妸涓婄彮鍜屼笅鐝竴涓瀵瑰簲璧锋潵锛屽嵆涓鏉′笂鐝暟鎹浣曞搴斿埌鐩稿搴旂殑涓嬬彮鏁版嵁銆傝鍙ョ被浼间笅闈㈣繖鏍峰氨琛屼簡 select from (select from 琛 where 绫诲埆='涓婄彮')a ,(select from 琛 where 绫诲埆='涓嬬彮')b where 纭畾涓婁笅鐝竴涓瀵瑰簲鐨勬潯浠讹紝濡俛.濮撳悕=b.濮撳悕 and a.鏃堕棿=b.鏃堕棿 ...
绛旓細(ms_id user_id ms_time ms_desc ms_tag )鍏夐潬杩欎竴涓〃锛屼笉鑳界‘瀹氫綘闇瑕佺殑淇℃伅鍟娿傛瘮濡傝繜鍒版鏁帮紝鏃╅娆℃暟锛岃繕闇瑕佺煡閬撲笂涓嬬彮鐨勬椂闂村晩锛堟槸鍥哄畾鐨勫煎悧锛夋椃宸ユ鏁帮紝闇瑕佺煡閬撳摢姝ゆ槸宸ヤ綔鏃ュ晩锛堜篃鏄浐瀹氬硷紝杩樻槸鎸夊父瑙勭畻锛夊啀鏈夛紝姣忎釜浜轰笂鐝墦涓娆″崱锛屼笅鐝墦涓娆″崱锛堝嵆姣忎汉姣忓湪鍦ㄨ繖涓〃涓湁2鏉璁板綍缃...
绛旓細浠涔堟暟鎹簱锛無racle鏁版嵁搴撹鍙ュ涓嬶細update A set 鏃堕棿瀛楁=to_date('2020-5-13 08:00:00','yyyy-mm-dd HH24:MI:SS')where id=1
绛旓細濡傛灉鏁版嵁榻愬叏杩樺彲浠ヨ绠 濡傛灉瀛樺湪蹇樿鎵撳崱鐜拌薄 杩樿鍒ゆ柇 灏遍夯鐑︿簡 鐢╠atediff DATEDIFF ( datepart , startdate , enddate )灏忔椂鐢╤our DATEDIFF ( hour, startdate , enddate )鍏朵粬鑷寰幆澶勭悊
绛旓細select name,sum(case when intime between '8:00' and '8:29' then 1 else 0 end ) as '杩熷埌',sum(case when intime between '8:30' and '9:00' then 1 else 0 end ) as '缂哄嫟'from table1 group by name 鍥犱负鈥8锛30鈥濇槸涓涓叕鐢ㄦ椂闂寸偣锛屼笉鑳藉垽鍒紡杩熷埌杩樻槸缂哄嫟锛屾墍浠ヨ繜鍒...
绛旓細濡傛灉鏄痵qlserver浣犻渶瑕佸厛寤虹珛涓涓嚱鏁板儚杩欐牱 CREATE FUNCTION [dbo].[JoinString](@time varchar(100),@name varchar(100))RETURNS varchar(2000)ASBEGINDECLARE @tmp varchar(1000)set @tmp=''SELECT @tmp=@tmp+','+sj from (select 浜哄憳濮撳悕,CONVERT(varchar(100), 鍒峰崱鏃ユ湡鏃堕棿, 23) as rq,...
绛旓細涓ゆ鑰冨嫟锛歴elect 鍛樺伐ID,TRUNC(TIME,'d') FROM 鑰冨嫟琛 group by 鍛樺伐ID,TRUNC(TIME,'d')having count(*)=2;婕忔墦锛歴elect 鍛樺伐ID,TRUNC(TIME,'d') FROM 鑰冨嫟琛 group by 鍛樺伐ID,TRUNC(TIME,'d')having count(*)<2;
