两张表
User: UID, U_Status, U_Point
Goods: UID, G_PointGoods表中可能有多个相同的UID, 把相同UID的G_Point合计一下,追加到User表中,且U_Status=1
也就是说:
User.U_Point = User.U_Point + Sum(Goods.G_Point)
User.UID = Goods.UID
User.U_Status = 1这个存储过程应该怎么写?
User: UID, U_Status, U_Point
Goods: UID, G_PointGoods表中可能有多个相同的UID, 把相同UID的G_Point合计一下,追加到User表中,且U_Status=1
也就是说:
User.U_Point = User.U_Point + Sum(Goods.G_Point)
User.UID = Goods.UID
User.U_Status = 1这个存储过程应该怎么写?
解决方案 »
- 菜鸟求助一个关于sql的查询语句
- 关于关系操作问题
- 该属性在外部数据源或用低版本的microsoft jet所创建的数据库中不受支持
- 求教,如何在存储过程利用敲击某个键,实现某个事件,例如敲回车键
- 在server2000中 怎样能使一个字段的值 根据其他字段和规则自动赋值。而不用sql语句插入
- 怎么复制不同数据库之间的表???在线等!!!!!
- 关于给成绩排名的问题,各位高手帮帮忙 (心急人上)
- 存储过程问题 请高手指教 急急急急急急!!!!!!
- 配置了路由和远程访问后,安装不上sql server2000,该如何解决
- 不太会,请指导(´;︵;`)
- UPDATE问题 急啊~~
- 对数据库多张表高频率的读写问题,在线等,马上给分!!!
from User a
join (select Uid,sum(G_Point) G_Point from Goods group by UID )b on a.UID = b.Uid
where U_Status = 1
update a
set U_Point=a.U_Point+b.con
from [user] a join (select count(1) as con,UID from Goods group by UID) b on a.UID=b.UID
set U_Point=a.U_Point+b.con
from
[user] a
join
(select count(1) as con,UID from Goods group by UID) b on a.UID=b.UID
and a.U_Status = 1 --------
update [user]set U_Point=U_Point+isnull((select count(1) from Goods where UID=[User].UID),0)
where
U_Status = 1
set a.U_Point = a.U_Point + b.G_Point
from user a,
(select UID,sum(G_Point) as G_Point from goods group by UID) b
where a.UID = b.UID
and a.U_Status = 1
as begin
update a set U_Point = U_Point + b.G_Point
from [User] a
join (select Uid,sum(G_Point) G_Point from Goods group by UID )b on a.UID = b.Uid
where U_Status = 1
end
update a
set U_Point=a.U_Point+b.con
from
[user] a
join
(select sum(G_Point) as con,UID from Goods group by UID) b on a.UID=b.UID
and a.U_Status = 1 --------
update [user]set U_Point=U_Point+isnull((select sum(G_Point) from Goods where UID=[User].UID),0)
where
U_Status = 1
create proc pr_sumPoint
as
begin
update user
set a.U_Point = a.U_Point + b.G_Point
from user a,
(select UID,sum(G_Point) as G_Point from goods group by UID) b
where a.UID = b.UID
and a.U_Status = 1
end
-- 准备
create table users(
uid varchar(20),
u_status varchar(20),
u_point int
)create table goods(
uid varchar(20),
g_point int
)
insert into users
select 'a',null,0 union all
select 'b',null,0 union all
select 'c',null,0 insert into Goods
select 'a',1 union all
select 'a',2 union all
select 'b',20 -- 更改
update users
set u_status = (select 1 from goods where goods.uid = users.uid group by goods.uid),
u_point = (select sum(g_point) from goods where goods.uid = users.uid group by goods.uid)
uid u_status u_point
-------------------- -------------------- -----------
a NULL 0
b NULL 0
c NULL 0
uid g_point
-------------------- -----------
a 1
a 2
b 20
结果:
uid u_status u_point
-------------------- -------------------- -----------
a 1 3
b 1 20
c NULL NULL(所影响的行数为 3 行)uid g_point
-------------------- -----------
a 1
a 2
b 20(所影响的行数为 3 行)
我晕,呵呵
from User a
join (select Uid,sum(G_Point) G_Point from Goods group by UID )b on a.UID = b.Uid
where U_Status = 1