Re-insert the first table after multi-table union query to repeat data. Workaround

Select Goods_id,goods_type,goods_name,in_buy_price,round (SUM (in_total_price)/sum (In_amount), 2), COUNT (*) from In_ Warehouse_detail GROUP BY Goods_id,goods_type,goods_name ORDER by Goods_id,goods_type,goods_name

The query results are as follows:


Another association table "GOODS_ID Association of GOODS_ID and In_warehouse_detail tables through the goods table"

Select  from goods

Want to achieve the effect
① updates the In_warehouse_detail query to the average "ROUND (sum (in_total_price)/sum (In_amount), 2) column data to goods table Goods_buy_price column according to the Primary foreign Key association
② Query In_warehouse_detail table in_buy_price and ROUND (avg (In_buy_price), 2) These two columns of data of different values
③ Query goods Table goods_sel_price column and In_warehouse_detail table ROUND (avg (In_buy_price), 2) These two columns have different values of data

And then I figured out something that I thought I had to use a temp table to handle the query. Merge discovery

Select  from goods

Select g.goods_id,g.goods_name,g.goods_type,g.goods_sel_price,g.goods_buy_price           , A.junjia  from  Goods g,avg_prices a           where=  a.goods_id           and! = A.junjia

Plug the above query results into a temporary table

DROP TABLE IF EXISTSavg_prices; Create TableAvg_prices as      SelectGoods_id,goods_type,goods_name,in_buy_price,ROUND(sum(In_total_price)/sum(In_amount),2) as 'Junjia',Count(*) fromScm.in_warehouse_detailGroup  byGoods_id,goods_type,goods_nameOrder  byGoods_id,goods_type,goods_name;

Querying data from a generated temporary table

Select *  from     Avg_prices

Then the problem solved becomes how to change the first table Junjia field to batch update to the second Table goods table goods_buy_price field value according to the ID condition

Through personal testing and finally realizing

Thanks to the sinking inspired me to sentence

update A.junjia from  set=  a.junji           where= a.goods_id

The implementation of the statement as follows can be added a timer daily execution can solve the real-time average price problem

1 DROP TABLE IF EXISTSavg_prices;2  3  Create TableAvg_prices as4       SelectGoods_id,goods_type,goods_name,in_buy_price,5 ROUND(sum(In_total_price)/sum(In_amount),2) as 'Junjia',Count(*) fromScm.in_warehouse_detail6  Group  byGoods_id,goods_type,goods_name7  Order  byGoods_id,goods_type,goods_name;

Source

---------If the current database contains an average price temporary table is deleted---------DROP TABLE IF EXISTSavg_prices;---------Create an average temp table and populate the query results into a temporary table--------- Create TableAvg_prices as      SelectGoods_id,goods_type,goods_name,in_buy_price,ROUND(sum(In_total_price)/sum(In_amount),2) as 'Junjia',Count(*) fromScm.in_warehouse_detailGroup  byGoods_id,goods_type,goods_nameOrder  byGoods_id,goods_type,goods_name;---------Batch Update goods table based on ID Association UpdateAvg_prices A,goods GSetG.goods_buy_price=A.junjiawhereg.goods_id=a.goods_id andG.goods_sel_price!=A.junjia;

Inventory can also try to bulk update the goods table

Re-insert the first table after multi-table union query to repeat data. Workaround