将 SQL 转换为 LINQ：滚动平均值

发布于 2024-11-03 06:38:17 字数 310 浏览 0 评论 0原文

我正在尝试为这个滚动平均 SQL 语句编写等效的 LINQ，但我正在努力解决如何使用同一个表 (BulbSales) 两次别名为 x 和 y 。

你能帮忙吗？

SELECT x.Id, AVG(y.Sales) moving_average
FROM BulbSales x, BulbSales y
WHERE x.Id>=6 AND x.Id BETWEEN y.Id AND y.Id+5 
GROUP BY x.Id
ORDER BY x.Id

原文

I am trying to write the equivalent LINQ for this rolling average SQL statement but I am struggling with how to use the same table (BulbSales) twice aliased as x and y.

Can you help?

SELECT x.Id, AVG(y.Sales) moving_average
FROM BulbSales x, BulbSales y
WHERE x.Id>=6 AND x.Id BETWEEN y.Id AND y.Id+5 
GROUP BY x.Id
ORDER BY x.Id

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

樱花落人离去 2024-11-10 06:38:17

如果这是 LINQ to SQL，您可以尝试：

var query = from x in context.BulbSales
            from y in context.BulbSales
            where x.Id >= 6 && x.Id >= y.Id && x.Id <= (y.Id + 5)
            group new { x.Id, y.Sales } by x.Id into g
            orderby g.Key
            select new { Id = g.Key, Average = g.Average(tuple => tuple.Sales) };

这会生成 SQL：

SELECT [t2].[Id], [t2].[value] AS [Average]
FROM (
    SELECT AVG([t1].[Sales]) AS [value], [t0].[Id]
    FROM [dbo].[BulbSales] AS [t0], [dbo].[BulbSales] AS [t1]
    WHERE ([t0].[Id] >= @p0) AND ([t0].[Id] >= [t1].[Id]) AND ([t0].[Id] <= ([t1].[Id] + @p1))
    GROUP BY [t0].[Id]
    ) AS [t2]
ORDER BY [t2].[Id]

If this is LINQ to SQL, you can try:

var query = from x in context.BulbSales
            from y in context.BulbSales
            where x.Id >= 6 && x.Id >= y.Id && x.Id <= (y.Id + 5)
            group new { x.Id, y.Sales } by x.Id into g
            orderby g.Key
            select new { Id = g.Key, Average = g.Average(tuple => tuple.Sales) };

This produces the SQL:

SELECT [t2].[Id], [t2].[value] AS [Average]
FROM (
    SELECT AVG([t1].[Sales]) AS [value], [t0].[Id]
    FROM [dbo].[BulbSales] AS [t0], [dbo].[BulbSales] AS [t1]
    WHERE ([t0].[Id] >= @p0) AND ([t0].[Id] >= [t1].[Id]) AND ([t0].[Id] <= ([t1].[Id] + @p1))
    GROUP BY [t0].[Id]
    ) AS [t2]
ORDER BY [t2].[Id]

回复收藏 0 原文

~没有更多了~