如何在 SQL 中从此查询中仅输出一个最大值?

问题描述

昨天 Thomas 提供了 正是我想要的查询.现在我需要它的一个变体，希望有人能帮助我.

Yesterday Thomas helped me a lot by providing exactly the query I wanted. And now I need a variant of it, and hopes someone can help me out.

我希望它只输出一行，即最大值 - 但它必须建立在以下查询中的算法上:

I want it to output only one row, namely a max value - but it has to build on the algorithm in the following query:

WITH Calendar AS (SELECT     CAST(@StartDate AS datetime) AS Date
                                          UNION ALL
                                          SELECT     DATEADD(d, 1, Date) AS Expr1
                                          FROM         Calendar AS Calendar_1
                                          WHERE     (DATEADD(d, 1, Date) < @EndDate))
    SELECT     C.Date, C2.Country, COALESCE (SUM(R.[Amount of people per day needed]), 0) AS [Allocated testers]
     FROM         Calendar AS C CROSS JOIN
                            Country AS C2 LEFT OUTER JOIN
                            Requests AS R ON C.Date BETWEEN R.[Start date] AND R.[End date] AND R.CountryID = C2.CountryID
     WHERE     (C2.Country = @Country)
     GROUP BY C.Date, C2.Country OPTION (MAXRECURSION 0)

上面的输出将类似于:

Date            Country         Allocated testers
06/01/2010      Chile             3
06/02/2010      Chile             4
06/03/2010      Chile             0
06/04/2010      Chile             0
06/05/2010      Chile            19

但我现在需要的是

Allocated testers
           19

即 - 只有一列 - 一行 - 最大值本身...(对于(通过参数(已经存在))选定的日期和国家/地区期间)

that is - only one column - one row - the max value itself... (for the (via parameters (that already exists)) selected period of dates and country)

推荐答案

WITH  Calendar
        AS (
             SELECT
              CAST(@StartDate AS datetime) AS Date
             UNION ALL
             SELECT
              DATEADD(d, 1, Date) AS Expr1
             FROM
              Calendar AS Calendar_1
             WHERE
              ( DATEADD(d, 1, Date) < @EndDate )
           )
SELECT TOP 1 *
FROM 
(           
  SELECT
    C.Date
   ,C2.Country
   ,COALESCE(SUM(R.[Amount of people per day needed]), 0) AS [Allocated testers]
  FROM
    Calendar AS C
    CROSS JOIN Country AS C2
    LEFT OUTER JOIN Requests AS R
      ON C.Date BETWEEN R.[Start date] AND R.[End date]
         AND R.CountryID = C2.CountryID
  WHERE
    ( C2.Country = @Country )
  GROUP BY
    C.Date
   ,C2.Country
OPTION
    ( MAXRECURSION 0 )
    ) lst
    ORDER BY lst.[Allocated testers] DESC

这篇关于如何在 SQL 中从此查询中仅输出一个最大值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！