| 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) |