SQL Server - T-SQL "not contained in either an aggregate function or the GROUP BY clause"

Question

我有一个工作查询，它会告诉我在美国离每个邮政编码最近的商店位置，但当我试图让外部查询选择所述商店的相关邮政编码时，它会告诉我必须将zipcode添加到聚合中；然而，如果我通过Zip进行订单，那么它不会向我显示所有的笛卡尔乘积。感谢您的帮助。

select
Sub1.Zip as Zip,
Sub1.Zip_ID as Zip_ID,
MIN(Sub1.Distance) as Distance from
    (
    SELECT 
    Z.Zip,
    Z.Zip_ID, 
       ((Sqrt(Square((Z.lat  - S.lat) *68.96799738887665) 
         + Square((Z.long - S.long)*54.69366983621222)))) Distance
    FROM   
    Tbl_Stores_Coordinates S
    CROSS JOIN Zip_Code_Coordinates Z
    )Sub1
group by Sub1.Zip_ID

Answer 1

您之所以出现此错误，是因为您是SELECT，而不是Zip和Zip_ID，但您只是GROUP和Zip_ID。

您必须通过SELECT的所有非聚合列来GROUP，如下所示：

select
Sub1.Zip as Zip,
Sub1.Zip_ID as Zip_ID,
MIN(Sub1.Distance) as Distance
....
group by Sub1.Zip, Sub1.Zip_ID