使用联接和聚合函数的 LINQ 查询

发布于 2024-09-15 22:08:35 字数 747 浏览 4 评论 0原文

呃...我的 LINQ 突然变得更糟了！！

我有两个表，想要连接并在表上执行聚合函数。

首先，这可能吗，我假设是，其次，我如何处理聚合函数？作为同一个 LINQ 语句的一部分？

SQL 语句看起来有点像这样：

SELECT     t1.Col1, t1.Col2, t1.Col3, t2.Col10, 
                      t2.Col11, SUM(t2.Col4) AS TotalPurchases, COUNT(t1.Col5) AS ProductCount,t2.Col7, 
                      t2.Col6, t2.Col8, t2.Col9
FROM         t1 INNER JOIN
                      t2 ON t1.Col1 = t2.Col1 AND t1.Col5 = t2.Col5 AND 
                      t1.Col2 = t2.Col2
GROUP BY t1.Col1, t1.Col2, t1.Col3, t2.Col7, t2.Col6, 
                      t2.Col8, t2.Col9, t2.Col10, t2.Col11
HAVING      (t1.Col1 = @p1) AND (t1.Col2 = @p2) AND (t1.Col3 = @p3)

显然，“AS”部分可以忽略，我可以在控件中重命名它们。

任何提示、技巧、指示将不胜感激。

原文

Urgh...my LINQ has suddenly taken a turn for the worse!!

I have two tables and want to join and perform aggregate functions upon the tables.

Firstly, is this possible, I am assuming it is, and secondly, how do I handle the aggregate functions? As part of the same LINQ statement?

The SQL statement will look a bit like this:

SELECT     t1.Col1, t1.Col2, t1.Col3, t2.Col10, 
                      t2.Col11, SUM(t2.Col4) AS TotalPurchases, COUNT(t1.Col5) AS ProductCount,t2.Col7, 
                      t2.Col6, t2.Col8, t2.Col9
FROM         t1 INNER JOIN
                      t2 ON t1.Col1 = t2.Col1 AND t1.Col5 = t2.Col5 AND 
                      t1.Col2 = t2.Col2
GROUP BY t1.Col1, t1.Col2, t1.Col3, t2.Col7, t2.Col6, 
                      t2.Col8, t2.Col9, t2.Col10, t2.Col11
HAVING      (t1.Col1 = @p1) AND (t1.Col2 = @p2) AND (t1.Col3 = @p3)

Obviously, the 'AS' sections can be ignored, I can rename those in the controls.

Any hints, tips, pointers would be greatly appreciated.

分享到QQ

分享到微博

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

发布评论

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

兮子 2024-09-22 22:08:35

尝试执行以下操作，使用带有 into 关键字的联接帮助您实现任务，

var query = from t1 in prodDtcx.t1
            join t2 in prodDtcx.t2 on t1.ID equals t2.ID
            group t1.col1,t2.col2 by new {col1= t1.col1,col2=  t2.col2,col3 = t1.col2} into g
            where g.col3 >= @variable
            select new { Col1 = g.col1, Col2 = g.col2};

查看此帖子以了解更多详细信息：LINQhaving 子句和带有条件的分组依据

Try to do something as below make use of joins with the into keyword help you to achieve your taks

var query = from t1 in prodDtcx.t1
            join t2 in prodDtcx.t2 on t1.ID equals t2.ID
            group t1.col1,t2.col2 by new {col1= t1.col1,col2=  t2.col2,col3 = t1.col2} into g
            where g.col3 >= @variable
            select new { Col1 = g.col1, Col2 = g.col2};

Check this post for more detail : LINQ Having Clause and Group By with condition

回复收藏 0 原文

~没有更多了~