萌新学mysql，完全照着b站视频搞的，请教下这段话为啥会报错呢？

ChouChou

2021-01-02T08:02:47+00:00

[url]https://www.bilibili.com/video/BV12b411K7Zu?p=98[/url]
学的是这个第三题

SELECT * FROM `departments` d
WHERE `department_id` = (
SELECT `department_id` FROM `employees`
GROUP BY `department_id`
ORDER BY AVG(`salary`)
LIMIT 1
)
INNER JOIN

(SELECT AVG(salary) ,`department_id` FROM `employees`
GROUP BY `department_id`) s0
ON s0.`department_id`=d.`department_id`

报错信息
1 queries executed, 0 success, 1 errors, 0 warnings

查询：select * from `departments` d where `department_id` = ( select `department_id` from `employees` group by `department_id` order b...

错误代码： 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'inner join

(select avg(salary) ,`department_id` from `employees`
GROUP BY `d' at line 8

Joker᲼

department_id后面应该是in吧

Tac Pack~🍩

这种sql会被dba打死

Sanchin

你有个缩写的表名“d”是哪个表？

ChouChou

[quote][pid=483650367,25023910,1]Reply[/pid] Post by [uid=42988055]我的天唉201806[/uid] (2021-01-08 16:09):
department_id后面应该是in吧[/quote]join之前的部分独立运行是可以查出来的
就是join之后就不行了

ChouChou

[quote][pid=483650979,25023910,1]Reply[/pid] Post by [uid=1358346]jayse109[/uid] (2021-01-08 16:11):
你有个缩写的表名“d”是哪个表？[/quote]就是department表

𝕮𝖍𝖗͢͢͢𝖎𝖘

前面整体括起来起个名字 s1

AbsoluteOni

s0.`department_id`=d.`department_id`

第一步:from
第二步:join on (无则略过,内联,左联,右联)
第三步:where(大多数情况都有)
第四步:group by (用来分组)
第五步:select (虽然他是第一个写的但是他的执行顺序却在特别靠后)
第六步:having(分组以后就不能用where,别问我为什么.where都执行完了还怎么执行!!!所以出现了一个代替where的方法而且里面可以写函数)
第七步:order by (用来排序,可以同时使用多个字段)
第八步:limit (取出条数后面跟一个数字是取前多少条跟两个数字就是从第多少条开始,取多少条)

Tac Pack~🍩

看错看错

KaoticDeath

把where拿到最后，要先join表在where筛选

KAPA_COPO

[quote][pid=483651158,25023910,1]Reply[/pid] Post by [uid=60340698]Charles-Martel[/uid] (2021-01-08 16:12):

就是department表[/quote]但你没在sql上定义

Shanti

inner join 不能放在where 语句内
SELECT * FROM `departments` d
INNER JOIN

(SELECT AVG(salary) ,`department_id` FROM `employees`
GROUP BY `department_id`) s0
ON s0.`department_id`=d.`department_id`

WHERE `department_id` = (
SELECT `department_id` FROM `employees`
GROUP BY `department_id`
ORDER BY AVG(`salary`)
LIMIT 1
)

TTKxMason

[quote][pid=483650367,25023910,1]Reply[/pid] Post by [uid=42988055]我的天唉201806[/uid] (2021-01-08 16:09):

department_id后面应该是in吧[/quote]里面limit 1 了，这边不会报错的。

SiroNeko

前面的表没起别名，然后你这个d是哪张表呢。。人家肯定不认识你啊
SELECT * FROM `departments`
WHERE `department_id` = (
SELECT `department_id` FROM `employees`
GROUP BY `department_id`
ORDER BY AVG(`salary`)
LIMIT 1
)
INNER JOIN

(SELECT AVG(salary) ,`department_id` FROM `employees`
GROUP BY `department_id`) s0
ON s0.`department_id`=[d.`department_id`]

Herpatov

有一说一萌新写的好复杂啊[s:ac:冷]然后前面的人说了WHERE肯定是放在最后的

watersocks

你先百度一下inner join怎么用

ᴊᴀʏ✞

join要放到where上面吧

ChouChou

[quote][pid=483652121,25023910,1]Reply[/pid] Post by [uid=60741274]低吟浅唱233[/uid] (2021-01-08 16:15):

但你没在sql上定义[/quote]我自己的表定义了，不知道为啥粘贴过来的时候少了一个字母，已经加上了

Stawwp

你这段代码里第一行有个d 但是报错里没有？

Ansi Fatu

你这sql写的什么玩意[s:ac:喷]

萌新学做饭，昨天不能吃的大盘鸡之后，今天的菜中午能吃了萌新，求ak配置和各种技巧 [萌新问题] 杀90+人的到底是大佬还是..？萌新求收留 [萌之厨房] [爱妻便当]今天的便当比较清淡。。 [萌新求教] 这种又小又厚的牛排怎么做啊 [萌新求教]炒年糕没有韩式辣酱可以用什么代替 [萌新求问]为什么说有些仓鼠粮是渣粮 [萌新求问]家里有一只猫，再接回来一只有什么要注意的吗？

返回主页