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]

12012-03-15 09:09:31.0002012-03-15 20:09:31.0501
22012-03-15 08:09:31.0502012-03-15 18:09:31.0502
32012-03-15 07:30:31.0502012-03-15 20:09:31.0504

[解决办法]

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 allselect 2,'李四','IT' union allselect 3,'王五','IT' union allselect 4,'小傅','HR' union allselect 5,'小李','HR' union allselect 6,'小红','HR' union allselect 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 allselect 2,'2012-03-15 08:09:31.050','2012-03-15 18:09:31.050',2 union allselect 3,'2012-03-15 07:30:31.050','2012-03-15 20:09:31.050',4select [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].deptgroup by [user].[dept]deptname    avgtimeHR    12.650000IT    10.500000不大清楚你要干什么