MySQL 8 Cookbook 原文書我在Internet下載不到 |
---|
只能找到中文版 |
今天測到一個書中的錯誤 |
描述到computed column |
其實應該跟sql server 的computed column是相同功能 |
我手上的書的語法是這樣的 |
ALTER TABLE employees ADD hire_date_year YEAR AS (YEAR(hire_date )) VIRTUAL ; |
只好上網找compute column的語法 |
修改為 |
alter table employees add hire_date_year char(4) GENERATED ALWAYS as (year(hire_date)) virtual; |
怪了~~ 寫書的人,怎麼會語法差這多~~ |
書中提了一個windows function的語法 |
---|
怎麼建怎麼失敗!! |
後來仔細看了一下我抄的語法 |
我猜可能rank這個字是保留字 |
select hire_date_year, salary, |
row_number() over( partition by hire_date_year order by salary desc) as rank, |
employees join salaries |
salaries.emp_no = employees.emp_no |
order by salary desc limit 10; |
果然.... |
我調整了語法如下: |
select hire_date_year, salary, |
row_number() over( partition by hire_date_year order by salary desc) as "rank" |
from employees join salaries |
on salaries.emp_no = employees.emp_no |
order by salary desc limit 10; |
+----------------+--------+------+ |
| hire_date_year | salary | rank | |
+----------------+--------+------+ |
| 1985 | 158220 | 1 | |
| 1985 | 157821 | 2 | |
| 1986 | 156286 | 1 | |
| 1985 | 155709 | 3 | |
| 1987 | 155513 | 1 | |
| 1985 | 155377 | 4 | |
| 1985 | 155190 | 5 | |
| 1985 | 154888 | 6 | |
| 1985 | 154885 | 7 | |
| 1985 | 154459 | 8 | |
+----------------+--------+------+ |
10 rows in set (5.96 sec) |
回看了書上的內容,它是用single qoute |
不過我看看它最後的結論 |
用CTE改了一下語法 |
把各年度最高年薪排在最上頭 |
with salary_rank as (select hire_date_year, salary, |
row_number() over( partition by hire_date_year order by salary desc) as salrank |
from employees join salaries |
on salaries.emp_no = employees.emp_no |
order by salary desc limit 10) |
select * from salary_rank order by salrank ; |
+----------------+--------+---------+ |
| hire_date_year | salary | salrank | |
+----------------+--------+---------+ |
| 1985 | 158220 | 1 | |
| 1986 | 156286 | 1 | |
| 1987 | 155513 | 1 | |
| 1985 | 157821 | 2 | |
| 1985 | 155709 | 3 | |
| 1985 | 155377 | 4 | |
| 1985 | 155190 | 5 | |
| 1985 | 154888 | 6 | |
| 1985 | 154885 | 7 | |
| 1985 | 154459 | 8 | |
+----------------+--------+---------+ |
10 rows in set (5.86 sec) |
windows function好像是8 的new feature |
不過保留字的問題 |
應該在其他的資料庫也一樣 |
只是我很少用到~~ |
第一个、最后一个和第n 个值 |
---|
照書上的語法,first_value的產出值我看得懂 |
但其他的,我就一頭霧水 |
後來我用CTE改成我的語法之後 |
看了之後,就恍然大悟 |
之所以有partition 語法 |
就是為了要分群 |
with sal_rank as ( |
SELECT hire_date_year , salary , RANK() OVER w AS 'Rank', |
FIRST_VALUE(salary) OVER w AS 'first', |
NTH_VALUE(salary, 3) OVER w AS 'third ', |
LAST_VALUE(salary) OVER w AS 'last' |
FROM employees join salaries ON salaries.emp_no=employees.emp_no |
WINDOW w AS (PARTITION BY hire_date_year ORDER BY salary DESC) |
ORDER BY salary DESC LIMIT 10 ) |
select * from sal_rank order by 3; |
+----------------+--------+------+--------+--------+--------+ |
| hire_date_year | salary | Rank | first | third | last | |
+----------------+--------+------+--------+--------+--------+ |
| 1985 | 158220 | 1 | 158220 | NULL | 158220 | |
| 1986 | 156286 | 1 | 156286 | NULL | 156286 | |
| 1987 | 155513 | 1 | 155513 | NULL | 155513 | |
| 1985 | 157821 | 2 | 158220 | NULL | 157821 | |
| 1985 | 155709 | 3 | 158220 | 155709 | 155709 | |
| 1985 | 155377 | 4 | 158220 | 155709 | 155377 | |
| 1985 | 155190 | 5 | 158220 | 155709 | 155190 | |
| 1985 | 154888 | 6 | 158220 | 155709 | 154888 | |
| 1985 | 154885 | 7 | 158220 | 155709 | 154885 | |
| 1985 | 154459 | 8 | 158220 | 155709 | 154459 | |
+----------------+--------+------+--------+--------+--------+ |
10 rows in set (9.92 sec) |