SQL语句问题

x_maizi 2017-11-14 03:23:35
现在有一个表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

...全文
130 9 点赞 打赏 收藏 举报
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
x_maizi 2017-11-15
感谢!linq最佳答案!
引用 8 楼 peng2739956 的回复:
代码太多,精简下

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
代码太多,精简下

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正解
引用 1 楼 sinat_28984567 的回复:
试试这样可以吗?
--测试数据
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

  • 打赏
  • 举报
回复
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

找个熟悉linq的人把上面的语句转成linq就可以,嘿嘿
  • 打赏
  • 举报
回复
二月十六 版主 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



  • 打赏
  • 举报
回复
相关推荐
发帖
MS-SQL Server
加入

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2017-11-14 03:23
社区公告
暂无公告