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 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不大清楚你要干什么