34,838
社区成员




var query=from m in List
group m by m.UID into g
let uid=g.Key
let max=g.OrderByDescending(t=>t.分数).FirstOrDefault()
let min=g.OrderBy(t=>t.分数).FirstOrDefault()
select new{uid,max分数=max.分数,max科目=max.科目,max考点=max.考点,min.分数,min.科目,min.考点};
query.Dump();
/*
uidΞΞ max分数ΞΞ max科目 max考点 分数ΞΞ 科目 考点
301 90 语文 考点B 80 数学 考点A
302 90 语文 考点B 60 英语 考点A
*/
结果:
UIDΞΞ 分数ΞΞ 考点 科目
301 90 考点B 语文
302 90 考点B 语文
301 80 考点A 数学
302 60 考点A 英语
//LINQ:
List<Model> List=new List<Model>();
List.Add(new Model { UID = 301, 科目 = "数学", 分数 = 80, 考点 = "考点A" });
List.Add(new Model { UID = 301, 科目 = "语文", 分数 = 90, 考点 = "考点B" });
List.Add(new Model { UID = 301, 科目 = "英语", 分数 = 85, 考点 = "考点A" });
List.Add(new Model { UID = 302, 科目 = "数学", 分数 = 80, 考点 = "考点A" });
List.Add(new Model { UID = 302, 科目 = "语文", 分数 = 90, 考点 = "考点B" });
List.Add(new Model { UID = 302, 科目 = "英语", 分数 = 90, 考点 = "考点A" });
List.Add(new Model { UID = 302, 科目 = "数学", 分数 = 70, 考点 = "考点A" });
List.Add(new Model { UID = 302, 科目 = "语文", 分数 = 90, 考点 = "考点B" });
List.Add(new Model { UID = 302, 科目 = "英语", 分数 = 60, 考点 = "考点A" });
var result = from u in List
group u by u.UID into b
select new
{
UID=b.Key,
分数=b.Max(t=>t.分数),
考点=b.Where(t=>t.UID==b.Key&&t.分数==b.Max(p=>p.分数)).Select(t=>t.考点).FirstOrDefault(),
科目=b.Where(t=>t.UID==b.Key&&t.分数==b.Max(p=>p.分数)).Select(t=>t.科目).FirstOrDefault()
};
var result2 = from u in List
group u by u.UID into b
select new
{
UID=b.Key,
分数=b.Min(t=>t.分数),
考点=b.Where(t=>t.UID==b.Key&&t.分数==b.Min(p=>p.分数)).Select(t=>t.考点).FirstOrDefault(),
科目=b.Where(t=>t.UID==b.Key&&t.分数==b.Min(p=>p.分数)).Select(t=>t.科目).FirstOrDefault()
};
var s=result.Union(result2).Dump();
//linqpad 执行
from a in TableA
group a by a.UserId into b
select new {
UserId=b.Key,
maxScore= b.Min(p => p.分数),
minScore= b.Max(p => p.分数)
} into c
join d in TableA on new{UserId=c.UserId,Score=c.maxScore} equals new{UserId=d.UserId,Score=d.分数} into dtemp
from dd in dtemp.DefaultIfEmpty()
join e in TableA on new{UserId=c.UserId,Score=c.minScore} equals new{UserId=e.UserId,Score=e.分数} into etemp
from ee in etemp.DefaultIfEmpty()
select new{
UserId=c.UserId,
maxScore= c.maxScore,
max科目=dd.科目==null?"":dd.科目,
max考点=dd.考点==null?"":dd.考点,
minScore= c.minScore,
min科目=ee.科目==null?"":ee.科目,
min考点=ee.考点==null?"":ee.考点
}
SELECT UserId,
最低分科目,
最低分,
最低分考点,
最高分科目,
最高分,
最高分考点
FROM (
SELECT a.UserId,
a.科目 AS '最低分科目',
a.分数 AS '最低分',
a.考点 AS '最低分考点',
c.科目 AS '最高分科目',
c.分数 AS '最高分',
c.考点 AS '最高分考点',
ROW_NUMBER() OVER(PARTITION BY a.UserId ORDER BY a.科目) AS rn
FROM #tab a
INNER JOIN (
SELECT UserId,
MAX(分数) AS max_sc,
MIN(分数) AS min_sc
FROM #tab
GROUP BY
UserId
) b
ON a.userid = b.userid
AND a.分数 = b.min_sc
INNER JOIN #tab c
ON c.userid = b.userid
AND c.分数 = b.max_sc
) a
WHERE rn = 1
找个熟悉linq的人把上面的语句转成linq就可以,嘿嘿--测试数据
if not object_id(N'Tempdb..#TableA') is null
drop table #TableA
Go
Create table #TableA([UserId] int,[科目] nvarchar(22),[分数] int,[考点] nvarchar(23))
Insert #TableA
select 301,N'数学',80,N'考点A' union all
select 301,N'语文',90,N'考点B' union all
select 301,N'英语',85,N'考点A' union all
select 302,N'数学',80,N'考点A' union all
select 302,N'语文',90,N'考点B' union all
select 302,N'英语',90,N'考点A' union all
select 302,N'数学',70,N'考点A' union all
select 302,N'语文',90,N'考点B' union all
select 302,N'英语',60,N'考点A'
Go
--测试数据结束
;WITH cte AS (
SELECT UserId ,
科目 ,
考点 ,
分数 ,
ROW_NUMBER() OVER ( PARTITION BY UserId, 分数 ORDER BY GETDATE() ) AS num
FROM #TableA
)
SELECT t1.userid ,
t1.max分数 ,
t2.科目 ,
t2.考点 ,
t1.min分数 ,
t3.科目 ,
t3.考点
FROM ( SELECT UserId ,
MAX(分数) AS max分数 ,
MIN(分数) AS min分数
FROM #TableA
GROUP BY UserId
) t1
JOIN cte t2 ON t2.UserId = t1.UserId
AND max分数 = t2.分数
AND t2.num = 1
JOIN cte t3 ON t3.UserId = t1.UserId
AND min分数 = t3.分数
AND t2.num = 1