T-SQL - La jointure de plusieurs tables produit des lignes en double

Question

J'ai du mal à obtenir le résultat suivant à partir de l'échantillon ci-dessous Violon .

Résultat :

Tableaux sources :

Violon

L'idée est de créer une seule rangée pour chaque RecordID en trouvant le compte max des ID de budget et de prévision pour créer les autres colonnes (en laissant les cellules des colonnes vides si aucune valeur n'existe pour cet ID de budget ou de prévision).

J'ai essayé avec PIVOT mais je n'ai pas réussi à obtenir un résultat décent.

UPDATE : Veuillez voir l'image ci-dessous où j'ai essayé d'expliquer ce qui est attendu. sortie :

En paroles : pour chaque BudgetID qui appartient à un RecordID créer séparé colonnes pour BDate , Résultat (Pourcentage * BAmount of Records table) et Statut .

Dans mon exemple, RecordID 55 tiene deux entrées dans le tableau budgétaire - donc 2x3 colonnes sont nécessaires pour montrer chaque date , résultat y statut dans sa propre colonne dans un une seule rangée pour ce RecordID.

Depuis RecordID 77 a le le plus (trois) entrées dans le tableau du budget, il est utilisé pour créer des colonnes 3x3 sur toutes les lignes.

Il en va de même pour Forecast.

J'espère que vous pourrez m'aider.

Merci.

Answer 1

Bonne journée,

Note ! Mon objectif est d'aider le lecteur à apprendre et non de lui fournir une question finale comme solution à ses devoirs. C'est pourquoi je présenterai la solution en deux étapes, et j'ajouterai plusieurs commandes "PRINT" dans la solution dynamique, afin que le lecteur ait la possibilité de vérifier les étapes intermédiaires du travail.

Le DDL+DML fourni par le PO :

DROP TABLE IF EXISTS Budget;
CREATE TABLE Budget
    (BudgetID int, RecordID int, BDate date,Percentage int, [Status] varchar(50));
INSERT INTO Budget
    (BudgetID, RecordID, BDate,Percentage,Status)
VALUES
    (1, 55, '2017-01-01', 60, 'ordered'),
    (2, 55, '2017-03-24', 40, 'ordered'),
    (3, 66, '2018-08-15', 100, 'invoiced'),
    (4, 77, '2018-12-02', 25, 'paid'),
    (5, 77, '2018-09-10', 35, 'ordered'),
    (6, 77, '2019-07-13', 40, 'ordered')
GO

DROP TABLE IF EXISTS Forecast;
CREATE TABLE Forecast
    (ForecastID int, RecordID int, FDate date, Percentage int);
INSERT INTO Forecast
    (ForecastID, RecordID, FDate,Percentage)
VALUES
    (1, 55, '2020-12-01', 100),
    (2, 77, '2023-05-17', 25),
    (3, 77, '2024-11-28', 75)
GO

DROP TABLE IF EXISTS Records;
CREATE TABLE Records
    (RecordID int, BAmount int, FAmount int, Name varchar(40), Description varchar(40) )
;
INSERT INTO Records
   (RecordID, BAmount,FAmount,Name, Description)
VALUES
    (55, 15000, 33000, 'Prod1', 'Desc1' ),
     (66, 22000, 17500, 'Prod2', 'Desc2' ),
    (77, 40000, 44000, 'Prod3', 'Desc3' )
GO

select * from Budget
select * from Forecast
select * from Records

Montrons d'abord une solution statique simple

Ceci est basé sur le fait que nous avons un maximum de trois rangées pour chaque RecordID dans la table Budget et un maximum de deux rangées pour chaque RecordID dans la table Forecast. Cela aidera à comprendre la solution dynamique que je vais vous présenter ensuite.

