我不明白使用子查询SQL [英] I don't understand the use of subqueries SQL

问题描述

大家好。

我正在关注Proffesor J Widom的斯坦福数据库课程

在她的一个讲座中，她有这个问题，她在讲述子查询的重要性。 FROM子句



这是csv fomat中的学生表

sID，sName，GPA ，sizeHS
123，Amy，3.9，1000
234，Bob，3.6，1500
345 ，Craig，3.5，500
456，Doris，3.9，1000
567，Edward，2.9，2000 
678，Fay，3.8，200
789，Gary，3.4，800
987，海伦， 3.7，800
876，Irene，3.9，400
765，Jay，2.9，1500
 654，Amy，3.9，1000
543，Craig，3.4，2000

 选择 * 
 来自（选择 sID，sName，GPA，GPA *（sizeHS / 1000。 0 ） as  scaledGPA 
 来自学生）G 
 其中​​ abs（G.scaledGPA  -  GPA）>  1 。 0  

我尝试了什么：



我尝试了没有FROM部分中的子查询，如下所示：

 选择 sID，sName，GPA，GPA *（sizeHS / 1000。 0 ） as  scaledGPA 
 来自学生
  where  abs（scaledGPA-GPA）>  1 。 0  

两个查询都给出了同样的结果。那么Prof Widom查询的优势或区别是什么？

解决方案

子查询可以更轻松地提取你想要的内容，你可以将你需要的内容细分为查询步骤并将它们组合起来以获得结果。



您所说的示例可以在您说明的庄园中进行，因此它不是使用子查询的好示例。



多年前学习它的最好方法是访问97本书（我不记得确切的标题，我早就把它拿走了）。



如果你搜索例如，有很多教程丢失： Microsoft Access提示：子查询基础知识 [ ^ ]

这是一个关于子查询的好教程： SQL子查询 [ ^ ]

 SELECT sID，sName，GPA，sizeHS，scaledGPA FROM Student G 
 INNER JOIN（SELECT sID，GPA *（sizeHS / 1000.0）as scaledGPA FROM Student）GG ON G.sID = GG.sID 
 WHERE abs（GG.scaledGPA  -  G.GPA）> 1.0 

Hello everyone
I am following the Stanford databases course by Proffesor J Widom
In one of her lectures she had this query where she was telling the importance of subqueries in FROM clause

This is the Student table in csv fomat

"sID","sName","GPA","sizeHS"
"123","Amy","3.9","1000"
"234","Bob","3.6","1500"
"345","Craig","3.5","500"
"456","Doris","3.9","1000"
"567","Edward","2.9","2000"
"678","Fay","3.8","200"
"789","Gary","3.4","800"
"987","Helen","3.7","800"
"876","Irene","3.9","400"
"765","Jay","2.9","1500"
"654","Amy","3.9","1000"
"543","Craig","3.4","2000"

select *
from(select sID, sName, GPA, GPA*(sizeHS/1000.0) as scaledGPA 
from Student) G
where abs(G.scaledGPA - GPA) > 1.0

What I have tried:

I tried it without the subquery in the FROM part like this:

select sID, sName, GPA, GPA*(sizeHS/1000.0) as scaledGPA 
from Student
where abs(scaledGPA - GPA) > 1.0

Both the queries gave the same result. So what is the advantage or difference in the query Prof Widom made?

解决方案

Sub queries make extracting what you want easier and you can break down what you need into sub-query steps and combine them for a result.

The example you stated can be made in the manor you stated so it is not a good sample for the use of sub queries.

Years ago the best way to learn it was from an access 97 book (which I can't remember the exact title, and I have long since given it away).

There are lost of tutorials if you search e.g. : Microsoft Access tips: Subquery basics[^]

Here is a good tutorial on subqueries: SQL Sub Queries[^]

SELECT sID,sName,GPA,sizeHS,scaledGPA FROM Student G
INNER JOIN (SELECT sID, GPA*(sizeHS/1000.0) as scaledGPA FROM Student ) GG ON G.sID=GG.sID
WHERE abs(GG.scaledGPA - G.GPA) > 1.0

这篇关于我不明白使用子查询SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！