常用SQL备忘

保留两位小数

sql server:

select **Convert**(decimal(18,2),2.176544)  

结果：2.18

select **CAST**(2.176544 as decimal(18,2))

结果：2.18

select **Round**(2.176544,2)　　

结果：2.180000

oracle:
select trunc(1.23856789,2)
round(m,n) 四舍五入
trunc(m,n) 不四舍五入，直接丢弃。

查询重复数据

select * from 表名 a 
where exists (select 1 from 表名 where 字段1=a.字段1 and 字段2=a.字段2 group by 字段1,字段2 having count(1)>1)

递归查询数据

with cte as
(
    select Id,Pid,Name from 表名
    where Id = 1
    union all
    select t.Id,t.Pid,t.Name from cte c inner join 表名 t
    on c.Id = t.Pid
)
select * from cte