常用SQL
来源:互联网 发布:网络信贷平台加盟 编辑:程序博客网 时间:2024/06/15 19:25
删除重复值
--1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select
*
from
people
where
peopleId
in
(
select
peopleId
from
people
group
by
peopleId
having
count
(peopleId) > 1)
--2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete
from
people
where
peopleId
in
(
select
peopleId
from
people
group
by
peopleId
having
count
(peopleId) > 1)
and
rowid
not
in
(
select
min
(rowid)
from
people
group
by
peopleId
having
count
(peopleId )>1)
--3、查找表中多余的重复记录(多个字段)
select
*
from
vitae a
where
(a.peopleId,a.seq)
in
(
select
peopleId,seq
from
vitae
group
by
peopleId,seq
having
count
(*) > 1)
--4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete
from
vitae a
where
(a.peopleId,a.seq)
in
(
select
peopleId,seq
from
vitae
group
by
peopleId,seq
having
count
(*) > 1)
and
rowid
not
in
(
select
min
(rowid)
from
vitae
group
by
peopleId,seq
having
count
(*)>1)
--5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select
*
from
vitae a
where
(a.peopleId,a.seq)
in
(
select
peopleId,seq
from
vitae
group
by
peopleId,seq
having
count
(*) > 1)
and
rowid
not
in
(
select
min
(rowid)
from
vitae
group
by
peopleId,seq
having
count
(*)>1)
--经典尝试 删除重复值
declare
@
table
table
(id
int
,
name
nvarchar(10))
insert
into
@
table
select
1,
'aa'
union
all
select
1,
'aa'
union
all
select
2,
'bb'
union
all
select
3,
'bb'
union
all
select
4,
'cc'
union
all
select
1,
'aa'
union
all
select
4,
'cc'
delete
a
from
(
select
id,
name
,rn = row_number() over (partition
by
id,
name
order
by
id)
from
@
table
) a
where
rn > 1
select
*
from
@
table
id
name
----------- ----------
1 aa
2 bb
3 bb
4 cc
(4 row(s) affected)
查询节点的函数
insert
into
tb
values
(
'001'
,
null
,
'广东省'
)
insert
into
tb
values
(
'002'
,
'001'
,
'广州市'
)
insert
into
tb
values
(
'003'
,
'001'
,
'深圳市'
)
insert
into
tb
values
(
'004'
,
'002'
,
'天河区'
)
insert
into
tb
values
(
'005'
,
'003'
,
'罗湖区'
)
insert
into
tb
values
(
'006'
,
'003'
,
'福田区'
)
insert
into
tb
values
(
'007'
,
'003'
,
'宝安区'
)
insert
into
tb
values
(
'008'
,
'007'
,
'西乡镇'
)
insert
into
tb
values
(
'009'
,
'007'
,
'龙华镇'
)
insert
into
tb
values
(
'010'
,
'007'
,
'松岗镇'
)
go
-------------创建方法
create
function
f_cid(@ID
varchar
(3))
returns
@t_level
table
(id
varchar
(3) ,
level
int
)
as
begin
declare
@
level
int
set
@
level
= 1
insert
into
@t_level
select
@id , @
level
while @@ROWCOUNT > 0
begin
set
@
level
= @
level
+ 1
insert
into
@t_level
select
a.id , @
level
from
tb a , @t_Level b
where
a.pid = b.id
and
b.
level
= @
level
- 1
end
return
END
GO
--------------使用方法
select
a.*
from
tb a , f_cid(
'001'
) b
where
a.id = b.id
order
by
a.id
--------------调用函数查询(广州市)及其所有子节点
select
a.*
from
tb a , f_cid(
'002'
) b
where
a.id = b.id
order
by
a.id
Exists,Datediff,Newid,
01
---两张关联表,删除主表中已经在副表中没有的信息
02
delete
from
table1
where
not
exists (
select
*
from
table2
where
table1.field1=table2.field1 )
03
04
---日程安排提前五分钟提醒
05
SQL:
select
*
from
日程安排
where
datediff(
'minute'
,f开始时间,getdate())>5
06
07
---随机取出10条数据
08
select
top
10 *
from
tablename
order
by
newid()
09
10
-- 类似有 month day year
11
select
*
from
table1
where
convert
(
varchar
,
date
,120)
like
'2006-04-01%'
12
--datediff
13
select
*
from
table1
where
datediff(
day
,
time
,
'2006-4-1'
)=0