如何显示表的所有列

Question

我正在制作一个过滤器的存储生成器。我的sp里有很多类型的支票，比如

if(@cityId!=0 and @sourceLatitude!=0 and @sourceLongitude!=0 
and @KM!=0 and  @food='' and @price='' and @sort='')

但我只显示一个表对所有检查，但我也使用group和AVG()。

我的问题是，有没有办法显示所有的字段，而不写group by.

我的代码

SELECT

--cast(AVG(rr.Rates ) as decimal(10,5)) Rating,
round(AVG(rr.Rates ),2) Rating,
[dbo].[Fun_distanceCalculate](r.Latitude,r.Longitude,@sourceLatitude,@sourceLongitude) AS Distance,
[dbo].[Fun_calculate_delivery_fee](r.Latitude,r.Longitude,@sourceLatitude,@sourceLongitude) AS DeliveryCharges,
r.Id,
r.IsActive,
r.CreatedDate,
Name,
TagLine,
ApproximateCostPerPerson,
FullAddress2,
FullAddress,
Tags,
IsRecommend,
Longitude,
DeliveryTime,
IsOnline,
Logo,
ImageUrl,
Latitude,
ProductDiscount,
TagLine,
IsApproved,
Salt,
Email,
PhoneNumber,
CityId,
CountryId


FROM [dbo].[Restaurants] r left join [dbo].[RestaurantReviews] rr

on r.Id=rr.RestaurantId
WHERE r.IsActive = 1 and r.IsApproved=1 and ((r.CityId=@cityId) OR @KM > [dbo].[Fun_distanceCalculate](r.Latitude,r.Longitude,@sourceLatitude,@sourceLongitude))
--ORDER BY Id  DESC
--OFFSET  @PageNumber  ROWS 
--FETCH NEXT @pageSize ROWS ONLY
group by r.Id,Name,
r.IsActive,
TagLine,
ApproximateCostPerPerson,
FullAddress2,
FullAddress,
IsOnline,
Tags,
Logo,
IsRecommend,
Longitude,
DeliveryTime,
ImageUrl,
Latitude,
ProductDiscount,
TagLine,
IsApproved,
Salt,
Email,
PhoneNumber,
CityId,
r.CreatedDate,
CountryId 

Answer 1

您可以将聚合移动到CTE，然后将CTE连接到主查询。

WITH AvgRates
AS
(
r.Id
round(AVG(rr.Rates ),2) AS Rating,
FROM [dbo].[Restaurants] r left join [dbo].[RestaurantReviews] rr on r.Id=rr.RestaurantId
--can use the WHERE filtering here or in the final select
GROUP BY r.Id
)
SELECT
av.Rating,
--rest of your query
FROM [dbo].[Restaurants] r left join [dbo].[RestaurantReviews] rr on r.Id=rr.RestaurantId
INNER JOIN AvgRates av ON r.Id = av.Id
--rest of your WHERE filtering etc.