Recherche d'loggings dans la table principale qui correspondent aux loggings d'une autre table dans SQL Server

Ow, mon cerveau fait mal …

Remplacez tous les points d'interrogation (3 d'entre eux) par l'ID de voyage du voyage où vous souhaitez vérifier les voyages similaires.

 select distinct s.sortingpIDFK as sortingpId from Legs l left join Slices s on l.sliceIDFK = s.sliceId where s.sortingpIDFK != ? and not exists ( select carrier, flightNumber, departureAirport, departureDate from Legs l2 left join Slices s2 on l2.sliceIDFK = s2.sliceId where s2.sortingpIDFK = s.sortingpIDFK except select carrier, flightNumber, departureAirport, departureDate from Legs l2 left join Slices s2 on l2.sliceIDFK = s2.sliceId where s2.sortingpIDFK = ? ) and not exists ( select carrier, flightNumber, departureAirport, departureDate from Legs l2 left join Slices s2 on l2.sliceIDFK = s2.sliceId where s2.sortingpIDFK = ? except select carrier, flightNumber, departureAirport, departureDate from Legs l2 left join Slices s2 on l2.sliceIDFK = s2.sliceId where s2.sortingpIDFK = s.sortingpIDFK ) order by s.sortingpIDFK 

La viande de la requête est la clause and not exists . Ils obtiennent datatables de la jambe pour un voyage et soustrait effectivement datatables de la jambe d'un autre voyage en utilisant la clause except . S'il ne vous rest rien, datatables du deuxième trajet contiennent toutes datatables du premier trajet. Vous devez exécuter la clause and not exists deux fois (avec les opérandes inversés) pour vous assurer que les deux sets de données de triggersment sont vraiment identiques et que l'un n'est pas simplement un sous-set de l'autre.

Ceci n'est en aucun cas adaptable à un grand nombre de lignes.

J'espère que cela t'aides.

Passez juste la base TripId (dans @BaseTripID) avec laquelle vous voulez comparer d'autres loggings. Je suppose que vous n'êtes concerné que par le transporteur, le numéro de vol, l'aéroport de départ, l'aéroport d'arrivée pour correspondre exactement à tout autre voyage sans égard aux champs de date.

 create table Trips(sortingpId int,saleTotal int,queryDate date) create table Slices(sliceId int ,connections int,duration int ,sortingpIdFK int) create table Legs(legId int, carrier char(2), flightNumber int, departureAirport char(3), departureDate date, ArrivalAirport char(3), ArrivalDate date, sliceIDFK int) insert into Trips values(1,200,'6/10/2015'),(2,198,'6/11/2015'),(3,300,'6/15/2015'),(4,200,'6/21/2015') insert into Slices values(1,1,50,1),(2,1,45,1),(3,0,60,2),(4,1,85,2),(5,1,50,3),(6,1,45,3),(7,1,45,4),(8,1,45,4) insert into Legs values(1,'AA',1,'JFK','7/1/2015','LON','7/2/2015',1) , (2,'AA',2,'LON','7/2/2015','FRA','7/2/2015',1), (3,'AA',11,'FRA','7/10/2015','LON','7/10/2015',2), (4,'AA',12,'LON','7/10/2015','JFK','7/10/2015',2), (5,'UA',5,'EWR','8/1/2015','LAX','8/1/2015',3), (6,'UA',6,'LAX','8/5/2015','ORD','8/5/2015',4), (7,'UA',7,'ORD','8/5/2015','EWR','8/5/2015',4), (8,'AA',1,'JFK','7/11/2015','LON','7/12/2015',5), (9,'AA',2,'LON','7/12/2015','FRA','7/12/2015',5), (10,'AA',11,'FRA','7/20/2015','LON','7/20/2015',6), (11,'AA',12,'LON','7/20/2015','JFK','7/20/2015',6), (12,'AA',1,'JFK','7/1/2015','LON','7/2/2015',7) , (13,'AA',2,'LON','7/2/2015','FRA','7/2/2015',7), (14,'AA',11,'FRA','7/10/2015','BEL','7/10/2015',8), (15,'AA',12,'BEL','7/10/2015','JFK','7/10/2015',8) --select * from Trips --select * from Slices --select * from Legs ------------------------------------------------------------------- Declare @BaseTripID int = 1, @Legs int ,@MatchingTripID int declare @BaseTrip table(carrier char(2), flightNumber int, departureAirport char(3), ArrivalAirport char(3),row_no int) declare @MatchingTrip table(carrier char(2), flightNumber int, departureAirport char(3), ArrivalAirport char(3),row_no int,legid int,sortingpid int) insert into @BaseTrip select carrier, flightNumber, departureAirport, ArrivalAirport,ROW_NUMBER() over(order by l.legId) from Legs l join slices s on s.sliceId = l.sliceIDFK where s.sortingpIdFK = @BaseTripID select @Legs=count(*) from @BaseTrip Insert into @MatchingTrip select carrier, flightNumber, departureAirport, ArrivalAirport,ROW_NUMBER() over(partition by s.sortingpIdFK order by l.legId) as row_no,l.legId,s.sortingpIdFK from Legs l join slices s on s.sliceId = l.sliceIDFK and s.sortingpIdFK in (select s.sortingpIdFK from Legs l join slices s on s.sliceId = l.sliceIDFK and s.sortingpIdFK <> @BaseTripID Group by s.sortingpIdFK having count(l.legId)=@Legs) select @MatchingTripID = m.sortingpid from @MatchingTrip m join @BaseTrip b on m.carrier = b.carrier and m.flightNumber = b.flightNumber and m.departureAirport = b.departureAirport and m.ArrivalAirport = b.ArrivalAirport and m.row_no = b.row_no GROUP BY m.sortingpid HAVING COUNT(*) = @Legs select s.sortingpIdFK as matchingTripID,l.legid,l.carrier,l.flightNumber,l.departureAirport,l.ArrivalAirport from Legs l join Slices s on s.sliceId = l.sliceIDFK where s.sortingpIdFK = @MatchingTripID --------------------- drop table Trips drop table Slices drop table Legs 

L'utilisation du nombre de jambes est la key.Ainsi, nous éliminons tous les matches autres que des jambes complètement identiques (voyage 4 avec seulement deux jambes correspondant). Alors maintenant, nous obtenons seulement Trip 3 comme loggings correspondants.

S'il vous plaît noter que nous excluons également les voyages, qui a des jambes supplémentaires en plus des correspondants. J'espère que c'est ce que vous attendez, une paire de voyages parfaitement identiques.

Une autre approche consiste à déterminer / stocker un legsKey avec chaque tranche de sorte que vous pouvez find des tranches correspondant à travers les voyages.

Pour chaque tranche, votre legsKey est le support et le numéro de vol de chaque jambe, ce que vous pouvez faire en utilisant For XML Path, comme ceci

quelque chose comme:

 select distinct stuff(( select ',' + l.carrier + ':' + l.flightNumber from legs l where l.carrier = carrier and l.flightnumber = flightnumber order by l.carrier, l.flightnumber for xml path('') ),1,1,'') as legsList from legs group by carrier, flightnumber