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)
)
上面是查询出来的结果集，我想把typeid相同的 number相加合并。可以根据部门来查询。求告知。
------解决方案--------------------
按你说的，那么ReceiverNumber不是你那个哦
----------------------------------------------------------------
-- 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 记号笔 王尼玛 广告部
*/
------解决方案--------------------