保留两位小数

sql server:

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

结果:2.18

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

结果:2.18

1
select **Round**(2.176544,2)  

结果:2.180000

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

查询重复数据

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

递归查询数据

1
2
3
4
5
6
7
8
9
10
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