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)

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

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)

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

执行查询

select *,ids=dbo.f_cid(id) from os

转载：https://blog.csdn.net/slowlifes/article/details/7973904

SQL的经典算法

相关推荐