Hi我正在尝试在mysql视图中实现循环中的行自动编号。我确实从用于在结果集中自动编号行的纯SQL技术开始，并使其发挥了作用。然后我试着调整它，几乎达到了目的。。。。现在我在学习
视图列出了狗（CaneID）和它们的优势（AvoID）。我想实现的是，每当主犬（CaneID）发生变化时，都会重新启动编号，这样我就可以从1开始让每个优势犬都属于一个犬号。
到目前为止，它只适用于第一只狗，下面的狗仍然按顺序编号，但不是从1开始。
这是我的视图查询：
SELECT
`avi1`.`CaneID`,
`avi1`.`Cane`,
`avi1`.`PedPos`,
`avi1`.`AvoID`,
`avi1`.`Avo`,
`avi1`.`Ripetuto`,
(
SELECT
count(0)
FROM
`v_avi_ripetuti` `Avi2`
WHERE
(
(`avi2`.`Ripetuto` > 1)
AND (`avi2`.`CaneID` <= `avi1`.`CaneID`)
AND (`avi2`.`AvoID` <= `avi1`.`AvoID`)
)
ORDER BY
`avi2`.`CaneID`,
`avi2`.`AvoID`
) AS `RowNumber`
FROM
`v_avi_ripetuti` `Avi1`
WHERE
(`avi1`.`Ripetuto` > 1)
ORDER BY
`avi1`.`CaneID`,
`avi1`.`AvoID`
我现在得到的是：
CaneID Cane AvoID Avo RowNumber
---------------------------------------------------------------------
2 Antigua 472 Anika v. Stammhaus Eike 1
2 Antigua 527 Britta v.d. Römerlinde 2
2 Antigua 642 Ares v. Nettenberg 3
2 Antigua 657 Carmen v. Westfalenzwinger 4
2 Antigua 658 Leu v. Stammhaus Eike 5
2 Antigua 684 Chipsy v. Theresienhof 6
2 Antigua 1662 Astrit v.d. Burrlinde 7
-----------------------> New Main dog<-------------------------------
3 Ambra Gaya 472 Anika v. Stammhaus Eike 2
3 Ambra Gaya 657 Carmen v. Westfalenzwinger 6
3 Ambra Gaya 658 Leu v. Stammhaus Eike 8
3 Ambra Gaya 684 Chipsy v. Theresienhof 10
3 Ambra Gaya 718 Leo v. guten Löwen 11
3 Ambra Gaya 1022 Zara v. Ratibor u. Corvey 12
3 Ambra Gaya 1024 Ruth v. Ratibor u. Corvey 13
3 Ambra Gaya 1050 Dunja v. Hofoldinger Forst 14
3 Ambra Gaya 1200 Icksle v. Ratibor u. Corvey 15
3 Ambra Gaya 1227 Troldegaards Tanja 16
3 Ambra Gaya 1228 Anemone v. Ratibor u. Corvey 17
-----------------------> New Main dog<-------------------------------
3371 Always Habit 600 Absinthe v. Kallenberg 4
3371 Always Habit 750 Mathoaka's Relaxing Boy 13
3371 Always Habit 2560 Leijonamielen Luvaton Lyyli 21
我是mysql的新手（一周前安装了它），并且非常努力地放弃访问。。。。请耐心等待。
我猜您希望按结果中的顺序排列，先按CaneId，然后按AvoId。如果是，正确的子查询是：
(SELECT count(*)
FROM v_avi_ripetuti` `Avi2`
WHERE `avi2`.`Ripetuto` > 1 AND
(`avi2`.`CaneID` < `avi1`.`CaneID` or
`avi2`.`CaneID` = `avi1`.`CaneID` and avi2`.`AvoID` <= `avi1`.`AvoID`
)
) AS `RowNumber`
除了修复where子句外，我还删除了order by，因为它在子查询中是不相关的。我还将count(0)更改为count(*)。这些是等效的，但count(0)在我看来很尴尬。
我做到了！！！！！！！我使用了函数SUM（）而不是COUNT（）。这是我的mysql视图代码，以防其他人可以使用它：
SELECT
`avi1`.`CaneID`,
`avi1`.`Cane`,
`avi1`.`PedPos`,
`avi1`.`AvoID`,
`avi1`.`Avo`,
`avi1`.`Ripetuto`,
(
SELECT
sum(
IF (
(
(
`avi2`.`CaneID` = `avi1`.`CaneID`
)
AND (`avi2`.`AvoID` <= `avi1`.`AvoID`)
),
1,
0
)
)
FROM
`v_avi_ripetuti` `Avi2`
WHERE
(`avi2`.`Ripetuto` > 1)
) AS `NumRiga`
FROM
`v_avi_ripetuti` `Avi1`
WHERE
(`avi1`.`Ripetuto` > 1)
这给了我想要的结果：
CaneID Cane AvoID Avo RowNumber
---------------------------------------------------------------------
2 Antigua 472 Anika v. Stammhaus Eike 1
2 Antigua 527 Britta v.d. Römerlinde 2
2 Antigua 642 Ares v. Nettenberg 3
2 Antigua 657 Carmen v. Westfalenzwinger 4
2 Antigua 658 Leu v. Stammhaus Eike 5
2 Antigua 684 Chipsy v. Theresienhof 6
2 Antigua 1662 Astrit v.d. Burrlinde 7
-----------------------> New Main dog<-------------------------------
3 Ambra Gaya 472 Anika v. Stammhaus Eike 1
3 Ambra Gaya 657 Carmen v. Westfalenzwinger 2
3 Ambra Gaya 658 Leu v. Stammhaus Eike 3
3 Ambra Gaya 684 Chipsy v. Theresienhof 4
3 Ambra Gaya 718 Leo v. guten Löwen 5
3 Ambra Gaya 1022 Zara v. Ratibor u. Corvey 6
3 Ambra Gaya 1024 Ruth v. Ratibor u. Corvey 7
3 Ambra Gaya 1050 Dunja v. Hofoldinger Forst 8
3 Ambra Gaya 1200 Icksle v. Ratibor u. Corvey 9
3 Ambra Gaya 1227 Troldegaards Tanja 10
3 Ambra Gaya 1228 Anemone v. Ratibor u. Corvey 11
-----------------------> New Main dog<-------------------------------
3371 Always Habit 600 Absinthe v. Kallenberg 1
3371 Always Habit 750 Mathoaka's Relaxing Boy 2
3371 Always Habit 2560 Leijonamielen Luvaton Lyyli 3