表名: clientsreport
brnum c_num p_num 001 11 50
002 22 60
003 33 70004 44 10
005 55 20
006 66 30我想用 004 的数据加上 001 的数据,然后将结果置到001 数据中,005+002 =002 ;006+ 003 =003结果如下:brnum c_num p_num 001 55 60
002 77 80
003 99 100请问sql如何写合适呢,谢谢!!
brnum c_num p_num 001 11 50
002 22 60
003 33 70004 44 10
005 55 20
006 66 30我想用 004 的数据加上 001 的数据,然后将结果置到001 数据中,005+002 =002 ;006+ 003 =003结果如下:brnum c_num p_num 001 55 60
002 77 80
003 99 100请问sql如何写合适呢,谢谢!!
解决方案 »
- 如何在一个日期变量中减去5分钟
- sql server 2005 sql server agent 域帐户sqlstart, run job时出错
- SELECT @QuerryStr FROM ContactHistory的问题,,查询的列怎么使用变量
- 用户和表的问题
- 继续提问 full jion的修改
- 麻烦高手给我写一下这个存储过程,谢谢,不会写
- 这到底是怎么回事??各位大大帮忙!急.....
- 求证理解是否正确
- 在线等如何把查询中数据值转换成另外特定的数据值,比如把列A中为"1"的数据,显示为"正常"
- 大虾们帮我看看:在添加记录时有错误...?
- sql语句问题
- 请问这条sql有问题吗?急,谢谢!!
UPDATE a SET a.c_num =aa.c_num,p_num=aa.p_num
FROM
(SELECT right(1000+brnum*1%3,3) brnum,SUM(c_num) c_num ,SUM(p_num) p_num FROM a GROUP BY right(1000+brnum*1%3,3))aa
WHERE a.brnum=b.brnum
--> Author :
--> Date : 2009-12-04 13:21:24
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (brnum nvarchar(6),c_num int,p_num int)
insert into [tb]
select '001',11,50 union all
select '002',22,60 union all
select '003',33,70 union all
select '004',44,10 union all
select '005',55,20 union all
select '006',66,30
update a set c_num =a.c_num +b.c_num ,p_num=a.p_num+b.p_num
from tb a ,tb b
where a.brnum =b.brnum -3
delete tb where brnum >'003'
select * from tb
/*
brnum c_num p_num
------ ----------- -----------
001 55 60
002 77 80
003 99 100(3 個資料列受到影響)
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-04 13:26:42
-- 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]([brnum] varchar(3),[c_num] int,[p_num] int)
insert [tb]
select '001',11,50 union all
select '002',22,60 union all
select '003',33,70 union all
select '004',44,10 union all
select '005',55,20 union all
select '006',66,30
--------------开始查询--------------------------
update
a
set
a.[c_num] =b.[c_num],a.[p_num]=b.[p_num]
from
tb a,
(select right(1000+brnum*1%3,3) as brnum,sum(c_num) as c_num ,sum(p_num) as p_num from tb group by right(1000+brnum*1%3,3))b
where
a.brnum=b.brnum delete tb where brnum >'003'select * from tb
----------------结果----------------------------
/* brnum c_num p_num
----- ----------- -----------
001 55 60
002 77 80
003 33 70(3 行受影响)*/
brnum c_num p_num 001 11 50
002 22 60
003 33 70 007 44 10
009 55 20
005 66 30 结果:brnum c_num p_num 001 55 60
002 77 80
003 99 100
如果是2000的话
select id=identity(int),* into #t from tb
update a set c_num =a.c_num +b.c_num ,p_num=a.p_num+b.p_num
from #t a ,#t b
where a.brnum =b.brnum -3
delete tb where brnum >'003'
select * from tb
select id=identity(int),* into #t from tb
update a set c_num =a.c_num +b.c_num ,p_num=a.p_num+b.p_num
from #t a ,#t b
where a.id =b.id -3
delete tb where brnum >'003'
select * from tb-2005的话用row_number()over
bra_num之间差值不是3,还是没法做,郁闷~!
对不起,我再重新表述一下我的需求:表名: clientsreport
brnum c_num p_num 001 11 50
002 22 60
003 33 70 007 44 10
009 55 20
005 66 30 结果: brnum c_num p_num 001 55 60
002 77 80
003 99 100上面的执行条件是 : 将bra_num 为 007的c_num 值 加上 bra_num 为 001的c_num 值,然后将值赋给 bra_num 为 001的 c_num
CREATE TABLE uull(brnum VARCHAR(20),c_num INT, p_num INT)
INSERT uull SELECT '001',11,50
UNION ALL SELECT '002',22,60
UNION ALL SELECT '003',33,70
UNION ALL SELECT '007',44,10
UNION ALL SELECT '009',55,20
UNION ALL SELECT '005',66,30
go
ALTER TABLE uull ADD x INT
go
DECLARE @i INT
UPDATE uull SET @i=CASE WHEN @i>2 THEN 1 ELSE isnull(@i,0)+1 END ,x=@i
SELECT MIN(brnum) brnum,SUM(c_num) c_num,SUM(p_num) p_num FROM uull GROUP BY x--result
/*brnum c_num p_num
-------------------- ----------- -----------
001 55 60
002 77 80
003 99 100(所影响的行数为 3 行)
*/