有个表T,结构如下
ID:int
GroupID:int
Price:int现在想把GroupID=1和GroupID=2的相同ID的项的Price进行求和,写到GroupID=1的那个ID的Price里比如
GroupID ID Price
1 100 1000
2 100 2000
1 101 500
2 101 500执行完后
GroupID ID Price
1 100 3000
2 100 2000
1 101 1000
2 101 500我这样写,貌似语法有问题求解
update hockshop_item as T1 set Price=(select sum(Price) from hockshop_item where (GroupID=1 or GroupID=2) and ID=T1.ID)sql
ID:int
GroupID:int
Price:int现在想把GroupID=1和GroupID=2的相同ID的项的Price进行求和,写到GroupID=1的那个ID的Price里比如
GroupID ID Price
1 100 1000
2 100 2000
1 101 500
2 101 500执行完后
GroupID ID Price
1 100 3000
2 100 2000
1 101 1000
2 101 500我这样写,貌似语法有问题求解
update hockshop_item as T1 set Price=(select sum(Price) from hockshop_item where (GroupID=1 or GroupID=2) and ID=T1.ID)sql
update a set a.price=(select sum(price) from table b
where a.id=b.id)
from table a
where a.groupid=1
create table tb
(
GroupID int,
ID int,
Price int
)insert into tb values(1,100,1000)
insert into tb values(2,100,2000)
insert into tb values(1,101,500)
insert into tb values(2,101,500)update a set a.price = b.price
from tb a, (
select id,sum(price)price from tb
where groupid in(1,2)
group by id) b
where a.groupid = 1select * from tb
/*
GroupID ID Price
1 100 3000
2 100 2000
1 101 1000
2 101 500(4 行受影响)
insert @t select 1, 100 , 1000
union all select 2 , 100 , 2000
union all select 1 , 101 , 500
union all select 2 , 101, 500update a set n = N + (select SUM(n) m from @t where groupid=a.groupid and id=2)
from @t a
where a.id=1
select * from @t
大神,这个在MSSQL没问题,要是MySQL下怎么写呢,我突然发现我们系统是MySQL的- -!
也没用刀什么东西,就一个 sum函数,两表内连接,分组函数。分别查一下,MySQL对应的怎么写就可以了