如何获取查询中列出现的索引?

Question

我正在尝试获取数据集中列出现的索引/行号，以产生类似于以下内容的结果：

| Make        | Model    | Option    | Model Index |
├-------------+----------+-----------+-------------┤
| Lamborghini | Diablo   | SE30 Jota | 1           |
| Lamborghini | Diablo   | SE30      | 1           |
| Lamborghini | Cala     |           | 2           |
| Pontiac     | Trans AM | GTA       | 1           |
| Pontiac     | Trans AM | Firefox   | 1           |
| Pontiac     | GTO      | Judge     | 2           |
| Pontiac     | Fiero    | GT        | 3           |

请注意，对于具有不同选项的相同品牌和型号的汽车，模型索引应重复，但也应在更改时重置。我已经设法找到了 SQL 来重置进行更改，但是一旦我包含选项列，我就会得到错误的结果。

SELECT
Makes.Name,
Models.Name,
Options.Name,
ROW_NUMBER() OVER (PARTITION BY Makes.Id ORDER BY Models.Name) [Model Index]
FROM 
Makes
INNER JOIN  
Models ON Models.MakeId = Makes.Id
INNER JOIN 
Options ON Options.ModelId = Models.Id

此 SQL 的结果(不需要)：

| Make        | Model    | Option    | Model Index |
├-------------+----------+-----------+-------------┤
| Lamborghini | Diablo   | SE30 Jota | 1           |
| Lamborghini | Diablo   | SE30      | 2           |
| Lamborghini | Cala     |           | 3           |
| Pontiac     | Trans AM | GTA       | 1           |
| Pontiac     | Trans AM | Firefox   | 2           |
| Pontiac     | GTO      | Judge     | 3           |
| Pontiac     | Fiero    | GT        | 4           |

也许我在这里的错误是我在查询中使用了ROW_NUMBER，并且我认为行号不能在给定分区内重复。 我想我需要SomethingElse(PerhapsSomeReferenceToModel) OVER (PARTITION BY Makes.Id ORDER BY Models.Name)但不知道SomethingElse(PerhapsSomeReferenceToModel)实际上应该是什么！

Answer 1

让我重新表述一下您的要求：

• 进行更改时模型索引重置
• 同一模型的模型索引重复

如果模型索引不能包含间隙，则需要RANK()而不是ROW_NUMBER()或DENSE_RANK()：

SELECT
*,
RANK() OVER (PARTITION BY Make ORDER BY Model) AS [Model Index],
DENSE_RANK() OVER (PARTITION BY Make ORDER BY Model) AS [Model Index DENSE]
FROM @t
ORDER BY Make, Model

德铁小提琴

(上面的示例按名称划分和排序。在实践中，您实际上会做类似PARTITION BY MakeID ORDER BY ModelID)。

Answer 2

对于 1-2-1-2-3 的结果，您的查询应该是正确的。表别名还简化了查询：

SELECT ma.Name, mo.Name, o.Name,
ROW_NUMBER() OVER (PARTITION BY mo.MakeId ORDER BY o.Name) as [Model Index]
FROM Makes ma INNER JOIN
Models mo
ON mo.MakeId = ma.Id INNER JOIN
Options o
ON o.ModelId = mo.Id;

我怀疑你想要 1-2-1-2-1。 如果是这样，您需要更改PARTITION BY：

SELECT ma.Name, mo.Name, o.Name,
ROW_NUMBER() OVER (PARTITION BY mo.MakeId, mo.ModelId ORDER BY o.Name) as [Model Index]
FROM Makes ma INNER JOIN
Models mo
ON mo.MakeId = ma.Id INNER JOIN
Options o
ON o.ModelId = mo.Id