有以下例子
列: A B C ....
数据 1 10
1 20
1 30
2 5
2 6
我 想输出以下结果:
列: A B C ....
数据 1 60 10
1 60 20
1 60 30
2 11 5
2 11 6
60 = 10+20+30
11 = 5+6
请教sql怎么实现?多谢
列: A B C ....
数据 1 10
1 20
1 30
2 5
2 6
我 想输出以下结果:
列: A B C ....
数据 1 60 10
1 60 20
1 60 30
2 11 5
2 11 6
60 = 10+20+30
11 = 5+6
请教sql怎么实现?多谢
解决方案 »
- 问一个菜鸟问题
- SQLserverExpress 换为SQLServer2005标准版后,web.config中数据库连接字符串怎们改
- 如何判断某个数据库里是否含有自定义的一个函数?
- 请教一个统计报表的sql serve 语句 谢谢大家帮助!
- 如何让SQL Server2005将数据库备份以附件的方式发送到指定邮箱?
- 如何通过BDE连接SQL Server数据库?
- 帮忙
- 一个简单的sql语句
- 存储过程动态指定排序接口问题?
- 班长,请教一个使用索引的问题,请解答 ^!*
- 咨询SQL SERVER20000日期字段显示问题
- 知道max() group by 排重复 怎么用,但还是不理解max()的用法
--> Author : wufeng4552
--> Date : 2010-02-10 15:58:02
if not object_id('tb') is null
drop table tb
Go
Create table tb([A] int,[B] int,[C] int)
Insert tb
select 1,null,10 union all
select 1,null,20 union all
select 1,null,30 union all
select 2,null,5 union all
select 2,null,6
Go
Select [A],
(select sum([c])from tb where [A]=t.[A])B,
[C]
from tb t
/*
A B C
----------- ----------- -----------
1 60 10
1 60 20
1 60 30
2 11 5
2 11 6(5 個資料列受到影響)*/
from tb a
join
(select a,sum(c) as c from tb group by a) b
on a.a=b.a
declare @tb table([A] int,[C] int)
insert @tb
select 1,10 union all
select 1,20 union all
select 1,30 union all
select 2,5 union all
select 2,6select a,(select sum(c) from @tb where a=a.a ) as b ,c from @tb a
/*a b c
----------- ----------- -----------
1 60 10
1 60 20
1 60 30
2 11 5
2 11 6*/
drop table tb
Go
Create table tb([A] int,[B] int,[C] int)
Insert tb
select 1,null,10 union all
select 1,null,20 union all
select 1,null,30 union all
select 2,null,5 union all
select 2,null,6
update a
set b=(select sum(c) from tb where a.a=a)
from tb aselect * from tb
-- SQL 2005
select a, b=sum(c) over (partition by a), c from tab
select a,
b=(select sum(c) from tt where a=ta.a),
c
from tt as ta
--假设你的表名是your_table
--先更新your_table中列B
update a
set B = b.sumC
from your_table a,
(
select sum(C) as sumC
from your_table
group by A
) b
where a.A = b.A
--输出结果
select * from your_table;
CREATE TABLE #testing_For
(A int,
B int,
C int
)
GO
INSERT INTO #testing_For VALUES(1,NULL,10)
INSERT INTO #testing_For VALUES(1,NULL,20)
INSERT INTO #testing_For VALUES(1,NULL,30)
INSERT INTO #testing_For VALUES(2,NULL,5)
INSERT INTO #testing_For VALUES(2,NULL,6)
GO
--2)如果是SELECT
SELECT t.A,B = B.t_Sum,t.C
FROM
#testing_For t
INNER JOIN
(SELECT t_A = t.A,t_Sum = SUM(C)
FROM #testing_For t
GROUP BY t.A)B
ON B.t_A = t.A--3如果是UPDATE
BEGIN TRAN
UPDATE t
SET t.B = B.t_Sum
FROM #testing_For t
INNER JOIN
(SELECT t_A = t.A,t_Sum = SUM(C)
FROM #testing_For t
GROUP BY t.A)B
ON B.t_A = t.A
--3.1测试
SELECT * FROM #testing_For
ROLLBACK TRAN
drop table tb
Go
Create table tb([A] int,[B] int,[C] int)
Insert tb
select 1,null,10 union all
select 1,null,20 union all
select 1,null,30 union all
select 2,null,5 union all
select 2,null,6
Go
--2000 子查询
Select A,[B]=(select sum(c)from tb where A=t.A),C
from tb t
--2005
--1.apply
select a,k.b,c
from tb t cross apply (select SUM(c)as b from tb where [A]=t.[A]) k
--2.over
select a,b=SUM(C)OVER(PARTITION BY A),C
FROM tb
/*
a b C
----------- ----------- -----------
1 60 10
1 60 20
1 60 30
2 11 5
2 11 6
*/
drop table temp1
Go
Create table temp1([A] int,[B] int,[C] int)
Insert temp1
select 1,null,10 union all
select 1,null,20 union all
select 1,null,30 union all
select 2,null,5 union all
select 2,null,6
Goselect * from temp1
select t.A,t1.B,t.C
from temp1 t
left join
(select a,sum(c) as B from temp1 group by a) as t1
on t.A=t1.A