MySQL基本查询语法

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

排序自连接

排序自连接1

select
e.emp_no,s.salary,e.last_name,e.first_name
from 
salaries s join employees e 
on s.emp_no = e.emp_no
where e.emp_no = 
(
    select 
    s1.emp_no
    from
    salaries s1 join salaries s2 
    on s1.salary < s2.salary
    group by s1.salary
    having count(distinct s2.salary)=1
)

排序自连接2

注意: 在排序自连接中，如果有count并且不加group by 关键字只能查出来一条数据，如果加上group by关键字，那么可以查出来多条数据

错误示范

SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS t_rank
FROM salaries AS s1 join salaries AS s2
on s1.salary <= s2.salary
ORDER BY s1.salary DESC, s1.emp_no ASC

正确写法

SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS t_rank
FROM salaries AS s1 join salaries AS s2
on s1.salary <= s2.salary
GROUP BY s1.emp_no
ORDER BY s1.salary DESC, s1.emp_no ASC

赏

谢谢你请我吃糖果

微信