I am actually trying to figure out the best way to generate some random test data and in the process I have come across some strange behaviour with a CTE. Maybe someone can explain why it is happening; also, I would certainly appreciate any advice on what would be a better way to randomly join the rows of two tables (TableA and TableB) such that each row in TableA is joined to only one row in TableB and every row in TableB is joined to at least one row in TableA. For example, TableA has 5 distinct rows and TableB has 3 distinct rows. A random join should result in something like this:
TableA TableB
1 3
2 1
3 1
4 2
5 1
It seems that it is necessary for some reason to use an interim table (@Q) to be able to ensure that a correct result is returned which has all rows from both TableA and TableB; just relying on the CTE results in not all the rows from TableB being included. I understand that sometimes no result is returned due to a failure of some kind in the cross apply which i have yet to identify and goes to the point that I am sure there is a better way to perform this operation. I hope that makes sense. Thanks in advance!
declare @TableA table (
ID int
);
declare @TableB table (
ID int
);
declare @Q table (
TrackRN int,
TableAID int,
TableBID int
);
with cte as (
select
1 as ID
union all
select
ID + 1
from cte
where ID < 5
)
insert @TableA (ID)
select ID from cte;
with cte as (
select
1 as ID
union all
select
ID + 1
from cte
where ID < 3
)
insert @TableB (ID)
select ID from cte;
select * from @TableA;
select * from @TableB;
with cte as (
select
row_number() over (partition by TableAID order by newid()) as TrackRN,
TableAID,
TableBID
from (
select
a.ID as TableAID,
b.ID as TableBID
from @TableA as a
cross apply @TableB as b
) as M
)
select --All rows from TableB not always included
TableAID,
TableBID
from cte
where TrackRN in (
select
top 1
iCTE.TrackRN
from cte as iCTE
group by iCTE.TrackRN
having count(distinct iCTE.TableBID) = (
select count(1) from @TableB
)
)
order by TableAID;
with cte as (
select
row_number() over (partition by TableAID order by newid()) as TrackRN,
TableAID,
TableBID
from (
select
a.ID as TableAID,
b.ID as TableBID
from @TableA as a
cross apply @TableB as b
) as M
)
insert @Q
select
TrackRN,
TableAID,
TableBID
from cte;
select * from @Q;
select --All rows from both TableA and TableB included
TableAID,
TableBID
from @Q
where TrackRN in (
select
top 1
iQ.TrackRN
from @Q as iQ
group by iQ.TrackRN
having count(distinct iQ.TableBID) = (
select count(1) from @TableB
)
)
order by TableAID;
Aucun commentaire:
Enregistrer un commentaire