当前位置:   article > 正文

SQL 的JOIN ON连表查询注意事项(查偏科学生的例子)_sql join on

sql join on

目录

1、关联查询时,别名随便起,极限情况下甚至中文也可以(但不提倡),注意在多个子查询内,对同一个表的同一个字段可以起不同的别名,在外面join on和where时也是互不影响的.

2.当left join或from后面跟的是子查询,即当查询内容是子查询(a和b)时,,如果给子查询中的字段取了别名(pid),那么在外层联表这两个子查询on和where时用a.就必须要使用别名来.出来了,必须要和各自子查询里面取的别名对的上 如果还是使用原字段名去on会导致查询时找不到字段,会报错:

[Err] 1054 - Unknown column '原字段名' in 'on clause'

3、表与表在使用ON连表必须严格用字段名,即使给字段取了别名,也不能用起的别名,否则会找不到详细的字段,报错:

Oracle 报错:ORA-00904 “p”."别名" : 标识符无效

MySQL报错:[Err] 1054 - Unknown column 'p.别名' in 'on clause'

​编辑

​编辑

4.关联表时注意先后关系,注意要先声明用到哪个表后,再使用其.点出里面的字段;比如到22行的时候s表还没有声明,就先使用的话,就会报异常

5.在连表查询时,若与关联的表有重复的字段,就不要用select*来直接获取所有字段的值了,需要一个一个写完整,并起别名,否则报错:

[Err] 1060 - Duplicate column name '重复的字段'​编辑

6、如果查询内容是子查询关联的结果,那么最外层一般直接select*,因为是连几张表或自己查,会指定好字段,外层不需要额外指定

7、select的时候可以from一个子查询()a,不是非要from一个表,同样left join也不是非要连上一个表,也可以连上一个子查询()b


三种写法速记

-- 两个子查询左连接  分别是语文成绩和数学成绩  条件是pid相等  条件偏科
-- 查询人员表 再左连接成绩表s1(语文)和成绩表s2(数学) 条件是偏科
-- from后面跟所有表,where后面些条件 把表关系用and连起来

现有:三张表 人员表、课程表、分数表;其中人员表和课程表是独表,成绩表为联表;

需求:查询语文分数大于数学分数(即偏科)的学生信息 

分析:显然,查每一个偏科的人,都需要从成绩表查此人的两条数据(语/数)出来进行分数比较,所以这里如果用到左连接,那么一定需要关联两次成绩表,on到同一个人的标识,即学号;再在最后定下偏科的where规则,即此人的语文成绩大于数学成绩完成查询;

然后,如果不使用左连接,在from时,也要在后面跟上两次成绩表,并取别名(s1/s2)详见博客最尾部展示了其他的几种查询方法

人员表(Person)id,name      课程表(Class)  id,name      成绩表(Score)  id,pid,cid,fen

1、关联查询时,别名随便起,极限情况下甚至中文也可以(但不提倡),注意在多个子查询内,对同一个表的同一个字段可以起不同的别名,在外面join的onwhere时也是互不影响的.

2.left joinfrom后面跟的是子查询中的字段若取了别名,那么在外层on联表或where写条件时,就必须要使用别名来点出各自的字段了,:

left joinfrom后面跟的是子查询,即当查询内容是子查询(a和b)时,如果给子查询中的字段取了别名(pid),那么在外层on联表或where写条件时,用子查询的字段就必须要使用别名来.出来,并且必须要和各自子查询里面取的别名对的上,因为会出现自己连接自己的情况,字段会重,如果还是使用原表字段名去on会导致查询时不能确定是哪个子查询里面的字段,会报错:

[Err] 1054 - Unknown column '原字段名' in 'on clause'

联表left join子查询on错误示例;

查询fromwhere条件错误示例; 

3、在使用ON连表必须严格用字段名,即使给字段取了别名,也不能用起的别名,否则会找不到详细的字段,报错:

Oracle 报错:ORA-00904p."别名" : 标识符无效

MySQL报错:[Err] 1054 - Unknown column 'p.别名' in 'on clause'

oracle数据库:

mysql数据库: 

4.关联表时注意先后关系,注意要先声明用到哪个表后,再使用其.点出里面的字段;比如到22行的时候s表还没有声明,就先使用的话,就会报异常

[Err] 1054 - Unknown column 's.cid' in 'on clause'

5.在连表查询时,若与关联的表有重复的字段,就不要用select*来直接获取所有字段的值了,需要一个一个写完整,并起别名,否则报错:

[Err] 1060 - Duplicate column name '重复的字段'

6、如果查询内容是子查询关联的结果,那么最外层一般直接select*,因为是连几张表或自己查,会指定好字段,外层不需要额外指定

7、select的时候可以from一个子查询()a,不是非要from一个表,同样left join也不是非要连上一个表,也可以连上一个子查询()b

select * from

(根据pid过滤出语文成绩) a

left ioin (根据pid过滤出语文成绩) b  on a.pid=b.pid

where a.fen>b.fen

附:

分析:查每一个偏科的人,都需要从成绩表查此人的两条数据(语/数)出来进行分数比较,所以这里如果用到左连接,那么一定需要关联两次成绩表,on到同一个人的标识,即学号;最后再来制定where规则:即此人的语文成绩大于数学成绩完成查询;

如果不使用左连接,在from时,也要在后面跟上两次成绩表,并取别名(s1/s2)后面第二种示例

一.这是几种方法中使用了左连接并最简洁的SQL:

SELECT * FROM person p 
LEFT JOIN    score s1 on s1.pid=p.id and s1.cid='101'
LEFT JOIN    score s2 on s2.pid=p.id and s2.cid='102'
where   s1.fen>s2.fen

如果觉得查询结果乱,可以将select* 改为别名

SELECT  p.id 学号,p.namee 名称,s1.fen 语文成绩,s2.fen 数学成绩 FROM person p 
LEFT JOIN    score s1 on s1.pid=p.id and s1.cid='101'
LEFT JOIN    score s2 on s2.pid=p.id and s2.cid='102'
where   s1.fen>s2.fen
            查询结果如下:

二。如果不使用左连接查询,在from时,也要在后面跟上两次成绩表,并取别名(s1/s2)

SELECT * FROM person p,score s1, score s2
WHERE p.id=s1.pid
AND p.id=s2.pid
AND s1.cid=(SELECT id FROM class WHERE namee='语文')
AND s2.cid='102'     ←←←(这里102可以如上一行一样,用内连接根据课程名查课程id)
AND s1.fen>s2.fen

 如果觉得查询结果乱,可以将select* 改为别名

SELECT p.id 学号,p.namee 名称,s1.fen 语文成绩,s2.fen 数学成绩 FROM person p,score s1, score s2
WHERE p.id=s1.pid
AND p.id=s2.pid
AND s1.cid=(SELECT id FROM class WHERE namee='语文')
AND s2.cid='102'     ←←←(这里102可以如上一行一样,用内连接根据课程名查课程id)
AND s1.fen>s2.fen

还发现了CSDN编辑页面CV到SQL查询页面的一个小bug:

---2022/11/8

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/很楠不爱3/article/detail/189026
推荐阅读
相关标签
  

闽ICP备14008679号