描述
有一個(gè)員工表employees簡(jiǎn)況如下:
有一個(gè)薪水表salaries簡(jiǎn)況如下:
請(qǐng)你查找薪水排名第二多的員工編號(hào)emp_no、薪水salary愿题、last_name以及first_name饮亏，不能使用order by完成搅方，以上例子輸出為:
（溫馨提示:sqlite通過(guò)的代碼不一定能通過(guò)mysql拴鸵，因?yàn)镾QL語(yǔ)法規(guī)定间聊，使用聚合函數(shù)時(shí)笆焰，select子句中一般只能存在以下三種元素：常數(shù)蓬豁、聚合函數(shù)谆奥，group by 指定的列名眼坏。如果使用非group by的列名，sqlite的結(jié)果和mysql 可能不一樣)
示例1
輸入：
<pre data-v-94fd4448="" style="margin: 0px 12px 0px 0px; padding: 0px; font-weight: 400; font-size: 14px; position: relative; border: none; background: initial; word-break: break-word; font-family: Courier, "Courier New", monospace; white-space: pre-wrap; overflow-wrap: break-word;">drop table if exists employees ;
drop table if exists salaries ;
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');</pre>
我的錯(cuò)誤答案?jìng)?/h1>
one
select s.emp_no,s.salary,e.last_name,e.first_name
from salaries s
inner join employees e
on s.emp_no = e.emp_no
where s.salary < max(s.salary) limit 1,1
two
select s.emp_no,max(s.salary),e.last_name,e.first_name
from salaries s
inner join employees e
on s.emp_no = e.emp_no
where s.salary < max(s.salary)
three
select s.emp_no,max(s.salary),e.last_name,e.first_name
from salaries s
inner join employees e
on s.emp_no = e.emp_no
where s.salary < (select max(s.salary) from salaries)
問(wèn)題:
https://blog.csdn.net/win7system/article/details/77972577
一般在書(shū)寫(xiě)sql的是時(shí)候很多時(shí)候會(huì)誤將聚合函數(shù)放到where后面作為條件查詢酸些，事實(shí)證明這樣是無(wú)法執(zhí)行的宰译，執(zhí)行會(huì)報(bào)【此處不允許使用聚合函數(shù)】異常。為什么會(huì)報(bào)異常呢魄懂？其原因很簡(jiǎn)單：
- where后不放聚合函數(shù), where 子句的作用是在對(duì)查詢結(jié)果進(jìn)行分組[前]沿侈，將不符合where條件的行去掉，即在分組之前過(guò)濾數(shù)據(jù)市栗，[條件中不能包含聚組函數(shù)]缀拭，使用where條件顯示特定的行咳短。
- having后可以放聚合函數(shù), having 子句的作用是篩選滿足條件的組，即在分組之后過(guò)濾數(shù)據(jù)蛛淋，條件中經(jīng)常包含聚組函數(shù)咙好，使用having 條件顯示特定的組，也可以使用多個(gè)分組標(biāo)準(zhǔn)進(jìn)行分組褐荷。
聚合函數(shù)只能在以下位置作為表達(dá)式使用：
[select 語(yǔ)句的選擇列表（子查詢或外部查詢）]勾效；
compute 或 compute by 子句；
having 子句叛甫；
正確答案:
SELECT e.emp_no, s.salary, e.last_name, e.first_name
FROM employees AS e
INNER JOIN salaries AS s
ON e.emp_no=s.emp_no
WHERE salary=(SELECT max(salary)
FROM salaries
WHERE salary<(SELECT MAX(salary)
FROM salaries));
