如何重新计算GROUP上的字段 [英] How to recalculate a field on GROUP

问题描述

我有一张桌子

f1|f2|fk
1 | 0|100
1 | 1|200
1 | 2|300
1 | 3|400
2 | 0|300
2 | 1|400
2 | 2|500

其中(f1,f2)是PK，fk是外键. fk的值并不多，大约20.f2永远不会超过3.但是，有很多(f1,f2)对. (f1,fk)是UNIQUE.

Where (f1,f2) is the PK and fk is a foreign key. There are not many values for fk, about 20. f2 is never more than 3. There are a lot of (f1,f2) pairs, however. (f1,fk) is UNIQUE.

现在，假设我们正在将100和200映射到A，将300和400映射到B，将500映射到C-这个映射是给定的.我们希望(f1_new,fk_mapped)保持UNIQUE.到目前为止，大致可以通过以下方式解决

Now, say we are mapping 100 and 200 to A, 300 and 400 to B, 500 to C -- this mapping is a given. We would like (f1_new,fk_mapped) to stay UNIQUE. This is so far solved roughly by

INSERT INTO mapped (f1_new,f2_new,fk_new)
SELECT f1, MIN(f2), CASE fk WHEN 100 THEN A WHEN 200 THEN A END AS fk
FROM origin
GROUP BY f1, fk

现在，问题在于我们需要在映射表中保留f2值0、1、2，所以这是期望的结果:

Now, the problem is that we need to keep f2 values 0, 1, 2 in the mapped table, so this is the desired result:

f1_new|f2_new|fk_mapped
1     | 0    |A
1     | 1    |B
2     | 0    |B
2     | 1    |C

我真的很想把它保留在MySQL中.

I really would like to keep this within MySQL.

推荐答案

我认为应该这样做:

INSERT INTO mapped (f1_new, f2_new, fk_new)
SELECT f1_new, f2_new, fk_new from (
    SELECT f1_new, @f2 := if(f1_new = @prev_f1, @f2+1, 0) f2_new, fk_new, @prev_f1 := f1_new
    FROM (select f1 AS f1_new, CASE WHEN fk IN (100, 200) THEN 'A'
                                    WHEN fk in (300, 400) THEN 'B'
                                    WHEN fk = 500 THEN 'C'
                               END AS fk_new
          FROM origin
          GROUP BY f1_new, fk_new
          ORDER BY f1_new, fk_new) new,
         (SELECT @f2 := NULL, @prev_f1 := NULL) vars
    ) x

FIDDLE

这篇关于如何重新计算GROUP上的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！