主页 / user-213150

Aldee's questions

Martin Hope
Aldee
Asked: 2020-08-05 22:58:26 +0800 CST
  • -1

我需要以下查询的帮助。我正在从 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 不能按预期工作？我的思维错误在哪里？提前致谢 ..

mysql case