英文:
SQL Auto assign a number to duplicating values that resets after a change in the values WITHOUT using ROW_NUMBER or Partition BY?
问题
我想创建一个“计数”或“顺序”字段，它会自动递增，但在不同字段发生变化时重新开始。在下表中，“Order”字段将在“Meal”字段发生变化时重新从“1”开始。谢谢。
我尝试了以下代码：
SELECT
t1.meal,
COUNT(t2.meal) + 1 AS RowNumb
FROM CTE t1
JOIN CTE t2 ON t2.time < t1.time OR (t2.time = t1.time AND t2.meal <> t1.meal)
GROUP BY
t1.time,
t1.meal
ORDER BY
t1.time;
但我得到了以下结果：
| Meal | Time | RowNumb |
|---|---|---|
| Lunch | 10:30 | 1 |
| Lunch | 11:00 | 2 |
| Lunch | 11:30 | 3 |
| Dinner | 4:30 | 4 |
| Dinner | 5:00 | 5 |
| Dinner | 5:30 | 6 |
| Dinner | 6:00 | 7 |
英文:
I would like 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.
| Meal | Time | RowNumb |
|---|---|---|
| Lunch | 10:30 | 1 |
| Lunch | 11:00 | 2 |
| Lunch | 11:30 | 3 |
| Dinner | 4:30 | 1 |
| Dinner | 5:00 | 2 |
| Dinner | 5:30 | 3 |
| Dinner | 6:00 | 4 |
I tried this:
SELECT
t1.meal,
COUNT(t2.meal) + 1 AS RowNumb
FROM CTE t1
JOIN CTE t2 ON t2.time < t1.time OR (t2.time = t1.time AND t2.meal <> t1.meal)
GROUP BY
t1.time,
t1.meal
ORDER BY
t1.time;
But I am getting this:
| Meal | Time | RowNumb |
|---|---|---|
| Lunch | 10:30 | 1 |
| Lunch | 11:00 | 2 |
| Lunch | 11:30 | 3 |
| Dinner | 4:30 | 4 |
| Dinner | 5:00 | 5 |
| Dinner | 5:30 | 6 |
| Dinner | 6:00 | 7 |
答案1
得分: 2
使用子查询来计算相同餐点和截止时间之前的行数：
SELECT
t.meal,
t.time,
(
SELECT COUNT(*)
FROM mytable t2
WHERE t2.meal = t.meal
AND t2.time <= t.time
) AS rownumb
FROM mytable t
ORDER BY t.meal, t.time;
英文:
Use a subquery to count the rows for the same meal and a time till then:
SELECT
t.meal,
t.time,
(
SELECT COUNT(*)
FROM mytable t2
WHERE t2.meal = t.meal
AND t2.time <= t.time
) AS rownumb
FROM mytable t
ORDER BY t.meal, t.time;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论