备份SQL

备份SQL

SELECT GOOD_ID,
       GOOD_NAME,
       FACTORY_ID,
       FACTORY_NAME,
       GOOD_USE_UNIT,
       sum(if(rk = 1, UNIT_PRICE, 0)) purchase_unit_price_1,
       sum(if(rk = 2, UNIT_PRICE, 0)) purchase_unit_price_2,
       sum(if(rk = 3, UNIT_PRICE, 0)) purchase_unit_price_3
FROM (
         SELECT id,
                GOOD_ID,
                GOOD_NAME,
                FACTORY_ID,
                FACTORY_NAME,
                GOOD_USE_UNIT,
                tmp.UNIT_PRICE,
                @rownum := @rownum + 1                                      row_num,
                IF(@pdept = tmp.GOOD_ID, @rank := @rank + 1, @rank := 1) AS rk,
                @pdept := tmp.GOOD_ID
         FROM (
                  SELECT id, GOOD_ID, GOOD_NAME, FACTORY_ID, FACTORY_NAME, UNIT_PRICE, GOOD_USE_UNIT
                  FROM GOOD_PURCHASE_SUMMARY
                  ORDER BY GOOD_ID asc, SUMMARY_TIME desc
              ) tmp,
              (SELECT @rownum := 0, @pdept := null, @rank := 0) a
     ) result
where 1 = 1
  #and GOOD_ID in (517, 571)
group by GOOD_ID, GOOD_NAME, FACTORY_ID, FACTORY_NAME, GOOD_USE_UNIT;