紧急求援--如何求在一张表中,具有相同字段a的值的所有记录的b字段的和

iloveyoubaby 2009-11-03 04:18:45
比如我有一张表,有a,b两个字段,
cpdm cpxse
06100100 44767
06100100 53215.2
06100200 13241
06100200 12315
06100200 13244
06100200 56898
06100200 464747
06100200 3467
06100200 45477
请问如何将具有相同cpdm值的记录的cpxse值求和,也就是说上面这些记录进行求和处理后只有两条记录

...全文
75 7 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
xiequan2 2009-11-03
  • 打赏
  • 举报
回复
select cpdm,sum(cpxse) as cpxse from tb group by cpdm
SQL77 2009-11-03
  • 打赏
  • 举报
回复
/*
-- Author:SQL77--RICHIE
-- Version:V1.001 Date:2008-05-15--转Flystone

*/

-- Test Data: TB
If object_id('TB') is not null
Drop table TB
Go
Create table TB(cpdm int,cpxse int)
Go
Insert into TB
select 06100100,44767 union all
select 06100100,53215.2 union all
select 06100200,13241 union all
select 06100200,12315 union all
select 06100200,13244 union all
select 06100200,56898 union all
select 06100200,464747 union all
select 06100200,3467 union all
select 06100200,45477
Go
--Start
SELECT cpdm,SUM(cpxse )AS cpxse FROM TB GROUP BY cpdm


--Result:
/*

(所影响的行数为 9 行)

cpdm cpxse
----------- -----------
6100100 97982
6100200 609389

(所影响的行数为 2 行)



*/
--End
bancxc 2009-11-03
  • 打赏
  • 举报
回复
create table test(cpdm nvarchar(10),cpxse int )
insert into test select
'06100100',44767 union all select
'06100100',53215.2 union all select
'06100200',13241 union all select
'06100200',12315 union all select
'06100200',13244 union all select
'06100200',56898 union all select
'06100200',464747 union all select
'06100200',3467 union all select
'06100200',45477

select cpdm,cpxse=SUM(cpxse)
from test
group by cpdm
/*cpdm cpxse
---------- -----------
06100100 97982
06100200 609389

(2 行受影响)
*/

drop table test
--小F-- 2009-11-03
  • 打赏
  • 举报
回复
不好意思 写掉了from tb
--小F-- 2009-11-03
  • 打赏
  • 举报
回复
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-03 16:20:08
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([cpdm] varchar(8),[cpxse] numeric(7,1))
insert [tb]
select '06100100',44767 union all
select '06100100',53215.2 union all
select '06100200',13241 union all
select '06100200',12315 union all
select '06100200',13244 union all
select '06100200',56898 union all
select '06100200',464747 union all
select '06100200',3467 union all
select '06100200',45477
--------------开始查询--------------------------
select cpdm,sum(cpxse) as cpxse from tb group by cpdm
----------------结果----------------------------
/* cpdm cpxse
-------- ---------------------------------------
06100100 97982.2
06100200 609389.0

(2 行受影响)
*/
SQL77 2009-11-03
  • 打赏
  • 举报
回复
SELECT cpdm,SUM(cpxse )AS cpxse  FROM TB GROUP BY cpdm
--小F-- 2009-11-03
  • 打赏
  • 举报
回复
select cpdm,sum(cpxse) as cpxse group by cpdm

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