MySQL CASE WHEN 不能按預期使用使用者定義的變數？

我需要以下查詢的幫助。我正在從 DB 中提取郵政編碼 ( plz) 和使用者 ID ( uid)，並通過 zip (plz) 和 uid 對它們進行排序。我還使用使用者定義的變數@prev來檢查previous plz 是否不同於current plz. 在此 CASE 列rownum中，應將起始使用者定義變數減@row1。

這是我對此的查詢..

SELECT   
 @prev as previous, @prev := u.plz as current, 
 CASE 
   WHEN @prev is null THEN @row := @row -1 
   WHEN @prev <> u.plz THEN @row := @row -1
   ELSE @row END AS rownum, 
 uid

FROM             
 (SELECT @prev := NULL, @row := 1001) as r, user as u

LEFT JOIN 
 geodb_locations ON u.plz = geodb_locations.id 
WHERE 
 u.plz IN(29386,30013,29271,30406) AND
ORDER BY 
 FIELD(u.plz,29386,30013,29271,30406), 
 uid DESC

輸出

+----------+---------+--------+-------+
| previous | current | rownum | uid   |
+----------+---------+--------+-------+
| NULL     |   29386 |   1001 | 92014 | <-- @prev <> u.plz why NOT decreasing `rownum`
| 29386    |   29386 |   1001 | 87223 |
| 29386    |   29386 |   1001 | 83160 |
| 29386    |   29386 |   1001 | 81322 |
| 29386    |   29386 |   1001 | 81293 |
| 29386    |   29386 |   1001 |  3152 |
| 29386    |   29386 |   1001 |  1939 |
| 29386    |   30013 |   1001 | 65193 | <-- @prev <> u.plz why NOT decreasing `rownum`
| 30013    |   29384 |   1001 | 12883 |
| 29384    |   29385 |   1001 | 32667 |
| 29385    |   29232 |   1001 | 86641 |
| 29232    |   29481 |   1001 | 94521 |
| 29481    |   29481 |   1001 | 33950 |
| 29481    |   29481 |   1001 | 33926 |
| 29481    |   29667 |   1001 | 16027 |
| 29667    |   29667 |   1001 |   496 |
| 29667    |   29355 |   1001 | 96013 |
+----------+---------+--------+-------+

預期產出

+----------+---------+--------+-------+
| previous | current | rownum | uid   |
+----------+---------+--------+-------+
| NULL     |   29386 |   1000 | 92014 | <-- decrease @row `rownum` by one
| 29386    |   29386 |   1000 | 87223 |
| 29386    |   29386 |   1000 | 83160 |
| 29386    |   29386 |   1000 | 81322 |
| 29386    |   29386 |   1000 | 81293 |
| 29386    |   29386 |   1000 |  3152 |
| 29386    |   29386 |   1000 |  1939 |
| 29386    |   30013 |    999 | 65193 | <-- decrease @row `rownum` by one 
| 30013    |   29384 |    998 | 12883 |
| 29384    |   29385 |    997 | 32667 |
| 29385    |   29232 |    996 | 86641 |
| 29232    |   29481 |    995 | 94521 |
| 29481    |   29481 |    995 | 33950 |
| 29481    |   29481 |    995 | 33926 |
| 29481    |   29667 |    994 | 16027 |
| 29667    |   29667 |    994 |   496 |
| 29667    |   29355 |    993 | 96013 |
+----------+---------+--------+-------+

為什麼 CASE WHEN 不能按預期工作？我的思維錯誤在哪裡？提前致謝 ..

輸出表達式求值的順序沒有記錄或指定。通常，評估順序與文本匹配。

看。您分配@prev := u.plz然後嘗試比較這些值 - 但由於分配，它們已經相等。

SELECT   
 @prev as previous,                          -- @prev stores "old" value
 @prev := u.plz as current,                  -- @prev is re-assigned to u.plz
 CASE 
   WHEN @prev is null THEN @row := @row -1   -- @prev is equal to u.plz, FALSE
   WHEN @prev <> u.plz THEN @row := @row -1  -- @prev is equal to u.plz, FALSE
   ELSE @row END AS rownum,                  -- this alternative is evaluated always
 uid

將此分配放在 CASE 表達式之後 - 在這種情況下，CASE 將使用舊的（取自前一行計算）值。

SELECT   
 @prev as previous,                          -- @prev stores "old" value
 CASE 
   WHEN @prev is null THEN @row := @row -1   -- "old" value is used for conditions
   WHEN @prev <> u.plz THEN @row := @row -1 
   ELSE @row END AS rownum,                 
 @prev := u.plz as current,                  -- and now @prev is re-assigned to u.plz
 uid

引用自：https://dba.stackexchange.com/questions/273241