sql查询各部门日平均上班时间解决方法
日期：2014-05-18 浏览次数：20770 次
sql查询各部门日平均上班时间
sql查询各部门日平均上班时间
表结构如下：
1 张三 IT
2 李四 IT
3 王五 IT
4 小傅 HR
5 小李 HR
6 小红 HR
7 小白 YW
CREATE TABLE [dbo].[Users](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Department] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkTime](
[ID] [int] IDENTITY(1,1) NOT NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[Department] [int] NULL,
CONSTRAINT [PK_WorkTime] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
1 2012-03-15 09:09:31.000 2012-03-15 20:09:31.050 1
2 2012-03-15 08:09:31.050 2012-03-15 18:09:31.050 2
3 2012-03-15 07:30:31.050 2012-03-15 20:09:31.050 4
------解决方案--------------------
sql查询各部门日平均上班时间
表结构如下：
1 张三 IT
2 李四 IT
3 王五 IT
4 小傅 HR
5 小李 HR
6 小红 HR
7 小白 YW
CREATE TABLE [dbo].[Users](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Department] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkTime](
[ID] [int] IDENTITY(1,1) NOT NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[Department] [int] NULL,
CONSTRAINT [PK_WorkTime] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
1 2012-03-15 09:09:31.000 2012-03-15 20:09:31.050 1
2 2012-03-15 08:09:31.050 2012-03-15 18:09:31.050 2
3 2012-03-15 07:30:31.050 2012-03-15 20:09:31.050 4
------解决方案--------------------
- SQL code
--> 测试数据：[user]
if object_id('[user]') is not null drop table [user]
create table [user]([id] int,[name] varchar(4),[dept] varchar(2))
insert [user]
select 1,'张三','IT' union all
select 2,'李四','IT' union all
select 3,'王五','IT' union all
select 4,'小傅','HR' union all
select 5,'小李','HR' union all
select 6,'小红','HR' union all
select 7,'小白','YW'
--> 测试数据：[dept]
if object_id('[dept]') is not null drop table [dept]
create table [dept]([id] int,[starttime] datetime,[endtime] datetime,[dept] int)
insert [dept]
select 1,'2012-03-15 09:09:31.000','2012-03-15 20:09:31.050',1 union all
select 2,'2012-03-15 08:09:31.050','2012-03-15 18:09:31.050',2 union all
select 3,'2012-03-15 07:30:31.050','2012-03-15 20:09:31.050',4
select [user].[dept] as deptname,avg(cast(datediff(mi,[starttime],
[endtime]) as decimal(10,2))/60)as avgtime
from [user] inner join [dept] on [user].id=[dept].dept
group by [user].[dept]
deptname avgtime
HR 12.650000
IT 10.500000
不大清楚你要干什么
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
