在两个表之间组合行数和分组方式

uinbv5nw  于 2021-08-01  发布在  Java
关注(0)|答案(1)|浏览(354)

我有两个表,我正试图将行号应用到其中,对于每个组,表2中的行号将从表1的结束处开始。
表1(按预期工作):

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

表2:期望输出:

当前代码:

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

2cmtqfgy

2cmtqfgy1#

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

  1. CREATE TABLE Table1 (Product varchar(1), Item int)
  2. INSERT INTO Table1 (Product, Item)
  3. VALUES ('A', 1), ('A', 2), ('B', 1), ('B', 2), ('B', 3)
  4. CREATE TABLE Table2 (Product varchar(1), Item int)
  5. INSERT INTO Table2 (Product, Item)
  6. VALUES ('A', 1), ('A', 2), ('B', 1)

声明:

  1. SELECT
  2. Product,
  3. Item,
  4. ROW_NUMBER() OVER (PARTITION BY Product ORDER BY Item) AS Rn
  5. FROM Table1
  6. SELECT
  7. t2.Product,
  8. t2.Item,
  9. ROW_NUMBER() OVER (PARTITION BY t2.Product ORDER BY t2.Item) + t1.[Count] AS Rn
  10. FROM Table2 t2
  11. LEFT JOIN (
  12. SELECT Product, COUNT(*) AS [Count]
  13. FROM Table1
  14. GROUP BY Product
  15. ) t1 ON t2.Product = t1.Product

结果:

  1. Product Item Rn
  2. A 1 1
  3. A 2 2
  4. B 1 1
  5. B 2 2
  6. B 3 3
  7. Product Item Rn
  8. A 1 3
  9. A 2 4
  10. B 1 4
展开查看全部

相关问题