mysql中update语句怎么设置变量,如何在UPDATE语句中使用用户定义的变量？

I was trying to answer another SO question and was suddenly faced with the following problem. Points should be assigned to the 3 highest scoring (mrk) groups (grp) of each class (sec). The highest scoring groups get 5 points, the second ranking ones 3 points and the groups in 3rd rank only 1 point. For all others pts should be set to null.

| ID | SEC | GRP | MRK | PTS |

|----|-----|-----|-----|--------|

| 1 | cl2 | ge | 32 | (null) |

| 2 | cl1 | gb | 22 | (null) |

| 3 | cl1 | gd | 22 | (null) |

| 4 | cl1 | ge | 18 | (null) |

| 5 | cl2 | ga | 26 | (null) |

| 6 | cl1 | ga | 55 | (null) |

| 7 | cl2 | gb | 66 | (null) |

| 8 | cl2 | gc | 15 | (null) |

| 9 | cl1 | gc | 12 | (null) |

| 10 | cl2 | gf | 5 | (null) |

| 11 | cl2 | ge | 66 | (null) |

I chose to work with user-defined variables as they provide maximum flexibility regarding the allocation scheme and soon came up with the following solution:

SELECT id,sec,grp,mrk,

CASE WHEN @s=sec THEN -- whenever there is a new class ...

CASE WHEN @m=mrk THEN @i ELSE -- issue the same points for

-- identical scorers, otherwise ...

CASE WHEN IF(@m:=mrk,@i,@i)>2 THEN @i:=@i-2 -- store mrk in @mrk and

-- while @i>2 return points: 3 or 1 ...

ELSE @i:=null -- no points for the rest

END

END

ELSE NULLIF(@i:=5,(@s:=sec)=(@m:=mrk)) -- store sec in @s and mrk in @m

-- and return points: 5

END pts

FROM tbl ORDER BY sec,mrk desc

Explanation of NULLIF(@i:=5,(@s:=sec)=(@m:=mrk)):

The expressions @s:=sec and @m:=mrk are both evaluated and then their values are compared by =. The result can either be 0 (false) or 1 (true) but it will definitely be unequal to 5, the other argument of the NULLIF function, therefore in the end only the first argument (5) will be returned. I chose the construct to make the two variable assignments happen without returning anything.

OK, maybe not the most straightforward solution ;-), but I did pay attention to define each variable only once for each record that is being processed, since "the order of evaluation for expressions involving user variables is undefined" mysql manual. The select indeed gives me the desired

result:

| ID | SEC | GRP | MRK | PTS |

|----|-----|-----|-----|--------|

| 6 | cl1 | ga | 55 | 5 |

| 2 | cl1 | gb | 22 | 3 |

| 3 | cl1 | gd | 22 | 3 |

| 4 | cl1 | ge | 18 | 1 |

| 9 | cl1 | gc | 12 | (null) |

| 7 | cl2 | gb | 66 | 5 |

| 11 | cl2 | ge | 66 | 5 |

| 1 | cl2 | ge | 32 | 3 |

| 5 | cl2 | ga | 26 | 1 |

| 8 | cl2 | gc | 15 | (null) |

| 10 | cl2 | gf | 5 | (null) |

Now, my question is:

How do I write an UPDATE statement along the same lines that will store the above calculated results in column pts?

My attempts so far have all failed:

UPDATE tbl SET pts=

CASE WHEN @s=sec THEN

CASE WHEN @m=mrk THEN @i ELSE

CASE WHEN IF(@m:=mrk,@i,@i)>2 THEN @i:=@i-2

ELSE @i:=null

END

END

ELSE NULLIF(@i:=5,(@s:=sec)=(@m:=mrk))

END

ORDER BY sec,mrk desc

result:

| ID | SEC | GRP | MRK | PTS |

|----|-----|-----|-----|-----|

| 6 | cl1 | ga | 55 | 5 |

| 2 | cl1 | gb | 22 | 5 |

| 3 | cl1 | gd | 22 | 5 |

| 4 | cl1 | ge | 18 | 5 |

| 9 | cl1 | gc | 12 | 5 |

| 7 | cl2 | gb | 66 | 5 |

| 11 | cl2 | ge | 66 | 5 |

| 1 | cl2 | ge | 32 | 5 |

| 5 | cl2 | ga | 26 | 5 |

| 8 | cl2 | gc | 15 | 5 |

| 10 | cl2 | gf | 5 | 5 |

Why does the update statement only get a single value (5) for pts?!?

You can find all the data and SQL statements in my SQLfiddle.

解决方案

I have tried to debug this case.

I've added 6 new columns to the tbl table: b_s, b_m, b_i and a_s, a_m, a_i

b_* - means "before", a_* - means "after",

and I've modified the query to:

UPDATE tbl SET

b_s = @s,

b_m = @m,

b_i = @i,

pts=

CASE WHEN @s=sec THEN

CASE WHEN @m=mrk THEN @i ELSE

CASE WHEN IF(@m:=mrk,@i,@i)>2 THEN @i:=@i-2

ELSE @i:=null

END

END

ELSE NULLIF(@i:=5,(@s:=sec)=(@m:=mrk))

END,

a_s = @s,

a_m = @m,

a_i = @i

ORDER BY sec,mrk desc

My intent was to log values of variables before and after of the expression evaluation.

It's strange - I don't know why, but it seems that when you assign values to all variables before the execution of the update then the update works as expected.

Compare these two demos:

1 - wrong: http://sqlfiddle.com/#!2/2db3e4/1

2 - fine: http://sqlfiddle.com/#!2/37ff5/1

The only difference is this code fragment before the update:

set @i='alamakota';

set @m='alamakota';

set @s='alamakota';

Some kind on "magic string" :)