如何编写嵌套 sql 语句以获取 mssql 中特定项的行号

Question

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

假设我有一张这样的表：

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 

例如，我想获得挡泥板的物品。我希望桌子现在像这样(假设我使用 select 语句来获取所有带有"挡泥板"品牌的商品(：

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

然后例如，我想获取品牌"挡泥板"的行号，并且模型"strat30"。返回值应为 2。如果我想要模型"strat15"，行号应为 3，反之亦然。

我可能需要嵌套一些sql语法，但我不知道如何将 SELECT * FROM 语句组合到 SELECT ROW_NUMBER语句。希望你们能为此提供榜样。

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

Answer 1

你可以简单地使用 ROW_NUMBER((。与 WHERE 子句结合使用，编号将仅应用于按 WHERE 筛选的结果。

SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS Rn 
FROM YourTable
WHERE Brande LIKE 'Fender'

如果您确实只需要特定模型上的 RN，请将其放在子查询中并从中进行选择。

SELECT Rn FROM 
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS Rn 
    FROM YourTable
    WHERE Brande LIKE 'Fender'
) x
WHERE x.Model LIKE 'Stat15'

Answer 2

您可以使用

子查询来实现它并ROW_NUMBER .在外部WHERE子句中添加您想要的型号和品牌

SELECT * 
FROM (
       SELECT *,ROW_NUMBER() OVER(PARTITION BY Brand ORDER BY ID) RN
       FROM [YourTable]
       WHERE Type='Guitar' ) X
WHERE X.Brand='Fender' AND X.Model='strat30'

Answer 3

是的，你会在这里使用ROW_NUMBER。您需要订单标准，例如 ID：

select rn
from
(
  select id, type, brand, model, row_number() over (order by id) as rn
  from mytable
  where brand = 'Fender'
) thebrand
where model = 'strat30';

或：

select rn
from
(
  select id, type, brand, model, row_number() over (partition by brand order by id) as rn
  from mytable
) thebrand
where brand = 'Fender'
  and model = 'strat30';

Answer 4

BEGIN TRAN
--Here you get the row number of Brand "Fender" and Model "strat30". The returned value should be 2. 
--AND If you want the Model "strat15", the row number should be 3
CREATE TABLE #TEMP (ID INT,type NVARCHAR(50),Brand NVARCHAR(50),Model NVARCHAR(50))
INSERT INTO #TEMP
SELECT 1,'Guitar','Ibanez','custom33' UNION ALL
SELECT 2,'Guitar','Ibanez','custom45' UNION ALL
SELECT 3,'Guitar','Ibanez','custom27'UNION ALL
SELECT 40,'Guitar','Fender','strat45'UNION ALL
SELECT 41,'Guitar','Fender','strat30'UNION ALL
SELECT 42,'Guitar','Fender','strat15'
Select ROW_NUMBER ()OVER (PARTITION BY Brand ORDER BY ID)Rownum, * INTO #T
FROM #TEMP 
SELECT * FROM #T 
WHERE Brand='Fender' AND Model='strat30'
DROP TABLE #TEMP
DROP TABLE #T
ROLLBACK TRAN

Answer 5

将缩减的表存储到名为 #Table 的临时表中(在本例中(，然后使用以下 SQL：

SELECT 
    Model , 
    RowNumber
FROM 
(
    SELECT 
        * ,
        ROW_NUMBER() OVER(PARTITION BY Brand ORDER BY ID) RowNumber
    FROM 
        [#ReducedTable]
)