这个查询如何写？

[Kingofcode]

表table1
字段 id,id1,xx,yy,zz

内容

1,101,a,b,c
1,102,c,d,e
2,201,d,f,c

我想得到的结果是

1,102
2,201


也就是说 我想得到groupby id 时 id1最大的数据集

[sp951]

select id,max(id) from table1 group by id order by id1

[Kingofcode]

大哥 这个我知道 这是linq版块我想知道的是linq的写法

[老毕]

试试：

var result = table.GroupBy(g => g.id).Select(s => new { id = s.Key, id1 = s.Max(m => m.id1) }).ToList();

[Kingofcode]

abbey 你这个不对
提示必须以select 结尾

[老毕]

哦，刚才的LINQ查询是凭感觉写的，没有把握。现在我用Northwind来写了个简单的测试，查找某个用户的订单中最大的Freight值，发现我之前写的LINQ查询是正确的。代码和结果如下：

using (NorthwindEntities db = new NorthwindEntities())
{
	var result = db.Orders.GroupBy(g => g.CustomerID).Select(s => new { cid = s.Key, max = s.Max(m => m.Freight) }).ToList();
	result.ForEach(e => Console.WriteLine("ID:{0},  Freight:{1}", e.cid, e.max));
}

对应的SQL查询：

select CustomerID, MAX(Freight) from Orders group by CustomerID

结果：

ID:ALFKI,  Freight:69.5300
ID:ANATR,  Freight:43.9000
ID:ANTON,  Freight:84.8400
ID:AROUT,  Freight:146.3200
ID:BERGS,  Freight:244.7900
ID:BLAUS,  Freight:53.8300
ID:BLONP,  Freight:156.6600
ID:BOLID,  Freight:97.0900
ID:BONAP,  Freight:350.6400
ID:BOTTM,  Freight:243.7300
ID:BSBEV,  Freight:123.8300
ID:CACTU,  Freight:31.5100
ID:CENTC,  Freight:3.2500
ID:CHOPS,  Freight:96.6500
ID:COMMI,  Freight:79.7000
ID:CONSH,  Freight:38.2400
ID:DRACD,  Freight:149.4700
ID:DUMON,  Freight:32.7600
ID:EASTC,  Freight:278.9600
ID:ERNSH,  Freight:789.9500
ID:FAMIA,  Freight:176.8100
ID:FOLIG,  Freight:487.3800
ID:FOLKO,  Freight:328.7400
ID:FRANK,  Freight:242.9500
ID:FRANR,  Freight:121.8200
ID:FRANS,  Freight:47.0900
ID:FURIB,  Freight:89.0000
ID:GALED,  Freight:18.6900
ID:GODOS,  Freight:175.3200
ID:GOURL,  Freight:210.8000
ID:GREAL,  Freight:719.7800
ID:GROSR,  Freight:66.2900
ID:HANAR,  Freight:193.3700
ID:HILAA,  Freight:210.1900
ID:HUNGC,  Freight:111.2900
ID:HUNGO,  Freight:603.5400
ID:ISLAT,  Freight:154.7200
ID:KOENE,  Freight:306.0700
ID:LACOR,  Freight:52.5100
ID:LAMAI,  Freight:249.9300
ID:LAUGB,  Freight:4.6500
ID:LAZYK,  Freight:11.9200
ID:LEHMS,  Freight:174.2000
ID:LETSS,  Freight:90.9700
ID:LILAS,  Freight:163.9700
ID:LINOD,  Freight:158.4400
ID:LONEP,  Freight:26.3100
ID:MAGAA,  Freight:155.9700
ID:MAISD,  Freight:155.6400
ID:MEREP,  Freight:379.1300
ID:MORGK,  Freight:127.3400
ID:NORTS,  Freight:32.0700
ID:OCEAN,  Freight:217.8600
ID:OLDWO,  Freight:257.6200
ID:OTTIK,  Freight:157.5500
ID:PERIC,  Freight:83.4900
ID:PICCO,  Freight:360.6300
ID:PRINI,  Freight:202.2400
ID:QUEDE,  Freight:99.2300
ID:QUEEN,  Freight:890.7800
ID:QUICK,  Freight:1007.6400
ID:RANCH,  Freight:90.8500
ID:RATTC,  Freight:708.9500
ID:REGGC,  Freight:89.9000
ID:RICAR,  Freight:160.5500
ID:RICSU,  Freight:232.4200
ID:ROMEY,  Freight:32.9900
ID:SANTG,  Freight:93.6300
ID:SAVEA,  Freight:830.7500
ID:SEVES,  Freight:288.4300
ID:SIMOB,  Freight:257.2600
ID:SPECD,  Freight:87.3800
ID:SPLIR,  Freight:195.6800
ID:SUPRD,  Freight:424.3000
ID:THEBI,  Freight:237.3400
ID:THECR,  Freight:94.8000
ID:TOMSP,  Freight:62.2200
ID:TORTU,  Freight:218.1500
ID:TRADH,  Freight:81.8300
ID:TRAIH,  Freight:37.6000
ID:VAFFE,  Freight:299.0900
ID:VICTE,  Freight:194.7200
ID:VINET,  Freight:32.3800
ID:WANDK,  Freight:105.6500
ID:WARTH,  Freight:180.4500
ID:WELLI,  Freight:55.2300
ID:WHITC,  Freight:606.1900
ID:WILMK,  Freight:38.1100
ID:WOLZA,  Freight:80.6500
请按任意键继续. . .

[老毕]

我怀疑你是不是把哪里的括号写掉了，呵呵。

[threenewbee]

var query = from x 
            in db.table1
            group x
                by x.id into g 
            select 
                new { 
                    id = g.id, 
                    id1 = g.Max(y => y.id1)
                };

[q107770540]

var result=db.Table1.GroupBy(t=>t.id).Select(t=>new{id=t.Key,id1=t.Max(s=>s.id1)});

[你的选择B]

[Quote=引用楼主 kingofcode 的回复:]
表table1
字段 id,id1,xx,yy,zz

内容

1,101,a,b,c
1,102,c,d,e
2,201,d,f,c

我想得到的结果是

1,102
2,201


也就是说 我想得到groupby id 时 id1最大的数据集
[/Quote]

var query = from u in entities.table1
                           group u by u.id into g
                           orderby g.Key
                           select new
                           {
                               g.Key,
                               Max = (from m in g
                                     where m.id1== g.Max(c => c.id1)
                                     select m).FirstOrDefault()
                           };
               foreach (var v in query)
               {
                   Console.WriteLine(v.Key + "  "+v.Max.CustomerID);
               }

[你的选择B]

lamda形式的：如下
顺便给排了个序

var s = entities.table1.GroupBy(c => c.id).OrderBy(c=>c.Key).Select(m => new { m.Key, Max= m.Max(g => g.id1) });

[luck_luke]

唉，怎么像看天书一样。。。。

[Kingofcode]

昨天的问题解决了 现在有个新的问题 如果我知道 任意一个id1的值 如何 查询得出 id及对应最大id1

例如 我知道id1为 101 获取 102 得出 结果 1,102


我不知道如何加分 各位如果需要重新开贴

[Kingofcode]

对应的sql 语句为

Select id,MAX(id1) from table1
where ID=(selext id from table1 where id1='101') group by id

结果为 1,102

[Kingofcode]

好了 上一个问题也搞定了 结贴