SQL/LINQ的的GroupBy与最小值,并根据列
问题描述:
我与这些例子行的表:SQL/LINQ的的GroupBy与最小值,并根据列
CouponID ShopID ShopLongitude ShopLatitude 365 1881 55,5574105 9,9613295 365 23550 55,5510846 9,9936818 365 33550 55,6220936 10,0663895 365 33551 55,5573436 9,9611765 366 1881 55,5574105 9,9613295 366 23550 55,5510846 9,9936818 367 1881 55,5574105 9,9613295 533 1881 55,5574105 9,9613295 533 23550 55,5510846 9,9936818 533 33550 55,6220936 10,0663895 533 33551 55,5573436 9,9611765 354 1881 55,5574105 9,9613295 354 23550 55.5510846 9,9936818 354 33550 55,6220936 10,0663895 354 33551 55,5573436 9,9611765
我想有结果,每最近ShopID CouponID
我已经有查询:
SELECT CouponID, MIN (dbo.DistanceBetween (53.54613,9.98537,ShopLatitude,ShopLongitude)) as distance
FROM Table
GROUP BY CouponID
ORDER BY CouponID, distance ASC
输出该CouponID并向ShopID的最小距离为每个优惠券:
CouponID distance 354 0,778524633472375 365 0,778524633472375 366 0,778524633472375 367 2,02548179145764
在LINQ的语句如下:
var coupon = (from c in dbContext.table
group c by c.CouponID into cgrp
select new
{
CouponID = cgrp.Key,
Distance = cgrp.Min(c => DistanceBetween(latitude, longitude, c.ShopLatitude, c.ShopLongitude))
})
.OrderBy(c => c.CouponID).ThenBy(c => DistanceBetween(latitude, longitude, c.ShopLatitude, c.ShopLongitude));
我怎么也拿回相关ShopID从 查询和LINQ声明的最小距离?
答
SELECT c.CouponID, c.ShopID, nearest.distance
FROM
Coupons C JOIN
(
SELECT CouponID, MIN (dbo.DistanceBetween (53.54613,9.98537,ShopLat,ShopLon)) as distance
FROM Coupons
GROUP BY CouponID
) nearest on c.CouponID = nearest.CouponID
AND dbo.DistanceBetween (53.54613,9.98537,ShopLat,ShopLon) = nearest.distance
ORDER BY CouponID, distance ASC
答
只是修改您的SQL查询来执行分组并加入你想要的。
创建一个表来保存距离,然后选择couponid和shopid匹配的min。