Geek Answers Handbook

MySql query - subtract the given value

I need help with a math function ,

The following query will result in two lines after the lotfollowing:

group | style | lot | section | q1 | q2 |q3  | q4  | ...

aaaaa | sssss | 123 | 111111  | 55 | 77 | 88 | 99 | ...

aaaaa | sssss | 123 | 222222  | 10 | 20 | 20 | 10 | ...

aaaaa | sssss | 321 | 111111  | 11 | 22 | 44 | 55 | ...

aaaaa | sssss | 321 | 222222  | 10 | 23 |33  | 10  | ...

each lotresult 2 diff section code(2 lines)

Question : how to do subtraction between 2 section codefor columns q1 q2 q3 q4 q5 ...?

Expected results:

group | style | lot | q1 | q2 |q3 | q4  | ...

aaaaa | sssss | 123 | 45 | 57 |68 | 89 | ...

aaaaa | sssss | 321 | 1  | -1 |11 | 45 | ...

request :

SELECT DISTINCT gp_style_gr.code_groupe, po_lot.num_style, po_lot_sp.Num_lot,
po_lot_sp.num_secti, po_lot_se.code_secti, po_lot.terminer, po_lot.date_livraison,
po_lot_sp.qte_1, po_lot_sp.qte_2, po_lot_sp.qte_3, po_lot_sp.qte_4, po_lot_sp.qte_5,
po_lot_sp.qte_6, po_lot_sp.qte_7, po_lot_sp.qte_8, po_lot_sp.qte_9, po_lot_sp.qte_10,
po_lot_sp.qte_11, po_lot_sp.qte_12, po_lot_sp.qte_13, po_lot_sp.qte_14, po_lot_sp.qte_15,
po_lot_sp.qte_16, po_lot_sp.qte_17, po_lot_sp.qte_18, po_lot_sp.qte_19, po_lot_sp.qte_20,
po_lot_sp.qte_21, po_lot_sp.qte_22, po_lot_sp.qte_23, po_lot_sp.qte_24, po_lot_sp.qte_25,
po_lot_sp.qte_26, po_lot_sp.qte_27, po_lot_sp.qte_28, po_lot_sp.qte_29, po_lot_sp.qte_30

FROM po_lot_sp

LEFT OUTER JOIN po_lot_se ON po_lot_se.num_lot = po_lot_sp.num_lot 
and po_lot_se.num_secti = po_lot_sp.num_secti

LEFT OUTER JOIN po_lot ON po_lot.num_lot = po_lot_sp.num_lot

LEFT OUTER JOIN gp_style_gr ON gp_style_gr.num_style = po_lot.num_style

WHERE
((gp_style_gr.code_groupe = 'INSTOCK') and (po_lot.terminer = '0') 
and (po_lot_se.code_secti = '01')) or ((gp_style_gr.code_groupe = 'INSTOCK') 
and (po_lot.terminer = '0') and (po_lot_se.code_secti = '09'))

ORDER BY gp_style_gr.code_groupe, po_lot.num_style, po_lot_sp.Num_lot, 
po_lot_sp.num_secti, po_lot_se.code_secti, po_lot.terminer, po_lot.date_livraison,

Thanks!

+4
source share
3 answers

If the section code follows some pattern, as in your example, you can simply join the table against yourself.

I will pretend that your table is called po_lot_sp, as in your example.

, . t1.section > t2.section. , . , .

SELECT t1.`group`, t1.style, t1.lot, t1.section,
  t2.q1 - t1.q1 q1, t2.q2 - t1.q2 q2, t2.q3 - t1.q3 q3, t2.q4 - t1.q4 q4
FROM t t1
JOIN t t2 ON t1.`group` = t2.`group` AND t1.style = t2.style AND
  t1.lot = t2.lot AND t1.section > t2.section

Fiddle .

+2

:

select a.group,a.style,a.lot, 
coalesce(a.q1 - 
(select b.q1 from  tablename b where b.ID = a.ID + 1), a.q1) as q1,  
coalesce(a.q2- 
(select b.q2 from  tablename b where b.ID = a.ID + 1), a.q2) as q2,
coalesce(a.q3- 
(select b.q3 from  tablename b where b.ID = a.ID + 1), a.q3) as q3,
coalesce(a.q4- 
(select b.q4 from  tablename b where b.ID = a.ID + 1), a.q4) as q4
from tablename a group by a.lot

: ID , tablename . ID .

0

, . , , "111111":

SELECT `group`, style, lot,
  sum(if(section = '111111', q1, -q1)),
  sum(if(section = '111111', q2, -q2)),
  sum(if(section = '111111', q3, -q3)),
  sum(if(section = '111111', q4, -q4))
FROM t
GROUP BY `group`, style, lot

Fiddle .

, group . .

If you do not know the value of the section that you want to reduce, and you only want to reduce it from the bottom, then go to the Andy solution.

0
source

More articles:


All Articles