22,209
社区成员
发帖
与我相关
我的任务
分享
create table ReceiverRecord
(
ID int identity(1,1) primary key not null,
[Typeid] int
,[ReceiverNumber] int
,[UserId] int
,[ReceiverDateTime] datetime
,TypeName nvarchar(100)
,EmpName nvarchar(100)
,BureauName nvarchar(100)
)
IF OBJECT_ID('[ReceiverRecord]') IS NOT NULL
DROP TABLE [ReceiverRecord]
go
CREATE TABLE [ReceiverRecord]
(
[ID] INT ,
[TypeID] INT ,
[ReceiverNumber] INT ,
[UserID] INT ,
[ReceiverDateTime] DATETIME ,
[TypeName] VARCHAR(6) ,
[EmpName] VARCHAR(6) ,
[BureauName] VARCHAR(6)
)
insert [ReceiverRecord]
select 15,20,10,30,'2014-07-16 19:42:29','记号笔','张三','技术部' union all
select 22,19,11,14,'2014-07-21 15:10:05','白板笔','王尼玛','广告部' union all
select 23,19,21,14,'2014-07-28 16:17:00','白板笔','王尼玛','广告部' union all
select 24,20,15,30,'2014-07-28 17:00:00','记号笔','张三','技术部' union all
select 25,20,22,14,'2014-07-28 17:30:00','记号笔','王尼玛','广告部'
GO
----------------------------------------------查询-------------------------------------
SELECT MIN(id) AS id,typeid,SUM(ReceiverNumber) ReceiverNumber,MAX(userid) userid,
min(ReceiverDateTime) ReceiverDateTime,TypeName,EmpName,MAX(BureauName) BureauName
FROM [ReceiverRecord] GROUP BY typeid,TypeName,EmpName ORDER BY MIN(id)
----------------------------------------------结果-------------------------------------
/*
id typeid ReceiverNumber userid ReceiverDateTime TypeName EmpName BureauName
----------- ----------- -------------- ----------- ----------------------- -------- ------- ----------
15 20 25 30 2014-07-16 19:42:29.000 记号笔 张三 技术部
22 19 32 14 2014-07-21 15:10:05.000 白板笔 王尼玛 广告部
25 20 22 14 2014-07-28 17:30:00.000 记号笔 王尼玛 广告部
*/
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-29 08:15:22
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[ReceiverRecord]
if object_id('[ReceiverRecord]') is not null drop table [ReceiverRecord]
go
create table [ReceiverRecord]([ID] int,[TypeID] int,[ReceiverNumber] int,[UserID] int,[ReceiverDateTime] datetime,[TypeName] varchar(6),[EmpName] varchar(6),[BureauName] varchar(6))
insert [ReceiverRecord]
select 15,20,10,30,'2014-07-16 19:42:29','记号笔','张三','技术部' union all
select 22,19,11,14,'2014-07-21 15:10:05','白板笔','王尼玛','广告部' union all
select 23,19,21,14,'2014-07-28 16:17:00','白板笔','王尼玛','广告部' union all
select 24,19,15,30,'2014-07-28 17:00:00','记号笔','张三','技术部' union all
select 25,20,22,14,'2014-07-28 17:30:00','记号笔','王尼玛','广告部'
--------------开始查询--------------------------
SELECT a.id ,
a.typeid ,
b.[ReceiverNumber] ,
a.userid ,
a.ReceiverDateTime ,
a.TypeName ,
a.EmpName ,
a.BureauName
FROM [ReceiverRecord] a
INNER JOIN ( SELECT typeid ,EmpName ,
SUM([ReceiverNumber]) [ReceiverNumber]
FROM ReceiverRecord
GROUP BY typeid,EmpName
) b ON a.typeid = b.typeid AND a.[ReceiverNumber]=b.[ReceiverNumber]
----------------结果----------------------------
/*
id typeid ReceiverNumber userid ReceiverDateTime TypeName EmpName BureauName
----------- ----------- -------------- ----------- ----------------------- -------- ------- ----------
15 20 10 30 2014-07-16 19:42:29.000 记号笔 张三 技术部
24 19 15 30 2014-07-28 17:00:00.000 记号笔 张三 技术部
25 20 22 14 2014-07-28 17:30:00.000 记号笔 王尼玛 广告部
*/
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-29 08:15:22
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[ReceiverRecord]
if object_id('[ReceiverRecord]') is not null drop table [ReceiverRecord]
go
create table [ReceiverRecord]([ID] int,[TypeID] int,[ReceiverNumber] int,[UserID] int,[ReceiverDateTime] datetime,[TypeName] varchar(6),[EmpName] varchar(6),[BureauName] varchar(6))
insert [ReceiverRecord]
select 15,20,10,30,'2014-07-16 19:42:29','记号笔','张三','技术部' union all
select 22,19,11,14,'2014-07-21 15:10:05','白板笔','王尼玛','广告部' union all
select 23,19,21,14,'2014-07-28 16:17:00','白板笔','王尼玛','广告部' union all
select 24,19,15,30,'2014-07-28 17:00:00','记号笔','张三','技术部' union all
select 25,20,22,14,'2014-07-28 17:30:00','记号笔','王尼玛','广告部'
--------------开始查询--------------------------
SELECT a.id ,
a.typeid ,
b.[ReceiverNumber] ,
a.userid ,
a.ReceiverDateTime ,
a.TypeName ,
a.EmpName ,
a.BureauName
FROM [ReceiverRecord] a
LEFT JOIN ( SELECT typeid ,
SUM([ReceiverNumber]) [ReceiverNumber]
FROM ReceiverRecord
GROUP BY typeid
) b ON a.typeid = b.typeid
WHERE BureauName='你要的部门名'
----------------结果----------------------------
/*
id typeid ReceiverNumber userid ReceiverDateTime TypeName EmpName BureauName
----------- ----------- -------------- ----------- ----------------------- -------- ------- ----------
15 20 32 30 2014-07-16 19:42:29.000 记号笔 张三 技术部
22 19 47 14 2014-07-21 15:10:05.000 白板笔 王尼玛 广告部
23 19 47 14 2014-07-28 16:17:00.000 白板笔 王尼玛 广告部
24 19 47 30 2014-07-28 17:00:00.000 记号笔 张三 技术部
25 20 32 14 2014-07-28 17:30:00.000 记号笔 王尼玛 广告部
*/