SQL Server 2008选择和筛选数据

本文介绍了SQL Server 2008选择和筛选数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有3张桌子

1个用于车辆

I have 3 tables

1 for Vehicles

VehicleId
Code

1个用于Oweners

1 for Oweners

OwnerId
Name

1用于车主历史记录

1 for Vehicle Owner History

VehicleId
OwenerId
OwnedFromDate

我需要做的是获取每个车辆的清单，以及在用户指定的日期拥有车辆的人，例如每辆车的前1名，WHERE OwnedFromDate< =
指定的日期ORDER BY OwnedFromDate降序.

What I need to do is get a list of every vehicle and who owned the vehicle on a date specifed by the user e.g. TOP 1 for each vehicle WHERE OwnedFromDate <=
Specified Date ORDER BY OwnedFromDate DESCending.

SELECT  TOP 1  dbo.Owners.HaulierId, dbo.Vehicles.VehicleId, dbo.Vehicles.Code, dbo.Owners.Name, dbo.VehicleOwnershipHistory.OwnedFromDate
FROM         dbo.VehicleOwnershipHistory INNER JOIN
                      dbo.Vehicles ON dbo.VehicleOwnershipHistory.VehicleId = dbo.Vehicles.VehicleId INNER JOIN
                      dbo.Haulier ON dbo.VehicleOwnershipHistory.OwnerId = dbo.Owner.HaulierId
WHERE dbo.VehicleOwnershipHistory.OwnedFromDate <= @Date
ORDER BY dbo.VehicleOwnershipHistory.OwnedFromDate DESC

推荐答案

SELECT
    dbo.Owners.HaulierId,
    dbo.Vehicles.VehicleId,
    dbo.Vehicles.Code,
    dbo.Owners.Name,
    dbo.VehicleOwnershipHistory.OwnedFromDate,
    RANK() over(partition by dbo.VehicleOwnershipHistory.OwnedFromDate order by dbo.Owners.HaulierId) Rnk
FROM dbo.VehicleOwnershipHistory
    INNER JOIN dbo.Vehicles
        ON dbo.VehicleOwnershipHistory.VehicleId = dbo.Vehicles.VehicleId
    INNER JOIN dbo.Haulier
        ON dbo.VehicleOwnershipHistory.OwnerId = dbo.Owner.HaulierId

WHERE dbo.VehicleOwnershipHistory.OwnedFromDate <= @Date
    and Rnk = 1
ORDER BY dbo.VehicleOwnershipHistory.OwnedFromDate DESC

从您的查询中，我删除了top 1并使用了

From your query i have removed top 1 and used

RANK() over(partition by dbo.VehicleOwnershipHistory.OwnedFromDate order by dbo.Owners.HaulierId) Rnk

并设置条件以仅使用第一等级

and put a condition to fetch the data with only First Rank

Where Rnk = 1

希望这会有所帮助.

Hope this will help..

这篇关于SQL Server 2008选择和筛选数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

VEHICLE