---减集查未绑定关系的数据sql------tablename(主表)---t_con_table_name(关联表)
select * from tablename where tableid in(
select tableid from tablename
except
select tableid
from t_con_table_name where type = '3'
);
-----自关联查询-------查询当前id的所有父级信息以及自己信息
with RECURSIVE r as
(SELECT * FROM tablename where id='3718'
UNION ALL select t.* from tablename t join r on t.id=r.parentid)
select * from r
--------查询id为3718的所有子节点信息以及自己信息
WITH RECURSIVE r AS(
SELECT *
FROM
tablename
WHERE 1=1
and id='3718'
UNION ALL
SELECT
t.*
FROM
tablename t,
r
WHERE
t.parentid = r.id
)SELECT * FROM r
-----ROW_NUMBER() OVER函数的基本用法:语法:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
需求:根据部门分组,显示每个部门的工资等级
实现如下SQL脚本:
SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee ( where top=1-------如果加上top=1,可以实现去重复功能)