表名: 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如何写合适呢,谢谢!!
set A.p_num=p_num+B.p_num
from clientsreport A,clientsreport B
where A.brnum=B.rnum-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
----------------------------------------------------------------
-- 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 行受影响)*/
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 '007',44,10 union all
select '009',55,20 union all
select '005',66,30
---更新---
alter table tb add tid int identity(1,1);
goupdate a
set a.c_num=b.c_num,a.p_num=b.p_num
from tb a,
(select tid%3 as tid,sum(c_num) as c_num,sum(p_num) as p_num from tb group by tid%3) b
where a.tid%3=b.tid
godelete tb where brnum not in(select top 3 brnum from tb order by brnum)
goalter table tb drop column tid
go
---查询---
select * from [tb]---结果---
brnum c_num p_num
----- ----------- -----------
001 55 60
002 77 80
003 99 100(所影响的行数为 3 行)
谢谢小F支持,运行倒是可以,不过我库表中的 bra_num不是只差3这个值阿,所以不能用上面的条件阿,brnum c_num p_num 001 11 50
002 22 60
003 33 70
004 44 80007 44 10
009 55 20
005 66 30
006 77 40
结果: brnum c_num p_num 001 55 60
002 77 80
004 121 120001 = 001 + 007
002 = 002 + 009
004 = 004 + 006
min(brnum) brnum,
sum(c_num) c_num,
sum(p_num) p_num
from clientsreport
group by CONVERT(INT,brnum)%3
order by min(brnum);
brnum varchar(5),
c_num int,
p_num int );
insert into clientsreport(brnum, c_num, p_num)
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 union all select
'007',53,2;
SELECT
min(brnum) brnum,
sum(c_num) c_num,
sum(p_num) p_num
from clientsreport
group by CONVERT(INT,brnum)%3
order by min(brnum);
-----------------------------------------
brnum c_num p_num
001 108 62
002 77 80
003 99 100
brnum varchar(5),
c_num int,
p_num int,
insert into clientsreport(brnum, c_num, p_num)
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 union all selectSELECT
min(brnum) brnum,
sum(c_num) c_num,
sum(p_num) p_num
from clientsreport
group by CONVERT(INT,brnum)%3
order by min(brnum);
create table clientsreport(
brnum varchar(5),
c_num int,
p_num int );
insert into clientsreport(brnum, c_num, p_num)
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 union all select
'007',53,2;
SELECT
min(brnum) brnum,
sum(c_num) c_num,
sum(p_num) p_num
from clientsreport
group by CONVERT(INT,brnum)%3
order by min(brnum);
-----------------------------------------
brnum c_num p_num
001 108 62
002 77 80
003 99 100---------------------------------------更新语句
update clientsreport
set c_num=b.c_num, p_num=b.p_num
from clientsreport a,(SELECT
min(brnum) brnum,
sum(c_num) c_num,
sum(p_num) p_num
from clientsreport
group by CONVERT(INT,brnum)%3
) b
where a.brnum=b.brnum;--验证
select * from clientsreport
where brnum in ('001','002','003');
--------------------------------------------
brnum c_num p_num
001 108 62
002 77 80
003 99 100