SQL 的一些经典算法
1 SQL分页查询，每页10个数据，取第三页
A） 如果有id列
select top(10) * from Spider_Hotel
where Spider_Hotel.HotelId not in
( select top (10*2) Spider_Hotel.HotelId from Spider_Hotel)
B） 如果没有id列
select top (10) * from
(select ROW_NUMBER() over (order by Name) as Row,* from Spider_Hotel)
a
where Row>10*2
C） 如果有id列
select top (10) * from Spider_Hotel
where HotelId >(select max(HotelId)
from (select top (10*2) HotelId from Spider_Hotel order by HotelId) as a
)
2，数据库删除重复记录。
a， 如果有id列 a,b,c 重复
Delete from table where id not in (select max (id) from table group a,b,c)
b, 如果没有id列a,b,c 重复 .用row_number() over 函数，制造个Id列
With Tab as
（
Select row_number() over (order by a) as Row,a,b,c from table
）
Delete from Tab
Where Row not in (selete max(Row) from Tab group a,b,c)
3，Count函数。
SELECT COUNT(*),COUNT(FNumber),COUNT(FName) FROM T_Employee
COUNT(*)统计的是结果集的总条数，而
COUNT(FName)统计的则是除了结果集中FName 不为空值（也就是不等于NULL）的记录的总
条数，如果Fname有为空COUNT(*) 不等于COUNT(FNumber)
4，求一公司年龄在20-30,40-60岁这两个年龄段的人数,行列转换
select SUM(Young) as Young ,SUM(Old) as Old from (
select
Users_age,
sum(case when Users_age between 1 and 3 then 1 else 0 end) as Young,
sum(case when Users_age between 4 and 6then 1 else 0 end) as Old
from Users
group by Users_age) as s
// 显示名字，不显示人数，我感觉数据库语句是一个一个判断下去的
select
years,
case when age between 20 and 30 then name else '---' end as [20-30],
case when age between 30 and 40 then name else '---' end as [30-40],
case when age between 40 and 50 then name else '---' end as [40-50]
from info
group by years,name,age
5,连续三天的记录 （查询连续三天抽取同一家酒店同一入住时间的免费房的人员记录）
select Member_Code from
dbo.Member a where
exists
(
select * from Member b
where b.Register_Date = DATEADD(D,1,a.Register_Date) and a.Member_Code=b.Member_Code
)
and
exists
(
select * from Member b
where b.Register_Date = DATEADD(D,2,a.Register_Date) and a.Member_Code=b.Member_Code
)
group by Member_Code
6,查询表结构 （查询一个数据库所有的数据库表 字段类型 字段大小）
--查询表结构
select Dense_Rank() over (order by o.name) as table_order, ROW_NUMBER() over (partition by o.id order by o.name) as column_order,
o.name as table_name, c.name as column_name, t.name+'['+ convert(varchar, c.[length])+']' as column_type
from sysobjects o inner join syscolumns c
inner join systypes t on c.xusertype=t.xusertype
on o.id=c.id
where o.xtype='U'
order by o.name
--查询数据库表
select * from dbo.sysobjects
where sysobjects.xtype ='U'
--查询数据库列
select * from syscolumns
where id=21575115
7,截止今天的销售额 （一个超市每天都有营业额。列出每天的营业额和截止到每天的总营业额。）
（比如，第一天 营业额 10 总营业额 10，第二天 营业额20 总营业额 30；第二天 营业额 10 总营业额 40）
select dates,(select SUM(moneys) from Table as TT
where TT.dates <= T.dates) from Table as T
8,全局临时表，防止用户两地登录。
--全局临时表防止用户地登录，但是如果用户量大，这很可能消耗大量资源。
--用户登录，就根据用户Id创建个临时表，如果用户账户异地登录，就会发现
--这张临时表已经存在，返回，提示登录不成功。用户退出系统后，临时表自动消失。
alter proc gp_findtemptable
@v_userid varchar(6),@i_out int output
as
declare @v_sql varchar(100)
if object_id('tempdb.dbo.##'+@v_userid) is null
begin
set @v_sql = 'create table ##'+@v_userid+'(userid varchar(6))'
exec (@v_sql)
set @i_out = 0
end
else
set @i_out = 1
declare @I int
exec gp_findtemptable 'T00001',@I output
select @I
9,查找指定节点的所有子节点的子节点。
create table os(id int,parentid int,desn varchar(10))
--自己不能是自己的节点，否则进入死循环。
alter function f_cid(@id int)
returns varchar(500)
as
begin
declare @t table(id int,desn varchar(10),parentid int,lev int)
declare @lev int
set @lev=1
insert into @t select *,@lev from os where id=@id
while(@@rowcount>0)
begin
set @lev=@lev+1
insert into @t select a.*,@lev from os a,@t b
where a.parentid=b.id and b.lev=@lev-1
end
declare @cids varchar(500)
select @cids=isnull(@cids+',','')+ltrim(id) from @t order by lev
return @cids
end
go
执行查询
select *,ids=dbo.f_cid(id) from os