;With CteBudget as (
    select 
        b.BDate, b.BudgetID, b.Percentage, b.RecordID, b.Status
        ,RN = ROW_NUMBER() OVER (partition by b.RecordID order by b.BudgetID)
    from Budget b
),
CteForecast as (
    select 
        f.FDate, f.ForecastID, f.Percentage, f.RecordID
        ,RN = ROW_NUMBER() OVER (partition by f.RecordID order by f.ForecastID)
    from Forecast f
)
select 
    r.RecordID, r.Name, r.Description, 

    b1.BDate BDate1, (b1.Percentage * r.BAmount)/100 BResult1, b1.Status BStatus1, 
    b2.BDate BDate2, (b2.Percentage * r.BAmount)/100 BResult2, b2.Status BStatus2,
    b3.BDate BDate3, (b3.Percentage * r.BAmount)/100 BResult3, b3.Status BStatus3, 

    f1.FDate FDate1, (f1.Percentage * r.BAmount)/100 FResult1,
    f2.FDate FDate2, (f2.Percentage * r.BAmount)/100 FResult2

from Records r
left join CteBudget b1 on r.RecordID = b1.RecordID and b1.RN = 1
left join CteBudget b2 on r.RecordID = b2.RecordID and b2.RN = 2
left join CteBudget b3 on r.RecordID = b3.RecordID and b3.RN = 3
left join CteForecast f1 on r.RecordID = f1.RecordID and f1.RN = 1
left join CteForecast f2 on r.RecordID = f2.RecordID and f2.RN = 2
--where r.RecordID = 77
GO

