遇到一个难问题,请各位兄弟帮忙.在线等代......

2005-10-15 10:36:39 322 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

华为机试：查找兄弟单词一、题目：描述定义一个单词的“兄弟单词”为：交换该单词字母顺序（注：可以交换任意次），而不添加、删除、修改原有的字母就能生成的单词。兄弟单词要求和原来的单词不同。例如：ab和ba是兄弟单词。ab和ab则不是兄弟单词。现在给定你n个单词，另外再给你一个单词str，让你寻找str的兄弟单词里，按字典序排列后的第k个单词是什么？注意：字典中可能有重复单词。本题含有多组输入数据。输入描述：先输入单词的个数n，再输入n个单词。再输入一个单词，为待查找的单词x 最后输入数字k 输出描述：阅读详情

我想得到一个月员工的考勤报表
要求是根据班次表和打卡原始记录来显示员工在规定的上下班时间是否迟到，迟到多长时间，早退多长时间，加班多长时间（单位最少是0.5小时），旷工多长时间
遇到星期六，跟日算加班。如果休息日上班算加班，不来不算旷工。

员工号班次打卡日期时间1 时间2 迟到时间早退时间加班时间旷工
A1459 MS 2004.04.01 0844 1717 14 20 0
A1459 MS 2004.04.02 0819 1910 0 0 1.5h
A1459 MS 2004.04.03 0821 1740
.
.
A2176 LS 2004.04.01 1932 0722 2 10 0
A2176 LS 2004.04.02 1925 0832 0 0 1h
A2176 LS 2004.04.03 1

班次表
shift_mst
班次上班时间下班时间是否跨日上班
SHIFT_TYPE WORK_START WORK_END OVER_NIGHT
---------- ---------- -------- ----- -------
MS 0830 1730 N
LS 1930 0730 Y

休息日
班次星期
SHIFT_TYPE DAY_OFF
MS 1
MS 7
LS 1

打卡原始记录
record
员工号班次打卡日期时间
emp_no shift_type date time
A1459 MS 2004.04.01 0844
A1459 MS 2004.04.01 1750
A1459 MS 2004.04.02 0819
A1459 MS 2004.04.02 1910
A1459 MS 2004.04.03 0821
A1459 MS 2004.04.03 1740
A1459 MS 2004.04.04 0822
A1459 MS 2004.04.04 1810
A2176 LS 2004.04.01 1932
A2176 LS 2004.04.02 0722
A2176 LS 2004.04.02 1925
A2176 LS 2004.04.03 0732
A2176 LS 2004.04.04 1945
A2176 LS 2004.04.05 0936

...全文

