SQL 的一些经典算法

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

 

转载于:https://www.cnblogs.com/miao817/archive/2012/09/12/2682089.html