34,838
社区成员
发帖
与我相关
我的任务
分享SQL语句问题
现在有一个表TableA设计如下字段（UserId、科目、分数、考点）；存储班级所有学生的所有科目，成绩、考点信息；
比如：
UserId、科目、分数、考点
301、数学、80、考点A
301、语文、90、考点B
301、英语、85、考点A
302、数学、80、考点A
302、语文、90、考点B
302、英语、90、考点A
302、数学、70、考点A
302、语文、90、考点B
302、英语、60、考点A
1、写SQL语句，查询每个学生的，userid、最高分、最高分的科目、最高分的考点、最低分、最低分科目、最低分考点（分数相同的随机取第一条）
如结果集：
301 、90、语文、考点B、80、数学、考点A
302、90、英语、考点A、80、语文、考点A
2、使用linq实现题1
比如：
UserId、科目、分数、考点
301、数学、80、考点A
301、语文、90、考点B
301、英语、85、考点A
302、数学、80、考点A
302、语文、90、考点B
302、英语、90、考点A
302、数学、70、考点A
302、语文、90、考点B
302、英语、60、考点A
1、写SQL语句，查询每个学生的，userid、最高分、最高分的科目、最高分的考点、最低分、最低分科目、最低分考点（分数相同的随机取第一条）
如结果集：
301 、90、语文、考点B、80、数学、考点A
302、90、英语、考点A、80、语文、考点A
2、使用linq实现题1
...全文
请发表友善的回复…
发表回复
x_maizi 2017-11-15
- 打赏
- 举报
感谢！linq最佳答案！
peng2739956 2017-11-15
- 打赏
- 举报
代码太多，精简下
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
*/
peng2739956 2017-11-15
- 打赏
- 举报
结果：
UIDΞΞ 分数ΞΞ 考点 科目
301 90 考点B 语文
302 90 考点B 语文
301 80 考点A 数学
302 60 考点A 英语
peng2739956 2017-11-15
- 打赏
- 举报
//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 执行
x_maizi 2017-11-14
- 打赏
- 举报
感谢，SQL正解
x_maizi 2017-11-14
- 打赏
- 举报
linq的写法，但是下面的语句遇到重复；
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.考点
}
示例数据
听雨停了 2017-11-14
- 打赏
- 举报
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
二月十六 版主 2017-11-14
- 打赏
- 举报
linq不太熟悉……
二月十六 版主 2017-11-14
- 打赏
- 举报
试试这样可以吗？
--测试数据
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
