左连接表A和表B

Question

我有两张桌子。

Table_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

Table_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

我不知道如何通过使用"normal“左连接来消除冗余行，如下所示

    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

我用过"Google“，但我没有找到任何这样的例子。

任何帮助都将不胜感激。(对不起，我的英语不好)

Answer 1

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

无论如何，这应该会让你(大部分)达到这个目标：

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;