从表中为所选的每一行选择可选行

Question

SELECT 
R.ArrivalAirportID, 
(
SELECT TOP(1) A1.AirportID 
FROM Airport A1 
WHERE A1.ISO_country = 'PT' 
ORDER BY( NEWID())
) 'PT Aleatory Airport'
FROM Routes R WHERE (
SELECT A2.ISO_country 
FROM Airports A2 
WHERE A2.AirportID = R.ArrivalAirportID
) != 'PT

这是我的SELECT，我希望列PT Aleatory Airport中的AirportID是随机的，但SELECT的所有行的AirportID总是相同的。

如何为SELECT中的每个R.ArrivalAirportID设置一个随机的AirportID FROM Airport A1 WHERE A1.ISO_country = 'PT'？

Answer 1

您需要在子查询中有一个外部引用，以确保它是逐行执行的，而不仅仅是一次。我认为这可能会做到：

SELECT 
R.ArrivalAirportID, 
(
SELECT TOP(1) A1.AirportID 
FROM Airport A1 
WHERE A1.ISO_country = 'PT' 
AND AI.AirportID <> R.ArrivalAirportID
ORDER BY( NEWID())
) 'PT Aleatory Airport'
FROM Routes R WHERE (
SELECT A2.ISO_country 
FROM Airports A2 
WHERE A2.AirportID = R.ArrivalAirportID
) != 'PT';

由于R.ArrivalAirportID仅限于国家/地区代码不是PT的机场，而AI.AirportID仅限于国家或地区代码是PT的机场，因此这两个ID无论如何都不可能相等，因此此附加谓词不会更改查询的含义，但会更改执行计划。

如果这不起作用，那么另一种选择是将一个连续的行号分配给aleatory pot中的每个机场，然后为每个

WITH RandomAirports AS
(   
SELECT  a.AirportID,
RowNumber = ROW_NUMBER() OVER(ORDER BY a.AirportID)
FROM    Airport AS a 
WHERE   a.ISO_country = 'PT' 
)
SELECT  r.ArrivalAirportID,
[PT Aleatory Airport] = ra.AirportID
FROM    Routes AS r
INNER JOIN dbo.Airports AS a
ON a.AirportID = r.ArrivalAirportID
CROSS JOIN (SELECT COUNT(*) FROM RandomAirports) AS rm (RandomMax)
LEFT JOIN RandomAirports AS ra
ON r1.RowNumber = CEILING(RAND(CHECKSUM(NEWID())) * rm.RandomMax)
WHERE   a.ISO_country <> 'PT';

这里的关键行是：

r1.RowNumber = CEILING(RAND(CHECKSUM(NEWID())) * rm.RandomMax)

这会产生一个介于1和可供选择的机场数量之间的随机数，因此将有效地随机选择一个。