mysql - 我需要更改mysql查询中的选定值(i need to change the selected value in mysql query)

Question

i need help with this mysql query, as i need to read kod value as different value during join as query below, your help realy needed

(我需要有关此mysql查询的帮助，因为我需要在下面的查询联接期间将kod值读取为其他值，因此确实需要您的帮助)

SELECT A.CODE_MAIN,A.CODE_DEP,
(SELECT IFNULL(SUM(amaunt),0) from ca_resit_trx 
WHERE DATE_DOC BETWEEN '2019-01-01' AND '2019-12-01' 
AND KOD_JAB=A.CODE_DEP
AND 
(case 
  when CODE_TRX= '110101' then '410101'
  when CODE_TRX= '110201' then '420301'
  else CODE_TRX
end as kod)=A.CODE_MAIN) trans
FROM vt_hasildetail A
WHERE A.REF_DATE BETWEEN '2019-01-01' AND '2019-12-01'
AND A.CODE_MAIN LIKE '4%'
AND A.CODE_DEP= '04' 
GROUP BY A.CODE_DEP, A.CODE_MAIN 
ORDER BY A.CODE_DEP, A.CODE_MAIN 

i need to read CODE_TRX from ca_resit_trx 110101 as 410101, to join with CODE_MAIN from vt_hasildetail...please help

(我需要从ca_resit_trx 110101中读取CODE_TRX为410101，才能从vt_hasildetail与CODE_MAIN一起加入...请帮助)

  ask by Jehe Acoroxz translate from so

Answer 1

if you want the sum(amount) for remapped kod code you could use inner join based on your refactored subquery

(如果要重映射的kod代码的总和（金额），则可以根据重构的子查询使用内部联接)

    SELECT A.CODE_MAIN,A.CODE_DEP,t.tot
    FROM vt_hasildetail A
    INNER JOIN  (
      SELECT KOD_JAB
      ,case 
          when CODE_TRX= '110101' then '410101'
          when CODE_TRX= '110201' then '420301'
            else CODE_TRX 
        end kod
      , IFNULL(SUM(amaunt),0) 
      from ca_resit_trx 
      WHERE DATE_DOC BETWEEN '2019-01-01' AND '2019-12-01'
      GROUP BY KOD_JAB, kod

    ) t ON t.KOD_JAB = A.CODE_DEP
      AND t.kod = A.CODE_MAIN
      AND A.REF_DATE BETWEEN '2019-01-01' AND '2019-12-01'
        AND A.CODE_MAIN LIKE '4%'
        AND A.CODE_DEP= '04' 
    ORDER BY A.CODE_DEP, A.CODE_MAIN