SQL Max 按查询分组帮助

Question

我有一个简单的问题。如何在一个查询中选择我需要的两个值？目前我正在这样做，效果很好，但显然在一个应该解决问题的时候运行了两个查询。我尝试了 MAX(columnA) 和 GROUP BY ColumnB，但返回多行。我只想返回一行。

DECLARE @biID  bigint  
, @dtThreshold      DateTime 

  SELECT @biID = MAX(biID)
FROM tbPricingCalculationCount WITH (NOLOCK)

  SELECT @dtThreshold = dtDateTime
FROM tbPricingCalculationCount WITH (NOLOCK)
WHERE biID = @biID

我希望在一个查询中正确设置这两个变量。我怎样才能做到这一点？

谢谢， 〜ck

Answer 1

你能不这样做吗？

SELECT TOP 1 @biID = biID, @dtThreshold = dtDateTime 
  FROM tbPricingCalculationCount WITH (NOLOCK) 
ORDER BY biID DESC;

Answer 2

怎么样：

DECLARE
  @biID bigint,
  @dtThreshold DateTime 

SELECT
  @dtThreshold = A.dtDateTime,
  @biID = B.biID
FROM tbPricingCalculationCount A
  INNER JOIN (SELECT MAX(biID) biID
              FROM tbPricingCalculationCount) B
    ON A.biID = B.biID

如果您不在其他地方使用 biID，您甚至可以将其修剪为：

DECLARE
  @dtThreshold DateTime 

SELECT
  @dtThreshold = A.dtDateTime
FROM tbPricingCalculationCount A
  INNER JOIN (SELECT MAX(biID) biID
              FROM tbPricingCalculationCount) B
    ON A.biID = B.biID

Answer 3

怎么样

SELECT TOP 1 @biID = biID, @dtThreshold = dtDateTime
FROM tbPricingCalculationCount (NOLOCK)
ORDER BY biID desc

Answer 4

这将返回具有最大 biID 的行的 dtDateTime：

SELECT t1.dtDateTime
FROM tbPricingCalculationCount t1
LEFT JOIN tbPricingCalculationCount t2
ON t2.biID > t1.biID
WHERE t2.biID IS NULL

如果多个行共享相同的“最大”biID，则需要使用 TOP 将结果限制为 1：

SELECT TOP 1 t1.dtDateTime
FROM tbPricingCalculationCount t1
LEFT JOIN tbPricingCalculationCount t2
ON t2.biID > t1.biID
WHERE t2.biID IS NULL