Note ! Pour une solution statique et sans index (comme je l'ajouterai à la fin), la solution ci-dessus est TRÈS mauvaise en ce qui concerne les performances, mais une fois que nous ajoutons le bon index et comme base pour une solution dynamique, cette option devrait bien convenir.

Nous pouvons maintenant présenter la solution dynamique.

-- Get number of columns
Declare @NumBudget tinyint
Declare @NumForecast tinyint
SELECT @NumBudget = MAX(C) FROM (
    select COUNT(RecordID) C
    from Budget
    GROUP BY RecordID
) t
SELECT @NumForecast = MAX(C) FROM (
    select COUNT(RecordID) C
    from Forecast
    GROUP BY RecordID
) t
---------------------------------------------
DECLARE @SQLString1 nvarchar(MAX) = '';
DECLARE @SQLString2 nvarchar(MAX) = '';
DECLARE @loop int = 1;
WHILE @loop <= @NumBudget BEGIN 
    SET @SQLString1 = @SQLString1 + N'
    b' + CONVERT(VARCHAR(2),@loop) + '.BDate BDate' + CONVERT(VARCHAR(2),@loop) + ', (b' + CONVERT(VARCHAR(2),@loop) + '.Percentage * r.BAmount)/100 BResult' + CONVERT(VARCHAR(2),@loop) + ', b' + CONVERT(VARCHAR(2),@loop) + '.Status BStatus' + CONVERT(VARCHAR(2),@loop) + ', '

    SET @SQLString2 = @SQLString2 + N'
    left join CteBudget b' + CONVERT(VARCHAR(2),@loop) + ' on r.RecordID = b' + CONVERT(VARCHAR(2),@loop) + '.RecordID and b' + CONVERT(VARCHAR(2),@loop) + '.RN = 1'

    SET @loop = @loop + 1
END
SET @loop = 1

WHILE @loop <= @NumForecast BEGIN  
    SET @SQLString1 = @SQLString1 + N'
    f' + CONVERT(VARCHAR(2),@loop) + '.FDate FDate' + CONVERT(VARCHAR(2),@loop) + ', (f' + CONVERT(VARCHAR(2),@loop) + '.Percentage * r.BAmount)/100 FResult' + CONVERT(VARCHAR(2),@loop) + ','

    SET @SQLString2 = @SQLString2 + N'
    left join CteForecast f' + CONVERT(VARCHAR(2),@loop) + ' on r.RecordID = f' + CONVERT(VARCHAR(2),@loop) + '.RecordID and f' + CONVERT(VARCHAR(2),@loop) + '.RN = 1'

    SET @loop = @loop + 1
END
SET @SQLString1 = STUFF (@SQLString1, LEN(@SQLString1) , 1 , '')  
PRINT '/************************************************/'
PRINT @SQLString1
PRINT @SQLString2
PRINT '/************************************************/'

DECLARE @SQLString nvarchar(MAX);
SET @SQLString = N'
;With CteBudget as (
    select 
        b.BDate, b.BudgetID, b.Percentage, b.RecordID, b.Status
        ,RN = ROW_NUMBER() OVER (partition by b.RecordID order by b.BudgetID)
    from Budget b
),
CteForecast as (
    select 
        f.FDate, f.ForecastID, f.Percentage, f.RecordID
        ,RN = ROW_NUMBER() OVER (partition by f.RecordID order by f.ForecastID)
    from Forecast f
)
select 
    r.RecordID, r.Name, r.Description, 
'
+ @SQLString1
+ N'
from Records r'
+ @SQLString2

print @SQLString

EXECUTE sp_executesql @SQLString
GO

IMPORTANT ! Cette solution n'est pas nécessairement celle qui donne les meilleures performances, mais celle qui est probablement la plus simple à suivre et à comprendre. En production, une fois que nous aurons les vrais DDL+DML et que le serveur aura les statistiques, nous pourrons améliorer les performances et choisir la meilleure solution pour notre cas spécifique.

Indices

Note ! La solution ci-dessus peut conduire à un grand nombre de tris de données, et avoir les bons index est TRÈS important ici ! Il est important de tester plusieurs options différentes et de choisir la meilleure.

Pour le bien du forum (ou quel que soit le nom que vous donnez à stackoverflow qui, à mon avis, n'est pas une interface de forum de discussion mais une interface de questions-réponses), j'ajoute des requêtes pour créer l'INDEX CLUSTERED que je suppose que vous avez dans la production, et un INDEX NONCLUSTERED optionnel que vous devriez tester (je n'ai pas testé d'autres options et c'est implicitement la première qui m'est venue à l'esprit, il est donc recommandé de continuer à vérifier le bon index avec le vrai DDL+DML).

-- CLUSTERED INDEX
CREATE CLUSTERED INDEX IX_Budget_BudgetID
    ON dbo.Budget (BudgetID);   
GO 
CREATE CLUSTERED INDEX IX_Forecast_ForecastID
    ON dbo.Forecast (ForecastID);   
GO 
CREATE CLUSTERED INDEX IX_Records_RecordID  
    ON dbo.Records (RecordID);   
GO 

-- NONCLUSTERED INDEX
CREATE NONCLUSTERED INDEX NX_Budget_RecordID_BudgetID
    ON dbo.Budget (RecordID,BudgetID);   
GO 
CREATE NONCLUSTERED INDEX NX_Forecast_RecordID_ForecastID
    ON dbo.Forecast (RecordID,ForecastID);   
GO 
CREATE NONCLUSTERED INDEX NX_Records_RecordID_RecordID  
    ON dbo.Records (RecordID);   
GO

Answer 2

Je pense que je le ferais :

select Max(BudgetId) as BudgetID, RecordID
into #MBudget
from Budget
group by RecordID

select B.* 
into #MaxB
from #MBudget M
inner join Budget B
on M.BudgetID = B.BudgetID
and M.RecordID = B.RecordID

--The above will then only have the "maximum" BudgetID data from the table.

-- You need to then do the same with the Forecast table

select Max(ForecastId) as ForecastID, RecordID
into #MForecast
from Forecast
group by RecordID

select F.* 
into #MaxF
from #MForecast M
inner join Forecast F
on M.ForecastID = F.ForecastID
and M.RecordID = F.RecordID

-- Join them together on the RecordID

select *  -- you will need to pick the required fields
from #MaxF F
inner join #MaxB B 
on F.RecordID = B.RecordID
inner join Record R
on F.RecordID = R.RecordID

Je pense que cela vous permettra d'y arriver, je ne suis pas un grand fan de violon et je l'ai fait dans ma tête.

Je suis un peu d'accord avec les autres, vous auriez pu être plus clair mais j'espère que cela vous mènera là où vous devez être.

Answer 3

Enfin, nous créons la requête dynamique en tant que jointure multiple.

Tableaux d'entrée :

CREATE TABLE #Budget (BudgetID int, RecordID int, BDate date,Percentage int, [Status] varchar(50));
INSERT INTO #Budget(BudgetID, RecordID, BDate,Percentage,Status)
VALUES(1, 55, '2017-01-01', 60, 'ordered'),(2, 55, '2017-03-24', 40, 'ordered'),(3, 66, '2018-08-15', 100, 'invoiced'),(4, 77, '2018-12-02', 25, 'paid'),(5, 77, '2018-09-10', 35, 'ordered'),(6, 77, '2019-07-13', 40, 'ordered')

CREATE TABLE #Forecast(ForecastID int, RecordID int, FDate date, Percentage int)
INSERT INTO #Forecast(ForecastID, RecordID, FDate,Percentage)
VALUES(4, 77, '2018-07-18', 24),(1, 55, '2020-12-01', 100),(2, 77, '2023-05-17', 25),(3, 77, '2024-11-28', 75)

CREATE TABLE #Records(RecordID int, BAmount int, FAmount int, Name varchar(40), Description varchar(40) )
INSERT INTO #Records(RecordID, BAmount,FAmount,Name, Description)
VALUES(55, 15000, 33000, 'Prod1', 'Desc1' ),(66, 22000, 17500, 'Prod2', 'Desc2' ),(77, 40000, 44000, 'Prod3', 'Desc3' )

Requête de jonction finale

 select * from (select r1.RecordID, f1.FDate fdate1, b1.BDate bdate1
 , ROW_NUMBER() over(partition by r1.recordid order by (select NULL)) rn from #Records r1
 join #Forecast f1
 on r1.RecordID = f1.RecordID
 and r1.RecordID = 77
 join #Budget b1
 on r1.RecordID = b1.RecordID
)t1 join (select r2.RecordID, f2.FDate fdate2, b2.BDate bdate2
 , ROW_NUMBER() over(partition by r2.recordid order by (select NULL)) rn from #Records r2
 join #Forecast f2
 on r2.RecordID = f2.RecordID
 and r2.RecordID = 77
 join #Budget b2
 on r2.RecordID = b2.RecordID
)t2 on t1.RecordID = t2.RecordID and t1.rn < t2.rn join (select r3.RecordID, f3.FDate fdate3, b3.BDate bdate3
 , ROW_NUMBER() over(partition by r3.recordid order by (select NULL)) rn from #Records r3
 join #Forecast f3
 on r3.RecordID = f3.RecordID
 and r3.RecordID = 77
 join #Budget b3
 on r3.RecordID = b3.RecordID
)t3 on t2.RecordID = t3.RecordID and t2.rn < t3.rn join (select r4.RecordID, f4.FDate fdate4, b4.BDate bdate4
 , ROW_NUMBER() over(partition by r4.recordid order by (select NULL)) rn from #Records r4
 join #Forecast f4
 on r4.RecordID = f4.RecordID
 and r4.RecordID = 77
 join #Budget b4
 on r4.RecordID = b4.RecordID
)t4 on t3.RecordID = t4.RecordID and t3.rn < t4.rn join (select r5.RecordID, f5.FDate fdate5, b5.BDate bdate5
 , ROW_NUMBER() over(partition by r5.recordid order by (select NULL)) rn from #Records r5
 join #Forecast f5
 on r5.RecordID = f5.RecordID
 and r5.RecordID = 77
 join #Budget b5
 on r5.RecordID = b5.RecordID
)t5 on t4.RecordID = t5.RecordID and t4.rn < t5.rn join (select r6.RecordID, f6.FDate fdate6, b6.BDate bdate6
 , ROW_NUMBER() over(partition by r6.recordid order by (select NULL)) rn from #Records r6
 join #Forecast f6
 on r6.RecordID = f6.RecordID
 and r6.RecordID = 77
 join #Budget b6
 on r6.RecordID = b6.RecordID
)t6 on t5.RecordID = t6.RecordID and t5.rn < t6.rn join (select r7.RecordID, f7.FDate fdate7, b7.BDate bdate7
 , ROW_NUMBER() over(partition by r7.recordid order by (select NULL)) rn from #Records r7
 join #Forecast f7
 on r7.RecordID = f7.RecordID
 and r7.RecordID = 77
 join #Budget b7
 on r7.RecordID = b7.RecordID
)t7 on t6.RecordID = t7.RecordID and t6.rn < t7.rn join (select r8.RecordID, f8.FDate fdate8, b8.BDate bdate8
 , ROW_NUMBER() over(partition by r8.recordid order by (select NULL)) rn from #Records r8
 join #Forecast f8
 on r8.RecordID = f8.RecordID
 and r8.RecordID = 77
 join #Budget b8
 on r8.RecordID = b8.RecordID
)t8 on t7.RecordID = t8.RecordID and t7.rn < t8.rn join (select r9.RecordID, f9.FDate fdate9, b9.BDate bdate9
 , ROW_NUMBER() over(partition by r9.recordid order by (select NULL)) rn from #Records r9
 join #Forecast f9
 on r9.RecordID = f9.RecordID
 and r9.RecordID = 77
 join #Budget b9
 on r9.RecordID = b9.RecordID
)t9 on t8.RecordID = t9.RecordID and t8.rn < t9.rn

Vérifiez d'abord le PO à partir du tableau des IP et de la requête ci-dessus. Si la réponse est acceptée, vous utiliserez la PS suivante.

SP 1

exec rownumber

create procedure rownumber as                                           --1st sp.
declare @r int = 1, @select nvarchar(100) = 'select * from ', @count int, @finalquery nvarchar(max) = N'', @out nvarchar(max)
begin
 set @count = (select Count(*) from (select r.RecordID, f1.FDate fdate1, b1.BDate bdate1
  , ROW_NUMBER() over(partition by r.recordid order by (select NULL)) rn from #Records r
  join #Forecast f1
  on r.RecordID = f1.RecordID
  and r.RecordID = 77
  join #Budget b1
  on r.RecordID = b1.RecordID
  )t
 )
 if @count > 2
 begin
  while @count >= @r
  begin
   exec multyJoin @r, @out output
   set @finalquery += @out
   set @r = @r + 1
  end
 end
 else begin
  select * from (
   select r.RecordID, f1.FDate fdate1, b1.BDate bdate1
   , ROW_NUMBER() over(partition by r.recordid order by (select NULL)) rn from #Records r
   join #Forecast f1
   on r.RecordID = f1.RecordID
   and r.RecordID = 77
   join #Budget b1
   on r.RecordID = b1.RecordID
  )t
 end
 set @finalquery = stuff(@finalquery,1, 6,@select)
 set @finalquery = REPLACE(@finalquery, 'on t0.RecordID = t1.RecordID and t0.rn < t1.rn ','')
 print @finalquery
 exec sp_executesql @finalquery
end

SP 2

create procedure multyJoin (@r int, @join varchar(max) output) as       --2nd sp. which is called form below sp
declare @cond nvarchar(100), @rvc varchar(3) = convert(int, @r)     ----row number of current in varchar
, @rvp varchar(3) = convert(int, @r-1)      ----row number of previous in varchar
begin

 set @join = ' join (select r'+@rvc+'.RecordID, f'+@rvc+'.FDate fdate'+@rvc+', b'+@rvc+'.BDate bdate'+@rvc+  ---Here add your columns as like as 'Percentage'+@rvc+' * '+@rvc+'BAmount'
  ', ROW_NUMBER() over(partition by r'+@rvc+'.recordid order by (select NULL)) rn from #Records r'+@rvc+'
  join #Forecast f'+@rvc+'
  on r'+@rvc+'.RecordID = f'+@rvc+'.RecordID
  and r'+@rvc+'.RecordID = 77
  join #Budget b'+@rvc+'
  on r'+@rvc+'.RecordID = b'+@rvc+'.RecordID
 )t'+@rvc+
 ' on t'+@rvp+'.RecordID = t'+@rvc+'.RecordID and t'+@rvp+'.rn < t'+@rvc+'.rn'  --Here multiple join has been created. Each table contains one row
end