mysql联表聚合更新

运行下列sql更新查询即可实现提问中的需求:

update order,(select order_id,

sum(goods_num*goods_price) as m

from order_goods group by order_id) b

set order.goods_total=b.m

where order.order_id=b.order_id

生成数据透视表

set @sql = 'SELECT [name], ' select @sql = @sql + 'sum(case book when '+quotename(book,'''')+' then saledNumber else 0 end) as ' + quotename(book)+',' from s group by book select @sql = left(@sql,len(@sql)-1) select @sql = @sql + ', sum(saledNumber) as [sum] from s group by [name]' select @sql exec(@sql)

---