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