数据库的一张表
表1:
id cid pid count
1 c1 p1 1
2 c1 p2 3
3 c2 p2 2
4 c2 p3 2
...我想把cid字段c2更新成c1,然后pid字段重复的删除,count相加,得到如下表.id cid pid count
1 c1 p1 1
2 c1 p2 5
3 c1 p3 2
...我有个办法实现,但还是想请教下有什么最好的办法.
表1:
id cid pid count
1 c1 p1 1
2 c1 p2 3
3 c2 p2 2
4 c2 p3 2
...我想把cid字段c2更新成c1,然后pid字段重复的删除,count相加,得到如下表.id cid pid count
1 c1 p1 1
2 c1 p2 5
3 c1 p3 2
...我有个办法实现,但还是想请教下有什么最好的办法.
解决方案 »
- 昨天在.NET区发了一个关于SQL的帖子无人问津
- 简单的,SQL 汇总问题
- T-SQL中如何声明一个带参数的游标?
- 重复项过滤 + 排序
- opendatasource只能对存在于本机文件夹下的相关文件操作,有何办法对其它机器上的文件?
- 怎样禁止sql数据库访问用户使用 exec ?
- mysql 迁移到 sql server 2005
- 请问如何使用一句 Select 语句生成自动序号。在线等待,立即给分!
- 把sql7.0恢复時,user的信息如何恢复?
- 关于创建触发器的问题
- 100分求写一个存储过程!!!
- MS SQL 中有没有类似于VB中format的函数,做到? format(0.4,“0.0000”) => “0.4000”这样的格式化小数的效果
from ta
group by cid,pid
update tb t set [count] = (select sum([count]) from tb where pid = t.pid)
delete tb from tb t where [count] not in (select max([count]) from tb where pid = t.pid)
update tb set cid = 'c1'
insert into tb select 1,'c1','p1',1
insert into tb select 2,'c1','p2',3
insert into tb select 3,'c2','p2',2
insert into tb select 4,'c2','p3',2 update tb set cid='c1'select * into # from (
select min(id) as id,cid,pid,sum([count]) as [count]
from Tb
group by cid,pid)tpdrop table tbselect * into tb from #select * from tb
id cid pid count
1 c1 p1 1
2 c1 p2 5
4 c1 p3 2
Create table tc(Id int,cid varchar(10),pId varchar(10),[count] int)
insert into tc select 1,'c1','p1',1
insert into tc select 2,'c1','p2',3
insert into tc select 3,'c2','p2',2
insert into tc select 4,'c2','p3',2
select 'c1' as cId,Pid,sum([count]) as [count] from tc
where cid in ('c1','c2')
group by Pid/*
cId Pid count
---- ---------- -----------
c1 p1 1
c1 p2 5
c1 p3 2(3 行受影响)
*/
Create table tc(Id int,cid varchar(10),pId varchar(10),[count] int)
insert into tc select 1,'c1','p1',1
insert into tc select 2,'c1','p2',3
insert into tc select 3,'c2','p2',2
insert into tc select 4,'c2','p3',2
--创建存储过程
Create procedure Proc_Name
as
beginupdate tc set cid='c1'
where cid='c2'update a set [count]=(select sum([count]) from tc where cid=a.cid and pid=a.pid ) from tc a
delete a from tc a
where exists(select 1 from tc b where a.cid=b.cid and a.pid=b.pid and a.id>b.id)end--执行
exec proc_nameselect * from tc/*
Id cid pId count
----------- ---------- ---------- -----------
1 c1 p1 1
2 c1 p2 5
4 c1 p3 2(3 行受影响)×/
as
begin
--这里
end就行了
create table tb(id int,cid varchar(10),pid varchar(10),[count] int)
insert into tb select 1,'c1','p1',1
insert into tb select 2,'c1','p2',3
insert into tb select 3,'c2','p2',2
insert into tb select 4,'c2','p3',2
select id=identity(int,1,1),'c1'cid,pid,sum([count])[count] into # from tb group by pidselect * from #
1. 根据c2 更新c1中pid相同的字段,
2.插入统计c2中c1中没有pid的记录,然后插入
3.删除cid=c2的记录
*/
--设表1的表名为tt
update a
set pid=a.[count]+b.[count]
from tt a,(select pid,sum([count]) [count] from tt where cid='C2' group by pid) b
where a.pid=b.pidinsert into tt (cid,pid,[count])
select 'c1',pid,sum([count])
from tt a where cid='c2'
and not exists(select * from tt where cid='c1' and pid=a.pid)
group by piddelete tt where cid='c2'