我在 MySQL 上有這樣的程式
SELECT t.name AS cat_name , f.id AS id_food
FROM food AS f, categories AS t
WHERE f.quantity > 0
AND t.id = f.id_cat
ORDER BY t.name
當我執行它時顯示一個表格
| cat_name | id_food |
| -------- | -------------|
| Seafood | 1 |
| Seafood | 3 |
| Seafood | 5 |
| Beef | 2 |
| Beef | 16 |
| Special | 7 |
想知道有什么東西可以讓這個表在它前面生成一個新的auto_increment id 欄位嗎？
這就是我希望它在執行時的樣子
| new_id | cat_name | id_food |
|---- |------ | ----- |
| 1 | Seafood | 1 |
| 2 | Seafood | 3 |
| 3 | Seafood | 5 |
| 4 | Beef | 2 |
| 5 | Beef | 16 |
| 6 | Special | 7 |
uj5u.com熱心網友回復：
如果你的 MySQL 版本支持ROW_NUMBER 視窗功能，你可以試試這個。
SELECT ROW_NUMBER() OVER(ORDER BY t.name)new_id
t.name AS cat_name ,
f.id AS id_food
FROM food AS f
INNER JOIN categories AS t
ON t.id = f.id_cat
WHERE f.quantity > 0
ORDER BY t.name
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/399378.html