如何使用子查询选择最大值？

2024-05-09 • 问答

我正在尝试构造一个困难的SQL Server查询以选择特定数据。首先，让我给您一些背景信息...

我正在使用一个数据库，需要使用此查询访问3个表。您可以在图中看到该图。如您所见，此数据库中有用户。这些用户可以购买订阅，以在城市中使用一些公共自行车。订阅具有订阅类型。此订阅类型声明订阅有效的时间。可以是一天，一个月或一年。一个用户可以有多个订阅。

对于我的查询，我需要选择userId，名称，由街道，数字，邮政编码，城市和国家/地区代码组成的地址以及订阅类型的描述。对于每个用户，我只需要最长的订阅时间。因此您必须在subscriptionTypeId DESC上过滤查询。不仅如此……一个用户可以拥有同一类型的多个订阅，因此我只需要最新的订阅即可。这意味着我还必须在订阅类型期间最长的时间上对ValidFrom DESC进行过滤。

我知道我需要为此使用子查询，但是似乎找不到合适的查询...这就是我能走多远了，但是我一直在使用多种技术来尝试获得我的东西需要：

SELECT 
    su.userId,u.name,u.street,u.number,u.zipcode,u.CountryCode,st.description,MAX(su.validFrom) AS MaxValidFrom,(SELECT MAX(SubscriptionTypeId) FROM dbo.Subscriptions 
     WHERE UserId = su.UserId) AS MaxSubscriptionTypeId
FROM 
    dbo.Users u 
INNER JOIN 
    dbo.Subscriptions su ON u.userId = su.userId
INNER JOIN 
    dbo.SubscriptionTypes st ON su.subscriptionTypeId = st.subscriptionTypeId
HAVING 
    su.SubscriptionTypeId = MAX(su.SubscriptionTypeId)
ORDER BY 
    su.UserId,MaxValidFrom DESC;

预先感谢您的帮助！

Joren

Joren，

尝试这样的缩放器函数调用

CREATE FUNCTION MaxSubscriptionTypeID

(   @UserId int ) RETURNS int AS BEGIN  DECLARE @ID int     SELECT @ID = MAX(SubscriptionTypeId) FROM dbo.Subscriptions 
     WHERE UserId = @UserID     -- Return the result of the function    RETURN idnull(@ID,0)

END GO

SELECT 
    su.userId,u.name,u.street,u.number,u.zipcode,u.CountryCode,st.description,MAX(su.validFrom) AS MaxValidFrom,dbo.MaxSubscriptionTypeID( su.UserId) AS MaxSubscriptionTypeId FROM 
    dbo.Users u  INNER JOIN 
    dbo.Subscriptions su ON u.userId = su.userId INNER JOIN 
    dbo.SubscriptionTypes st ON su.subscriptionTypeId = st.subscriptionTypeId HAVING 
    su.SubscriptionTypeId = MAX(su.SubscriptionTypeId) ORDER BY 
    su.UserId,MaxValidFrom DESC;

SQL可能会与内联子查询捆绑在一起，具体取决于功能和资源索引等。

我会避免使用自定义函数，因为查询计划几乎总是受到它的负面影响。

将其分解，将为您提供用户曾经拥有的Max（SubscriptionTypeId），以及订阅该TypeId的用户的最新实例。

SELECT 
    u.userId,u.CountryCode
    --,st.description 
    --,--,(SELECT MAX(SubscriptionTypeId) FROM dbo.Subscriptions WHERE UserId = su.UserId) AS MaxSubscriptionTypeId
into #temp
FROM 
    dbo.Users u 
WHERE EXISTS (SELECT top 1 1 from dbo.Subscriptions i_su where i_su.UserId = u.UserId)
--Now you have a list of all who have at least 1 subscription inside a temp table.

Select u.*,su.ValidFrom,su.SubscriptionTypeId
    --This function will return a "Rank" for each user (partition) in order of the "Order By" clause.,ROW_NUMBER() OVER (partition by u.UserId order by SubscriptionTypeId DESC,ValidFrom DESC) as RowNo
into #temp2
FROM #temp u
INNER JOIN dbo.Subscriptions su
ON u.userId = su.userId
INNER JOIN dbo.SubscriptionTypes st
ON su.subscriptionTypeId = st.subscriptionTypeId

SELECT *
FROM #temp2
where RowNo = 1
-- RowNo 1 has the data you want based on the rank.

if (object_id('temptb..#temp') IS NOT NULL)
    DROP TABLE #temp
if (object_id('temptb..#temp2') IS NOT NULL)
    DROP TABLE #temp2

如何使用子查询选择最大值？

thebestme_1 回答：如何使用子查询选择最大值？

大家都在问