华为OJ——查找兄弟单词输入描述: 先输入字典中单词的个数n，再输入n个单词作为字典单词。再输入一个单词，查找其在字典中兄弟单词的个数m 再输入数字k 输出描述: 根据输入，输出查找到的兄弟单词的个数m 然后输出查找到的兄弟单词的第k个单词。思路：主要通过将两个字符串进行转换为数组类型，然后对两个数组进行自然排序（Arrays.sort()），进而判断两个数组是否相等（Arrays.equals(x,y 阅读详情

qianduo

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

qianduo 2005-10-19

打赏
举报

请大家帮我补充,得到

员工号班次打卡日期时间1 时间2 迟到时间早退时间加班时间旷工
A1459 MS2004.04.010844 1717 14 20 0
A1459 MS2004.04.020819 1910 0 0 1.5h
A1459 MS2004.04.030821 1740
.
.
A2176 LS2004.04.011932 0722 2 10 0
A2176 LS2004.04.021925 0832 0 0 1h
A2176 LS2004.04.03 1

qianduo 2005-10-19

打赏
举报

LGame 2005-10-18

打赏
举报

高人啊

qianduo 2005-10-18

打赏
举报

班次要是再增加几个呢？

qianduo 2005-10-18

打赏
举报

算异常

real_name 2005-10-18

打赏
举报

到了有效时间没有打卡的怎么算呢？

qianduo 2005-10-17

打赏
举报

多谢scmail81(freedom) 大侠. 还有一个条件,就是遇到休息日,上班为加班,不上班显示为休息

休息日
班次星期
SHIFT_TYPE DAY_OFF
MS 1
MS 7
LS 1

qianduo 2005-10-17

打赏
举报

还有打卡的有效时间没有考虑进去

qianduo 2005-10-15

打赏
举报

不好意思，大概格式如下

员工号班次打卡日期上班时间下班时间迟到时间早退时间加班旷工
A1459 MS 2004.04.01 08:44 17:17 14分钟 20分钟 0
A1459 MS 2004.04.02 08:19 19:10 0 0 1.5h
A1459 MS 2004.04.03 08:21 17:40
.
.
A2176 LS 2004.04.01 19:32 07:22 2 10 0
A2176 LS 2004.04.02 19:25 08:32 0 0 1h
A2176 LS 2004.04.03 1

其实完整的班次表里还有上班打卡的有效时间段,下班打卡的有效时间段,防止重复多次打卡的现象发生.

shift_mst
班次上班时间下班时间是否跨日上班上班打卡的有效时间上班打卡的有效时间
SHIFT_TYPE WORK_START WORK_END OVER_NIGHT START_PUNCH STOP_PUNCH OT_START OT_END OT_OVE_NIGHT
---------- ---------- -------- ----- -------
MS 08:30 17:30 N 06:30 10:30 14:00 19:00 N
LS 19:30 07:30 Y 17:30 21:30 05:30 09:30 N

如MS 班次正常上班时间为08：30 打卡的有效时间为 START_PUNCH 06：30--- STOP_PUNCH 10:30 如在这段时间打了2次，就取最大的一次为打卡上班的时间
如打卡的时间大于上班的时间就为迟到并得到迟到多少分钟。

下班正常下班时间为17：30 下班打卡的有效时间为 OT_START 14:00 --- OT_END 19：00 。如果在这短时间打多次就取最小的时间为下班打卡时间。
如果下班打卡时间-正常下班时间为加班时间。但加班时间要大于一个小时才算为加班时间。

天地客人 2005-10-15

打赏
举报

你要的考勤记录是什么样的格式啊?请说明?

$扫地僧$ 2005-10-15

打赏
举报

结果为:
A1459 MS 2004-04-01 00:00:00.000 08:44:00 17:50:00 14 0 .330000 0
A1459 MS 2004-04-02 00:00:00.000 08:19:00 19:10:00 0 0 1.670000 0
A1459 MS 2004-04-03 00:00:00.000 08:21:00 17:40:00 0 0 .170000 0
A1459 MS 2004-04-04 00:00:00.000 08:22:00 18:10:00 0 0 .670000 0
A2176 LS 2004-04-01 00:00:00.000 19:32:00 07:22:00 2 8 .000000 0
A2176 LS 2004-04-02 00:00:00.000 19:25:00 07:32:00 0 0 .030000 0
A2176 LS 2004-04-03 00:00:00.000 NULL NULL 0 0 .000000 1
A2176 LS 2004-04-04 00:00:00.000 19:45:00 09:36:00 15 0 2.100000 0
A2176 LS 2004-04-05 00:00:00.000 NULL NULL 0 0 .000000 1

$扫地僧$ 2005-10-15

打赏
举报

create table record
(
emp_no varchar(10),
shift_type varchar(2),
T_date varchar(15),
T_time varchar(15)
)

insert record
select 'A1459','MS','2004.04.01','0844' union
select 'A1459','MS','2004.04.01','1750' union
select 'A1459','MS','2004.04.02','0819' union
select 'A1459','MS','2004.04.02','1910' union
select 'A1459','MS','2004.04.03','0821' union
select 'A1459','MS','2004.04.03','1740' union
select 'A1459','MS','2004.04.04','0822' union
select 'A1459','MS','2004.04.04','1810' union

select 'A2176','LS','2004.04.01','1932' union
select 'A2176','LS','2004.04.02','0722' union
select 'A2176','LS','2004.04.02','1925' union
select 'A2176','LS','2004.04.03','0732' union

select 'A2176','LS','2004.04.04','1945' union
select 'A2176','LS','2004.04.05','0936'

create table shift_mst
(
SHIFT_TYPE varchar(10),
WORK_START varchar(10),
WORK_END varchar(15),
OVER_NIGHT varchar(15),
START_PUNCH varchar(15),
STOP_PUNCH varchar(15),
OT_START varchar(15),
OT_END varchar(15),
OT_OVE_NIGHT varchar(2)
)
insert shift_mst
select 'MS','08:30','17:30','N','06:30','10:30','14:00','19:00','N' union
select 'LS','19:30','07:30','Y','17:30','21:30','05:00','09:30','N'
--视图
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[V_AM]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[V_AM]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[V_LS]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[V_LS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[V_LX]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[V_LX]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[V_PM]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[V_PM]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.V_AM --白班的上班时间
AS
SELECT emp_no, shift_type, CAST(T_date AS datetime) AS T_date,
MIN(CAST(T_date + ' ' + LEFT(T_time, 2) + ':' + RIGHT(T_time, 2) + ':00' AS datetime))
AS T_time, 'AM' AS Flag
FROM dbo.record R
WHERE (shift_type = 'MS')
GROUP BY emp_no, shift_type, T_date

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.V_LS --夜班上班时间
AS
SELECT emp_no, shift_type, CAST(T_date AS datetime) AS T_date,
MAX(CAST(T_date + ' ' + LEFT(T_time, 2) + ':' + RIGHT(T_time, 2) + ':00' AS datetime))
AS T_time, 'S' AS Flag
FROM dbo.record R
WHERE (shift_type = 'LS') AND (DATEDIFF(hh, CAST(T_date + ' ' + LEFT(T_time, 2)
+ ':' + RIGHT(T_time, 2) + ':00' AS datetime), DATEADD(d, 1,
CAST(T_date AS datetime))) < 12)
GROUP BY emp_no, shift_type, T_date

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.V_LX --夜班下班时间
AS
SELECT emp_no, shift_type, CAST(T_date AS datetime) AS T_date,
MIN(CAST(T_date + ' ' + LEFT(T_time, 2) + ':' + RIGHT(T_time, 2) + ':00' AS datetime))
AS T_time, 'E' AS Flag
FROM dbo.record R
WHERE (shift_type = 'LS') AND (DATEDIFF(hh, CAST(T_date + ' ' + LEFT(T_time, 2)
+ ':' + RIGHT(T_time, 2) + ':00' AS datetime), DATEADD(d, 1,
CAST(T_date AS datetime))) > 12)
GROUP BY emp_no, shift_type, T_date

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.V_PM --白班的下班时间
AS
SELECT emp_no, shift_type, CAST(T_date AS datetime) AS T_date,
MAX(CAST(T_date + ' ' + LEFT(T_time, 2) + ':' + RIGHT(T_time, 2) + ':00' AS datetime))
AS T_time, 'PM' AS Flag
FROM dbo.record R
WHERE (shift_type = 'MS')
GROUP BY emp_no, shift_type, T_date

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
---------执行语句
select distinct TMS.emp_no as 员工号,TMS.shift_type as 班次, TMS.T_date as 打卡日期,
(select convert(char(15),T_time,108) from V_AM where V_AM.T_date=TMS.T_date ) as 上班时间,
(select convert(char(15),T_time,108) from V_PM where V_PM.T_date=TMS.T_date ) as 下班时间,
case when datediff(n, sh.WORK_START,(select convert(char(15),T_time,108) from V_AM where V_AM.T_date=TMS.T_date ))>0 then datediff(n, sh.WORK_START,(select convert(char(15),T_time,108) from V_AM where V_AM.T_date=TMS.T_date )) else 0 end as 迟到时间,
case when datediff(n, sh.WORK_END,(select convert(char(15),T_time,108) from V_PM where V_PM.T_date=TMS.T_date ))<0 then abs(datediff(n, sh.WORK_END,(select convert(char(15),T_time,108) from V_PM where V_PM.T_date=TMS.T_date ))) else 0 end as 早退时间,
case when datediff(n, sh.WORK_END,(select convert(char(15),T_time,108) from V_PM where V_PM.T_date=TMS.T_date ))>0 then round(datediff(n, sh.WORK_END,(select convert(char(15),T_time,108) from V_PM where V_PM.T_date=TMS.T_date ))/60.0,2) else 0 end as 加班时间,
case when (select convert(char(15),T_time,108) from V_AM where V_AM.T_date=TMS.T_date )<>' ' and (select convert(char(15),T_time,108) from V_PM where V_PM.T_date=TMS.T_date ) <> '' then '0' else '1' end as 旷工
from shift_mst SH,
(select * from V_AM
union all
select * from V_PM) TMS
where SH.SHIFT_TYPE=TMS.shift_type
union
select distinct TMS.emp_no,TMS.shift_type, TMS.T_date,
(select convert(char(15),T_time,108) from V_LS where V_LS.T_date=TMS.T_date ) as 上班时间,
(select convert(char(15),T_time,108) from V_LX where V_LX.T_date=dateadd(dd,1,TMS.T_date)) as 下班时间,
case when datediff(n, sh.WORK_START,(select convert(char(15),T_time,108) from V_LS where V_LS.T_date=TMS.T_date ))>0 then datediff(n, sh.WORK_START,(select convert(char(15),T_time,108) from V_LS where V_LS.T_date=TMS.T_date )) else 0 end as 迟到时间,
case when datediff(n, sh.WORK_END,(select convert(char(15),T_time,108) from V_LX where V_LX.T_date=dateadd(dd,1,TMS.T_date)))<0 then abs(datediff(n, sh.WORK_END,(select convert(char(15),T_time,108) from V_LX where V_LX.T_date=dateadd(dd,1,TMS.T_date)))) else 0 end as 早退时间,
case when datediff(n, sh.WORK_END,(select convert(char(15),T_time,108) from V_LX where V_LX.T_date=dateadd(dd,1,TMS.T_date)))>0 then round(datediff(n, sh.WORK_END,(select convert(char(15),T_time,108) from V_LX where V_LX.T_date=dateadd(dd,1,TMS.T_date)))/60.0,2) else 0 end as 加班时间,
case when (select convert(char(15),T_time,108) from V_LS where V_LS.T_date=TMS.T_date )<>' ' and (select convert(char(15),T_time,108) from V_LX where V_LX.T_date=dateadd(dd,1,TMS.T_date) ) <> '' then '0' else '1' end as 旷工
from shift_mst SH,
(select * from V_LS
union all
select * from V_LX) TMS
where SH.SHIFT_TYPE=TMS.shift_type

遇到一个难问题,请各位兄弟帮忙.在线等代......

相关推荐