查询语句通过连接实现(因为是思考一个问题

 1 select UName,
 2 (select AVG(Grades) from UserInfo where GName = '语文') as '语文',
 3 (select AVG(Grades) from UserInfo where GName = '数学') as '数学',
 4 (select AVG(Grades) from UserInfo where GName = '英语') as '英语' 
 5 from UserInfo
 6 where UName = '小明'
 7 group by UName
 8 /*having GName = '语文' and GName = '数学' and GName = '英语'*/
 9 order by AVG(Grades)
10 go

金沙网址 1

金沙网址,  第一回代码修修改改如下,此时大概在只用一张表来查询(不能兑现上述目标结果,贴图只是为着验证下理念路径):

1 select UName,
2 (select AVG(Grades) from UserInfo where GName = '语文' and UName = tu.name) as '语文',
3 (select AVG(Grades) from UserInfo where GName = '数学' and UName = tu.name) as '数学',
4 (select AVG(Grades) from UserInfo where GName = '英语' and UName = tu.name) as '英语' 
5  from UserInfo as ui join T_User as tu
6  on ui.UName = tu.name
7 group by ui.UName,tu.name
8 go

  然后翻看资料也绝非头绪,就想只用一张表是或不是真正可行,要不用两张表试一下?接下去正是在原表A(实际为如下代码中
UserInfo表)基础上插入一个 学生id
的字段,然后新建一张学生表(T_User),具有五个字段:id、姓名,学生表的id相称表A里的上学的儿童id,查询语句通过连日完毕(因为是思索1个主题素材,就不怀想内外或然交叉连接的标题了),代码如下:

  成功!切磋并减轻大约花了3个多钟头,或许资质不行,不过会延续大力。从轻易动手,从萌新出发~

  编制程序萌新,因为碰到那样个SQL
查询的主题素材:在一张表A里有如下字段:学生姓名、学科名、学科战绩。写一条SQL
语句查出各科平均成绩并按学生姓名分组,按如下格式呈现:学生姓名|语文|数学|保加科钦语。一齐先遭逢的时候挺懵的,有记念只是却忘了怎么写了,接下去就是查阅资料并亲身上手测试是不是有效~

 

    

 

  运转测试如下:

相关文章