LINQ 分组问题

[lcaiyhh]

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分组，查询钱的总数，跟合计的数量

[L61551]

 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) 
                              };

[q107770540]

--测试数据准备:
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
			   

//LINQ语句:
void Main()
{
	var query=from a in this._As
	join b in this._Bs
	on a.AID equals b.AID into g
	select new 
	{
	  AID=a.AID,
	  Name=a.Name,
	  SumMoney=g.Sum(t=>t.Money),
	  Count=g.Count()
	};
	Console.WriteLine("AID\tName\tSumMoney\tCount");
	query.ToList().ForEach(q=>Console.WriteLine("{0}\t{1}\t\t{2}\t{3}",q.AID,q.Name,q.SumMoney,q.Count));
}
/*
AID	Name	SumMoney	Count
1	zhangsan		5	2
2	lisi		4	1
*/

[q107770540]

--LINQ语句对应的SQL语句:
SELECT [t0].[AID], [t0].[Name], (
    SELECT SUM([t1].[Money])
    FROM [_B] AS [t1]
    WHERE [t0].[AID] = [t1].[AID]
    ) AS [SumMoney], (
    SELECT COUNT(*)
    FROM [_B] AS [t2]
    WHERE [t0].[AID] = [t2].[AID]
    ) AS [Count]
FROM [_A] AS [t0]


--结果:

AID         Name                                               SumMoney    Count
----------- -------------------------------------------------- ----------- -----------
1           zhangsan                                           5           2
2           lisi                                               4           1

(2 row(s) affected)