SQL内部联接仅显示唯一的驱动程序

SQL内部联接仅显示唯一的驱动程序,第1张

学习,善待。

目前,此声明显示了他们用于这些旅行的司机和车辆ID。我如何才能使这个节目只显示哪些驾驶员使用了多辆车而不是车辆ID列表和哪个驾驶员使用过它? (这有意义吗?):

SELECT DISTINCT s.vehicleID, o.FirstName   ' '   o.LastName AS Driver
FROM Operators AS o, Runs AS r, Schedules AS s JOIN Trips AS t
ON s.scheduleID = t.scheduleID 
WHERE r.BidDate >= '09/01/2004' AND r.BidDate <= '09/30/2004'

最佳答案:

4 个答案:

答案 0 :(得分:1)

SELECT COUNT(DISTINCT s.vehicleID) AS Count, o.FirstName   ' '   o.LastName AS Driver
FROM Operators AS o
JOIN Runs AS r ON o.ID = r.OperatorID
JOIN Schedules AS s ON r.ScheduleID = s.ScheduleID
WHERE r.BidDate BETWEEN '09/01/2004' AND '09/30/2004' 
GROUP BY o.FirstName   ' '   o.LastName
HAVING COUNT(DISTINCT s.vehicleID) > 1

答案 1 :(得分:0)

您可以使用GROUP BY子句:

SELECT o.FirstName   ' '   o.LastName AS Driver, COUNT(s.vehicleID) NoOfVehicleUsed
FROM Operators AS o, Runs AS r, Schedules AS s JOIN Trips AS t
ON s.scheduleID = t.scheduleID 
WHERE r.BidDate >= '09/01/2004' AND r.BidDate <= '09/30/2004'
GROUP BY o.FirstName   ' '   o.LastName
HAVING COUNT(s.vehicleID) > 1

答案 2 :(得分:0)

我真的不知道每张桌子的目的是什么,但假设你有桌子(map_driver_To_vehicle):

driverId , vehicleID

代表

  • driverid 5参加了vehicleId 1
  • driverid 2参加了vehicleId 5
  • 等...

你可以这样做:

select driverId , cnt=count(distinct vehicleID )
from map_driver_To_vehicle
group by driverid
having count(distinct vehicleID ) >1
nb:你说的车多了一辆车。因此,我认为你的意思是不同的车,而不是使用一辆 - 同一辆车。

无论哪种方式,您都可以删除不同的。

答案 3 :(得分:0)

我最好的猜测是不知道表中的外键字段名称

SELECT
    o.FirstName   ' '   o.LastName AS Driver,
    COUNT(s.vehicleID) AS VehicleCount
FROM
    Operators AS o
    JOIN Runs AS r ON o.operatorID = r.operatorID
    JOIN Schedules AS s ON r.runsID = s.runsID
WHERE 
    r.BidDate >= '09/01/2004'
    AND r.BidDate <= '09/30/2004'
GROUP BY 
    o.FirstName   ' '   o.LastName
HAVING 
    COUNT(s.vehicleID) > 1
本文经用户投稿或网站收集转载,如有侵权请联系本站。

发表评论

0条回复