MySQL: Automatyczna inkrementacja tymczasowej kolumny w instrukcji select
Jak utworzyć i automatycznie zwiększyć tymczasową kolumnę w instrukcji select za pomocą MySQL?
Oto co mam do tej pory:
SET @cnt = 0;
SELECT
(@cnt =@cnt + 1) AS rowNumber,
rowID
FROM myTable
WHERE CategoryID = 1
Które zwraca:
+++++++++++++++++++++
+ rowNumber | rowID +
+++++++++++++++++++++
+ (NULL) | 1 +
+ (NULL) | 25 +
+ (NULL) | 33 +
+ (NULL) | 150 +
+ (NULL) | 219 +
+++++++++++++++++++++
Ale potrzebuję:
+++++++++++++++++++++
+ rowNumber | rowID +
+++++++++++++++++++++
+ 1 | 1 +
+ 2 | 25 +
+ 3 | 33 +
+ 4 | 150 +
+ ... | ... +
+++++++++++++++++++++
29
3 answers
To da ci konsekutywne Numer wiersza z 3
.
SELECT
(@cnt := @cnt + 1) AS rowNumber,
t.rowID
FROM myTable AS t
CROSS JOIN (SELECT @cnt := 0) AS dummy
WHERE t.CategoryID = 1
ORDER BY t.rowID ;
Wynik
| ROWNUMBER | ROWID | --------------------- | 1 | 1 | | 2 | 25 | | 3 | 33 | | 4 | 150 |
57
Author: Kermit,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-04-10 16:07:01
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-04-10 16:07:01
Spróbuj tego:
SET @rownr=0;
SELECT @rownr:=@rownr+1 AS rowNumber, rowID
FROM myTable
WHERE CategoryID = 1
17
Author: Niels,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-04-10 15:48:15
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-04-10 15:48:15
Ale co jeśli masz group by
w instrukcji select? odliczanie będzie wyłączone.
W takich przypadkach jedynym rozwiązaniem jakie znalazłem jest zagnieżdżenie select:
SELECT (@cnt := @cnt + 1) AS rowNumber, t.*
from
(select
t.rowID
FROM myTable
WHERE CategoryID = 1
ORDER BY rowID) t
CROSS JOIN (SELECT @cnt := 0) AS dummy
14
Author: Arrabi,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-03-16 05:37:57
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-03-16 05:37:57