相同结果合并 大概就是这样:select typeid,sum(ReceiverNumber) as xxfrom tbgroup by typeid 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 select Typeid, sum(ReceiverNumber) as Numberfrom ReceiverRecordwhere BureauName ='部门'group by Typeid ------------------------------------------------------------------ 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 allselect 22,19,11,14,'2014-07-21 15:10:05','白板笔','王尼玛','广告部' union allselect 23,19,21,14,'2014-07-28 16:17:00','白板笔','王尼玛','广告部' union allselect 24,19,15,30,'2014-07-28 17:00:00','记号笔','张三','技术部' union allselect 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.BureauNameFROM [ReceiverRecord] a LEFT JOIN ( SELECT typeid , SUM([ReceiverNumber]) [ReceiverNumber] FROM ReceiverRecord GROUP BY typeid ) b ON a.typeid = b.typeidWHERE 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 记号笔 王尼玛 广告部*/ /* 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 记号笔 王尼玛 广告部*/ 这是没加where条件还是加了的?也就是没有筛选部门还是筛选了? 你的ReceiverNumber 没按照typeid合并啊/* 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 记号笔 王尼玛 广告部*/ 不加where条件的,就是按typeid跟 empname来分组/* 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 记号笔 王尼玛 广告部*/ 按你说的,那么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 allselect 22,19,11,14,'2014-07-21 15:10:05','白板笔','王尼玛','广告部' union allselect 23,19,21,14,'2014-07-28 16:17:00','白板笔','王尼玛','广告部' union allselect 24,19,15,30,'2014-07-28 17:00:00','记号笔','张三','技术部' union allselect 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.BureauNameFROM [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 记号笔 王尼玛 广告部*/ /* 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 记号笔 王尼玛 广告部*/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 allselect 22,19,11,14,'2014-07-21 15:10:05','白板笔','王尼玛','广告部' union allselect 23,19,21,14,'2014-07-28 16:17:00','白板笔','王尼玛','广告部' union allselect 24,20,15,30,'2014-07-28 17:00:00','记号笔','张三','技术部' union allselect 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 记号笔 王尼玛 广告部*/ win2003安装MSSQL2005,找不到服务器 表分区的问题, 求助? 紧急求助一个郁闷的问题 获得数据库类型 这是乍回事呢?好像不应该吧? 怎样该字段的类型和长度 各位高手请进,给出您的建议。 请教[员工信息管理系统]里SQL设计问题,望大家指点一二。分不多,请见谅。。。。 SQL字符串截取 select:在线等…… 请教存储过程问题 如何使用游标的方法计算移动加权成本及结存数量及结存金额,分仓
from ReceiverRecord
where BureauName ='部门'
group by Typeid
-- 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 记号笔 王尼玛 广告部
*/
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 记号笔 王尼玛 广告部
*/
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 记号笔 王尼玛 广告部
*/
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 记号笔 王尼玛 广告部
*/
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 记号笔 王尼玛 广告部
*/
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 记号笔 王尼玛 广告部
*/