sql-server - 左连接表 A 和表 B

Question

我有 2 个表。

表_A:

  Doc_No |  BUCKET  |DPD
  ---------------
  21     |M1        |11 
  22     |M1        |11
  23     |M1        |15
  24     |M1        |19
  25     |M1        |13
  26     |M1        |18
  27     |M1        |12

表_B:

  BUCKET    |USER1
  -----------------
  M1        |David
  M1        |Tom
  M1        |Kirby

我想得到如下表所示的结果 (DPD按a-z排序)

 Doc_No  |BUCKET    |DPD     |USER1
  --------------------------
 21      |M1        |11      |David
 22      |M1        |11      |Tom
 27      |M1        |12      |Kirby
 25      |M1        |13      |David
 23      |M1        |15      |Tom
 26      |M1        |18      |Kirby
 24      |M1        |19      |David

我不知道如何使用如下所示的“正常”左连接来消除冗余行

    with a1 as
    (
        select *
            ,row_number() OVER (ORDER BY dpd) AS RowNum
        from TABLE_A
    )

SELECT * 
FROM a1 left join TABLE_B b on a1.BUCKET = b.BUCKET

我用过“谷歌”，但我没有找到这方面的任何例子。

如有任何帮助，我们将不胜感激。 (对不起我的英语)

Answer 1

它没有完全得到您想要的逻辑，因为您的表 Table_B 除了 User1 之外没有任何可订购的东西，因此 Kirby 和汤姆颠倒了。如果顺序必须是 David、Tom、Kirby，则需要引入另一列来提供顺序。 (可能是 IDENTITY 或 FIFO ID 列)

无论如何，这应该让你(大部分)到达那里:

USE Sandbox;
GO

CREATE TABLE dbo.TableA (Bucket char(2), DPD tinyint);
CREATE TABLE dbo.TableB (Bucket char(2), User1 varchar(5));
INSERT INTO dbo.TableA (Bucket,
                        DPD)
VALUES ('M1',11), 
       ('M1',11),
       ('M1',15),
       ('M1',19),
       ('M1',13),
       ('M1',18),
       ('M1',12);

INSERT INTO dbo.TableB (Bucket,
                        User1)
VALUES ('M1','David'),
       ('M1','Tom'),
       ('M1','Kirby');
GO
WITH Sizes AS(
    SELECT Bucket, COUNT(*) AS Users
    FROM dbo.TableB
    GROUP BY Bucket),
FIFO AS (
    SELECT A.*,
           --ROW_NUMBER() OVER (PARTITION BY A.Bucket ORDER BY DPD) AS RN, --Not actually needed
           ((ROW_NUMBER() OVER (PARTITION BY A.Bucket ORDER BY DPD) -1) % S.Users)+1 AS UserRN
    FROM dbo.TableA A
         JOIN Sizes S ON A.Bucket = S.Bucket),
Users AS(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY B.Bucket ORDER BY B.User1) AS RN
    FROM dbo.TableB B)
SELECT F.Bucket, F.DPD, U.User1
FROM FIFO F
     JOIN Users U ON F.UserRN = U.RN;

GO

DROP TABLE dbo.TableA;
DROP TABLE dbo.TableB;