sql - 在 2 个表之间结合 row_num 和 group by

Question

我有 2 个表，我正在尝试将 row_number 应用于每个组，表 2 中的 row_number 会在表 1 停止的地方出现。

表 1（按预期工作）：

SELECT DISTINCT Product, Item, ROW_NUMBER() OVER (PARTITION BY Product ORDER BY Product, Item)
FROM Table1 (NOLOCK)

表 2：所需输出：

当前代码：

SELECT DISTINCT Product, Item, ROW_NUMBER() OVER (PARTITION BY Product ORDER BY Product, Item)+(SELECT COUNT(*) FROM Table1)
FROM Table2 (NOLOCK)

标签： sqlsql-servertsql

Answer 1

一种可能的方法是以下声明：

表：

CREATE TABLE Table1 (Product varchar(1), Item int)
INSERT INTO Table1 (Product, Item)
VALUES ('A', 1), ('A', 2), ('B', 1), ('B', 2), ('B', 3)

CREATE TABLE Table2 (Product varchar(1), Item int)
INSERT INTO Table2 (Product, Item)
VALUES ('A', 1), ('A', 2), ('B', 1)

陈述：

SELECT 
   Product, 
   Item, 
   ROW_NUMBER() OVER (PARTITION BY Product ORDER BY Item) AS Rn
FROM Table1

SELECT 
  t2.Product, 
  t2.Item, 
  ROW_NUMBER() OVER (PARTITION BY t2.Product ORDER BY t2.Item) + t1.[Count] AS Rn
FROM Table2 t2
LEFT JOIN (
   SELECT Product, COUNT(*) AS [Count]
   FROM Table1 
   GROUP BY Product
) t1 ON t2.Product = t1.Product

结果：

Product Item  Rn
A       1     1
A       2     2
B       1     1
B       2     2
B       3     3

Product Item Rn
A       1    3
A       2    4
B       1    4