日期:2014-05-20  浏览次数:20864 次

LINQ 分组问题
A表
AID Name 
1 张三
2 李四

B表
BID Money AID
1 2 1
2 3 1
3 4 2


查询的结果

AID Name SumMoney Count
1 张三 5 2
2 李四 4 1

就是以AID分组,查询钱的总数,跟合计的数量

------解决方案--------------------
C# code


 var result = from a in data.A
                              from b in data.B
                              where a.AID == b.AID
                              group new { a = a, b = b } by new { a.AID, a.Name } into g
                              select new 
                              { 
                                  Aid = g.Key.AID,
                                  Name = g.Key.Name, 
                                  Count = g.Count(), 
                                  SumMoney = g.Sum(x => x.b.Money) 
                              };

------解决方案--------------------
SQL code

--测试数据准备:
CREATE TABLE _A
(
 AID int,
 Name nvarchar(50)
)

CREATE TABLE _B
(
 BID int,
 Money int,
 AID int
)

insert into _A SELECT 1,'zhangsan' union SELECT 2,'lisi'

insert into _B select 1,2,1 union 
               select 2,3,1 union 
           select 3,4,2