在另一个字段更改后重置的自动增量字段 [英] Auto-increment field that resets after a change in another field

问题描述

您能否提供一个非常简单的 SQL 示例，说明如何创建一个计数"或订单"字段，该字段会自动递增，但在不同字段的每次更改后重新启动?在下表中，每次膳食"字段发生更改时，订单"字段都会从1"重新开始.谢谢.

Can you provide a very simple SQL example of how to create a "count" or "order" field that would auto-increment, but restart after every change in a different field? In the table below, the "Order" field would restart at "1" every time there was a change in the "Meal" field. Thanks.

用餐    时间   订单
午餐   10:30    1
午餐   11:00    2
午餐   11:15    3
晚餐   4:30    1
晚餐   4:45    2
晚餐   5:00    3
晚餐   5:30    4

Meal      Time    Order
Lunch    10:30     1
Lunch    11:00     2
Lunch    11:15     3
Dinner    4:30      1
Dinner    4:45      2
Dinner    5:00      3
Dinner    5:30      4

推荐答案

与其将 Order 存储在表中，不如考虑将其添加到视图中.您可以在需要时从视图而不是表格中进行选择.

Instead of storing Order in the table, consider adding it to a view. You can select from the view instead of the table when you need it.

视图可以使用row_number()来计算顺序，比如:

The view could use row_number() to calculate the order, like:

select  row_number() over (partition by Meal order by Time)
,       *
from    YourTable

SE 数据示例.

这篇关于在另一个字段更改后重置的自动增量字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！