Linq TO SQL group by max 问题

[kk5595]

已经获取到了如下格式的结果集

country productname sum

USA ipad 100,000
USA mac air 200,000
CHINA dell 100,000


我想显示每个国家，销售金额最多的那个产品信息。如上表就显示

US mac air 200,000
CHINA DELL 100,000



-------------------------------------------------------------
下面是我的Linq 语句，productname 那列显示不对。。。


groupResultQuery 就是 上表结果集。



var finResult=from a in groupResultQuery
group a by new { country = a.country } into cqqq



select new
{
country = cqqq.Key.country,
pname=cqqq.Select(n=>n.productname),

total=cqqq.Max(n=>n.sum)

};


foreach (var f in finResult)
{
Write(f.country + "," + f.pname + "," + f.total);
}

[kk5595]

[Quote=引用 3 楼 的回复:]

C# code

var finResult=from a in groupResultQuery
group a by a.country into cqqq
let max=cqqq.OrderByDescending(x=>x.sum).First()
select new
{
country = cqqq.Key,
pname=max.productnam……
[/Quote]



我的是net 3.5
let max=cqqq.OrderByDescending(x=>x.sum).First() 后面.First()方法没有。。

[kk5595]

有了。。。刚才打错。

[kk5595]

pname列全为 ,System.Data.Linq.SqlClient.Implementation.ObjectMaterializer`1+<Conver

[huangwenquan123]

            List<Country> list = new List<Country>();
            list.Add(new Country() { country = "USA", productname = "ipad", sum = 100000 });
            list.Add(new Country() { country = "USA", productname = "mac air", sum = 200000 });
            list.Add(new Country() { country = "CHINA", productname = "dell", sum = 100000 });
            (from l in list group l by l.country into g select new { country = g.Key, productname = g.Max(x => x.productname), sum = g.Max(x => x.sum) }).ToList().ForEach(x => Console.WriteLine(x.country + "==" + x.productname + "==" + x.sum));

[q107770540]

  var finResult=from a in groupResultQuery
  group a by a.country into cqqq
  let max=cqqq.OrderByDescending(x=>x.sum).First()
  select new
  {
  country = cqqq.Key,
  pname=max.productname,
  total=max.sum
  };


  foreach (var f in finResult)
  {
  Write(f.country + "," + f.pname + "," + f.total);
  }

[wlf535944903]

这个只是取一个的

如果取前几个 稍微改动下大神Tim的即可

from a in Lists
							group a by a.Country into cqqq
							from max in cqqq.OrderByDescending(x => x.Sum).Take(1)
							select new
							{
								country = cqqq.Key,
								pname = max.Productname,
								total = max.Sum
							}

[wlf535944903]

再说下SQL

我希望的是

select * from 
(

select row_number() over(partition by country order by [sum] desc) as rowid,* from list

) as tmp

where rowid<2

但生成的是

SELECT [t1].[country], [t3].[productname] AS [pname], [t3].[sum] AS [total]
FROM (
    SELECT [t0].[country]
    FROM [List] AS [t0]
    GROUP BY [t0].[country]
    ) AS [t1]
CROSS APPLY (
    SELECT TOP (1) [t2].[productname], [t2].[sum]
    FROM [List] AS [t2]
    WHERE (([t1].[country] IS NULL) AND ([t2].[country] IS NULL)) OR (([t1].[country] IS NOT NULL) AND ([t2].[country] IS NOT NULL) AND ([t1].[country] = [t2].[country]))
    ORDER BY [t2].[sum] DESC
    ) AS [t3]
ORDER BY [t3].[sum] DESC

如何生成上面的SQL