请SQL高手帮忙写一个UPDATE

[llj480028]

table: t_a

id: 12
p_id:1
data: 10

id:12
p_id:1
data:2

id:3
p_id:3
data:32

table :t_b
id:2
p_id:1
data:45

id:4
p_id:67
data:56

我是想用一条update 更改t_b 里和t_a p_id 相同的data

我是这样写的
update t_a as a left join t_b as b on a.p_id=b.p_id set b.data=b.data-a.data

但是这样运行时，只在t_b 里减了一次，不知道为什么呢？

[BooJS]

t_a怎么有两个id:12的数据?
问题要整理下呀.

[llj480028]

哈哈，已经搞定了，
update retail_goods_storage AS a
right JOIN (select sum(goods_storage_quantity) as b,single_goods_id from retail_sell_goods_info where 1 group by single_goods_id) AS c ON a.single_goods_id =c.single_goods_id
set a.goods_storage_quantity = a.goods_storage_quantity - c.b
写了一个NIU B的！

[life169]

确实NB 佩服

[life169]

我把 楼主的代码翻译一下

update t_b as b right join (select sum(data) as c,p_id from t_a group by p_id) as a on b.p_id=a.p_id set b.data=b.data-a.c

[llj480028]

[Quote=引用 9 楼 ms_x0828 的回复:]
楼主自己解决了看来没有分了哦
唉~
[/Quote]

分必须给大家滴…………

[llj480028]

ELECT a + b AS ab, c + d AS cd
FROM (

SELECT sum( single_goods_income ) AS a
FROM retail_sell_goods_info
WHERE retail_sell_id >=342
AND retail_sell_id <=718
) AS ab, (

SELECT sum( aaa ) AS b
FROM (

SELECT package_num * package_infact_income AS aaa
FROM retail_package_goods_relation AS b
WHERE settle_id >=342
AND settle_id <=718
GROUP BY settle_id, package_id
) AS c
) AS cb, (

SELECT sum( retail_sell_income ) AS c, sum( retail_exchange_total ) AS d
FROM retail_sell_settlement
WHERE retail_sell_id >=342
AND retail_sell_id <=718
) AS cd

看看一个很BT的，哈哈……，这只是测试用，要不非把mysql搞死不可！

[TM_skyinfo]

[Quote=引用 12 楼 llj480028 的回复:]

ELECT a + b AS ab, c + d AS cd
FROM (

SELECT sum( single_goods_income ) AS a
FROM retail_sell_goods_info
WHERE retail_sell_id >=342
AND retail_sell_id <=718
) AS ab, (

SELECT sum( aaa ) AS……
[/Quote]够BT

[ms_X0828]

楼主自己解决了看来没有分了哦
唉~

[ihefe]

[kaifadi]

[Quote=引用 5 楼 life169 的回复:]

我把 楼主的代码翻译一下

update t_b as b right join (select sum(data) as c,p_id from t_a group by p_id) as a on b.p_id=a.p_id set b.data=b.data-a.c
[/Quote]

谢谢，学习了。刚开始没看懂，现在看明白了。

[hiti8]

[Quote=引用 12 楼 llj480028 的回复:]
ELECT a + b AS ab, c + d AS cd
FROM (

SELECT sum( single_goods_income ) AS a
FROM retail_sell_goods_info
WHERE retail_sell_id >=342
AND retail_sell_id <=718
) AS ab, (

SELECT sum( aaa ) AS ……
[/Quote]
够BT..