如何编写嵌套的SQL语句来获取mssql中特定项的行号？ [英] How to code a nested SQL statement to get row number of a specific item in mssql?

问题描述

我想知道如何获取mssql中特定项目的行号。



假设我有一张这样的表：



ID类型品牌型号

1吉他Ibanez custom33

2吉他Ibanez custom45

3吉他Ibanez custom27

40吉他挡泥板strat45

41吉他挡泥板strat30

42吉他挡泥板strat15



例如我想获得Fender的物品。我希望这个表现在是这样的（假设我使用select语句来获取所有带有Fender品牌的商品）：



ID型号型号

40吉他挡泥板strat45

41吉他挡泥板strat30

42吉他挡泥板strat15



然后，例如，我想获得品牌Fender和模型strat30的行号。返回值应为2.如果我想要模型strat15，行号应为3，反之亦然。



我尝试过：



我可能需要嵌套一些sql语法，但我不知道如何将SELECT * FROM语句与SELECT ROW_NUMBER语句结合起来。希望你们能为此提供一些例子。



随意提出实现此输出的最佳方法。

I wanted to know how to get the row number of a specific item in mssql.

Lets say I have a table like this:

ID Type Brand Model
1 Guitar Ibanez custom33
2 Guitar Ibanez custom45
3 Guitar Ibanez custom27
40 Guitar Fender strat45
41 Guitar Fender strat30
42 Guitar Fender strat15

For example i want to get the items for Fender. I want the table to be like this now(Assuming I'm using a select statement to get all the items with the brand "Fender" in it):

ID Type Brand Model
40 Guitar Fender strat45
41 Guitar Fender strat30
42 Guitar Fender strat15

Then for example, I want to get the row number of Brand "Fender" and Model "strat30". The returned value should be 2. If i want the Model "strat15", the row number should be 3 and vice versa.

What I have tried:

I probably might be needing to nest some sql syntax but i don't know how to combine the SELECT * FROM statement to SELECT ROW_NUMBER statement. Hope you guys can provide example for this.

Feel free to suggest the best way to achieve this output.

推荐答案

尝试：

Try:

SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) AS RowNumber, * FROM MyTable WHERE Brand='Fender'

但你不应该这样做：为品牌和类型设置单独的表，以便您不会复制信息并使用外键而不是重复存储相同的文本。

But you shouldn't do it like that: have separate tables for Brand and Type so that you aren't duplicating information and use a foreign key to them instead of storing the same text repeatedly.

这篇关于如何编写嵌套的SQL语句来获取mssql中特定项的行号？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！