时间戳

首页 > 解决方案 > 如何显示表格的所有列

sql - 如何显示表格的所有列

问题描述

我正在为过滤器的商店生产商工作。我的 sp 中有很多类型的支票，就像

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

但我只针对所有检查显示一张表，但我也在使用组和 AVG()。

我的问题是，有什么方法可以显示所有字段而不用分组写入。

我的代码

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

标签： sqlsql-servergroup-byaverage

解决方案

您可以将聚合移动到 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.

推荐阅读