是的，您可以为此使用HAVING

SELECT Shop_number
FROM   Details
GROUP  BY Shop_number
HAVING MAX(CASE WHEN Reference_number = 48 THEN Value END) > 
       MAX(CASE WHEN Reference_number = 54 THEN Value END)

另一种方法，正如您在自连接中提到的：

SELECT a.Shop_number, a.Value AS Value48, b.Value as Value54
FROM details a
INNER JOIN details b 
  ON a.Shop_number = b.Shop_number 
   AND a.Reference_number = 48 
   AND b.Reference_number = 54
WHERE a.Value > b.Value

使用透视聚合方法：

SELECT Shop_number
FROM Details
GROUP BY Shop_number
HAVING MAX(CASE WHEN Reference_number = 48 THEN Value END) >
       MAX(CASE WHEN Reference_number = 54 THEN Value END);

或者您可以通过子查询检查其他值：

SELECT DISTINCT Shop_number
FROM Details d1
WHERE Reference_number=48
  AND Value > (
    select top 1 Value 
    from Details 
    where Shop_number=d1.Shop_number
      and Reference_number=54
  )