sql经典语句大全

SQL Server提供了大量的函数, 
但是在一些常见的如, 
字符串拆分, 
字符提取,过滤等没有对应的处理, 
本帖主要收集一些常见的函数, 
整理如下: 
------------------------------ 
http://topic.csdn.net/u/20080306/23/d3c100f2-cda1-4efa-927d-f1f7968884ce.html 
/* 
功能:拆分字符串. 
作者:..... 
*/ 

http://topic.csdn.net/u/20080724/11/dacb530f-62ba-4417-a11a-fe2dee2172b4.html 
/* 
功能:根据身份证号码取得此CID所在省份 
作者:happyflystone 
*/ 

http://topic.csdn.net/u/20080713/00/77925c47-b7fa-4c1b-b307-0328e74a1c09.html 
/* 
功能:提取数字 
功能:提取英文 
功能:提取中文 
功能:过滤重复字符 
功能:过滤重复字符2 

作者:wzy_love_sly 
*/ 

MS SQL Server vs Oracle函数 
http://topic.csdn.net/u/20071022/09/477d57b8-1127-4159-8975-2055343c624f.html 

sql 函数 md5 
http://topic.csdn.net/u/20080619/09/4fb22d1b-f88e-487e-8b74-cf394263fc40.html 

求sql取字符串的首字母的函数 
http://topic.csdn.net/u/20080623/11/22a3f568-d37e-4bf5-9c13-64171066f582.html 

形如1,2,3,4,5,6的字符串转换成行 
http://topic.csdn.net/u/20080621/08/3397be02-47cd-40c3-9834-600cbb84c0bf.html 

小写金额转换成大写 
http://topic.csdn.net/u/20080118/11/80ff7847-20d9-45d8-9ed6-79933ced2cf8.html 

整数转换成二进制的函数 
http://topic.csdn.net/u/20071108/22/1650e0ef-3c14-4710-b2db-075f1e0badf3.html 

SQLserver中实现一个函数，能够将10进制转化为36进制 
http://topic.csdn.net/u/20070612/16/05e8b690-0818-479d-a34d-ee6258da4027.html 

http://blog.csdn.net/roy_88/archive/2007/11/13/1882106.aspx 
/* 
功能:十进制/十八进制的互转换(此方法应用于所有进制与10进制的转换) 
作者:roy_88 
*/ 

http://topic.csdn.net/u/20080715/10/548c6e12-731f-4e54-ac13-d05325068032.html 
/* 
功能:整数转换成二进制的函数 
作者:happyflystone 
*/ 

SQL Server里面有十六进制转化为十进制的函数吗 
http://topic.csdn.net/u/20070116/10/0c7d9ec1-acaa-4918-86cd-51516f7f517f.html 

SQL2000加密解密函数 
http://topic.csdn.net/t/20041202/20/3610469.html 

想通过一个函数或sql语句，随机生成任意6个字母组成的字符 
http://topic.csdn.net/u/20070627/17/ea906673-9821-46c6-b351-ab98a0b3d2c4.html 

求一个sql函数：计算时间差（除周六和周日外）的天数 
http://topic.csdn.net/u/20070322/12/2a8d5fcf-2d5b-49e1-b58a-c7277e15ee95.html 

Ip算法匹配 
http://topic.csdn.net/u/20080711/15/66639249-52d9-40c6-8f5c-131e49c1a6cf.html 
http://topic.csdn.net/u/20080801/11/506af00d-b882-41a2-99d9-4dc6927101fd.html 

http://blog.csdn.net/dobear_0922/archive/2007/12/18/1947219.aspx 
/* 
功能:SQL日历函数. 
作者:dobear_0922 
*/ 

http://topic.csdn.net/u/20080505/20/d2dffbbe-6d6b-41cf-b29a-41149540eafa.html 
/* 
功能:两个支持text字段内文字替换的存储过程 
作者:realgz 
*/ 

MS SQL Server 2000 中文模糊搜寻存储过程及函数 
http://www.cppfans.com/d_vcl_files/mssqlxp_chn.asp 


http://topic.csdn.net/t/20050419/10/3948336.html 
/* 
功能:全角/半角转换  
*/ 

http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html 
/* 
功能:合并与拆分 
整理者:roy_88 
*/ 

http://topic.csdn.net/u/20080605/11/55c273aa-2206-4ded-b6f6-508a7a755c6a.html 
/* 
功能:自定义函数，用户可以调用这个函数判断指定的字符串是否符合正则表达式的规则. 
作者:ranzj 
*/ 

http://topic.csdn.net/u/20080804/20/09f3d937-d0b9-4892-ab01-fae3f38f0525.html 
/* 
功能:查找某个字符串第N次出现的位置 
作者:..... 
*/

T-SQL: 15 个与日期时间相关的精典语句函数

T-SQL: 15 个与日期时间相关的自定义函数(UDF),周日作为周的最后一天,均不受 @@DateFirst、语言版本影响
都是从老文章里收集或提炼出来的!
提示:
(@@Datefirst + datepart(weekday,@Date)) % 7 判断周几是最保险的! 与 @@DateFirst 无关,与语言版本无关
@@DateFirst 可能会导致 datepart(weekday,@Date) 不一样!
无论 @@DateFirst 等于几,无论是什么语言版本的 SQL Server 下面永远恒成立! (@@Datefirst + datepart(weekday,@Date)): 2、3、4、5、6、0、1 分别代表 周一 到 周日
-- */
create function udf_GetAge(@StartDate datetime,@EndDate datetime)
returns integer
-- 返回精确年龄 select dbo.udf_GetAge(‘1949-10-01‘,getdate())
begin
return datediff(year,@StartDate,@EndDate)
       - case when datediff(day,dateadd(year,datediff(year,@StartDate,@EndDate),@StartDate),@EndDate) >= 0
                   then 0
              else
                   1
         end
end
go

create function udf_DaysOfYearByDate(@Date datetime)
RETURNS integer
-- 返回年的天数 可判断 平(365)、润(366) 年
begin
return datediff(day,dateadd(year,datediff(year,0,@Date),0),dateadd(year,datediff(year,0,@Date )+1,0))
end
go
create function udf_DaysOfYear(@Year integer)
RETURNS integer
-- 返回年的天数 可判断 平(365)、润(366) 年
begin
return datediff(day,dateadd(year,@year - year(0),0),dateadd(year,@year - year(0) + 1,0))
end
go

create function udf_HalfDay(@Date datetime)
returns datetime
-- 返回 @Date 是 上午 返回 @Date 的零点,@Date 是 下午 返回 @Date 的十二点
as
begin
return case when datepart(hour,@Date) 
go
create function udf_WeekDiff(@StartDate datetime,@EndDate datetime)
returns integer
-- 返回 [@StartDate , @EndDate] 之间周数 周日是当周的最后一天
begin
return datediff(week,@StartDate,@EndDate) -- + 1
       + case when (@@Datefirst + datepart(weekday,@StartDate)) % 7 = 1
                   then 1
              else
                   0
         end
       - case when (@@Datefirst + datepart(weekday,@EndDate)) % 7 = 1
                   then 1
              else 0
         end
end
go

create function udf_WeekOfMonth(@Date datetime)
-- 返回 @Date 是所在月的第几周 周日是当周的最后一天
returns integer
as
begin
return datediff(week
                ,case when (@@Datefirst + datepart(weekday,dateadd(month,datediff(month,0,@Date),0))) % 7 = 1
                           then dateadd(month,datediff(month,0,@Date),0) - 1
                      else
                           dateadd(month,datediff(month,0,@Date),0)
                      end
                ,case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1
                           then @Date-1
                      else @Date
                 end
               ) + 1
end
go

create function udf_WeekOfQuarter(@Date datetime)
-- 返回 @Date 是所在季度的第几周 周日是当周的最后一天
returns int
as
begin
return datediff(week
                ,case when (@@Datefirst + datepart(weekday,dateadd(Quarter,datediff(Quarter,0,@Date),0))) % 7 = 1
                           then dateadd(Quarter,datediff(Quarter,0,@Date),0) - 1
                      else
                           dateadd(Quarter,datediff(Quarter,0,@Date),0)
                 end
                ,case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1
                           then @Date - 1
                      else
                           @Date
                 end
               ) + 1
end
go

create function udf_WeekOfYear(@Date datetime)
-- 返回 @Date 是所在年的第几周 周日是当周的最后一天
returns int
as
begin
return datediff(week
                ,case when (@@Datefirst + datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@Date),0))))) % 7 = 1
                           then dateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@Date),0))))
                      else
                           dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@Date),0))) --date 所在年的第一天 即: 一月一号
                 end
                ,case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1
                           then dateadd(day,-1,@Date)
                      else
                           @Date
                 end
               ) + 1
end
go

create function udf_WeekDay(@ int,@Date datetime)
returns datetime
-- 返回 @Date 映射到 所在周的其他天 周日是当周的最后一天
begin
/*
--周日算作(上一)周的最后一天 
当 @ = 7 代表将 @Date 映射到 所在周的星期日
可用于按周汇总 Group by,均支持跨年跨月数据
*/
return dateadd(day
               ,case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 0 --周六
                          then case when @ between 1 and 6
                                         then @ - 6
                                    else
                                         1
                               end
                     when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1 --周日(七)
                          then case when @ between 1 and 6
                                         then @ - 7
                                    else
                                         0
                               end
                     when (@@Datefirst + datepart(weekday,@Date)) % 7 between 2 and 6 --周一至周五
                          then case when @ between 1 and 6
                                         then @ + 1 - (@@Datefirst + datepart(weekday,@Date)) % 7
                                    else
                                         8 - (@@Datefirst + datepart(weekday,@Date)) % 7
                               end
                end
               ,@Date)
end
go

create function udf_WeekdayDiff(@Weekday integer,@StartDate datetime,@EndDate datetime)
returns integer
-- -- 返回 [@StartDate , @EndDate] 之间周一 到 周日的个数 周日是当周的最后一天
begin
-- @Weekday: 1: Monday , ... ,7: Sunday
return datediff(week,@StartDate,@EndDate)
       + case when (@@Datefirst + datepart(weekday,@StartDate)) % 7
                   + case when (@@Datefirst + datepart(weekday,@StartDate)) % 7 = 0
                               then 7
                          else
                               0
                     end > @Weekday % 7 + 1
                   then 0
              else 1
         end
       - case when (@@Datefirst + datepart(weekday,@EndDate)) % 7
                   + case when (@@Datefirst + datepart(weekday,@EndDate)) % 7 = 0
                               then 7
                          else 0
                     end >= @Weekday % 7 + 1
                   then
                        0
              else
                   1
         end
/* test:
declare @b datetime
declare @e datetime
set @b = ‘2004-01-29‘
set @e = ‘2004-09-05‘
select @b as BeginDate ,@e as EndDate
,dbo.udf_WeekdayDiff(1,@b,@e) as CountOfMonday
,dbo.udf_WeekdayDiff(2,@b,@e) as CountOfTuesday
,dbo.udf_WeekdayDiff(3,@b,@e) as CountOfWednesday
,dbo.udf_WeekdayDiff(4,@b,@e) as CountOfThursday
,dbo.udf_WeekdayDiff(5,@b,@e) as CountOfFriday
,dbo.udf_WeekdayDiff(6,@b,@e) as CountOfSaturday
,dbo.udf_WeekdayDiff(7,@b,@e) as CountOfSunday
*/
end
go

create function udf_WeekdayID(@Date datetime)
returns integer
-- 返回 @Date 是 Monday 返回 1, ... ,是 Sunday 返回 1
begin
--1: Monday , ... ,7: Sunday
return (@@Datefirst + datepart(weekday,@Date)) % 7
       + case when (@@Datefirst + datepart(weekday,@Date)) % 7 
go

create function udf_NextWorkDate(@Date datetime)
returns datetime
-- 返回 @Date 的下一个工作日
begin
/*
declare @i int
set @i = 3
declare @Date datetime
set @Date = ‘2005-01-02‘
-- */
return case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 6 -- Friday
                 then dateadd(day,3,@Date)
            when (@@Datefirst + datepart(weekday,@Date)) % 7 = 0 -- saturday
                 then dateadd(day,2,@Date)
            else
                 dateadd(day,1,@Date)
       end
end
go

create function udf_PreviousWorkDate(@Date datetime)
returns datetime
-- 返回 @Date 的上一个工作日
begin
/*
declare @i int
set @i = 3
declare @Date datetime
set @Date = ‘2005-01-02‘
-- */
return case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 2 -- Monday
                 then dateadd(day,-3,@Date) 
            when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1 -- Sunday
                 then dateadd(day,-2,@Date) 
            else
                 dateadd(day,-1,@Date) 
       end
end
go

create function udf_WorkDateAdd(@i integer,@Date datetime)
returns datetime
-- 返回 @Date 加上一段 @i 个工作日的新值
begin
declare @ int
set @ = 0
while @ = 0
                         then --dbo.udf_nextworkdate(@Date)
                              case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 6 -- Friday
                                        then dateadd(day,3,@Date)
                                   when (@@Datefirst + datepart(weekday,@Date)) % 7 = 0 -- saturday
                                        then dateadd(day,2,@Date)
                                   else
                                        dateadd(day,1,@Date)
                              end
                    else
                         --dbo.udf_previousworkdate(@Date)
                         case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 2 -- Monday
                                   then dateadd(day,-3,@Date)
                              when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1 -- Sunday
                                   then dateadd(day,-2,@Date)
                              else
                                   dateadd(day,-1,@Date)
                         end
               end
               set @ = @ + 1 
end
return @Date
end
go

create function udf_GetStar (@ datetime)
RETURNS varchar(100)
-- 返回日期所属星座
BEGIN
RETURN
(
--declare @ datetime
--set @ = getdate()
select max(star)
from
(
select ‘魔羯座‘ as star,1 as [month],1 as [day]
union all select ‘水瓶座‘,1,20
union all select ‘双鱼座‘,2,19
union all select ‘牡羊座‘,3,21
union all select ‘金牛座‘,4,20
union all select ‘双子座‘,5,21
union all select ‘巨蟹座‘,6,22
union all select ‘狮子座‘,7,23
union all select ‘处女座‘,8,23
union all select ‘天秤座‘,9,23
union all select ‘天蝎座‘,10,24
union all select ‘射手座‘,11,22
union all select ‘魔羯座‘,12,22
) stars
where dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1 =
(
select max(dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1)
from (
select ‘魔羯座‘ as star,1 as [month],1 as [day]
union all select ‘水瓶座‘,1,20
union all select ‘双鱼座‘,2,19
union all select ‘牡羊座‘,3,21
union all select ‘金牛座‘,4,20
union all select ‘双子座‘,5,21
union all select ‘巨蟹座‘,6,22
union all select ‘狮子座‘,7,23
union all select ‘处女座‘,8,23
union all select ‘天秤座‘,9,23
union all select ‘天蝎座‘,10,24
union all select ‘射手座‘,11,22
union all select ‘魔羯座‘,12,22
) stars
where @ >= dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1
)
)
end

SQL Server 日期算法

一周的第一天
select @@DATEFIRST

一个月的第一天  
select dateadd(mm,datediff(mm,0,getdate()),0)

本周的星期一  
select dateadd(wk,datediff(wk,0,getdate()),0)

一年的第一天  
SELECT  DATEADD(yy,  DATEDIFF(yy,0,getdate()),  0)  

季度的第一天  
SELECT  DATEADD(qq,  DATEDIFF(qq,0,getdate()),  0)  

当天的零时
SELECT  DATEADD(dd,  DATEDIFF(dd,0,getdate()),  0)  

上个月的最后一天  ：本月第一天减2ms.
SELECT  dateadd(ms,-2,DATEADD(mm,  DATEDIFF(mm,0,getdate()),  0))  

本月的最后一天  
SELECT  dateadd(ms,-2,DATEADD(mm,  DATEDIFF(m,0,getdate())+1,  0)) 

本月的第一个星期一     

去掉时分秒  
DATEADD(day,  DATEDIFF(day,0,getdate()),  0)  
显示星期几  
select  datename(weekday,getdate())    
如何取得某个月的天数  
SELECT  Day(dateadd(ms,-3,DATEADD(mm,  DATEDIFF(m,0,getdate())+1,  0)))  

判断是否闰年：  
SELECT  case  day(dateadd(mm,  2,  dateadd(ms,-3,DATEADD(yy,  DATEDIFF(yy,0,getdate()),  0))))  
when  28  then  ‘平年‘  else  ‘闰年‘  end  
一个季度多少天  
declare  @m  tinyint,@time  smalldatetime  
select  @m=month(getdate())  
select  @m=case  when  @m  between  1  and  3  then  1  
                       when  @m  between  4  and  6  then  4  
                       when  @m  between  7  and  9  then  7  
                       else  10  end  
select  @time=datename(year,getdate())+‘-‘+convert(varchar(10),@m)+‘-01‘  
select  datediff(day,@time,dateadd(mm,3,@time))

SQL code

就用树型表,可到N级.参考如下:

create table tb(id int, name varchar(10), pid int, px int)
insert into tb values(0 , ‘栏目分类‘, 0 , 1)
insert into tb values(1 , ‘动物‘ , 0 , 1) 
insert into tb values(2 , ‘视频‘ , 0 , 2) 
insert into tb values(3 , ‘老虎‘ , 1 , 1) 
insert into tb values(4 , ‘狮子‘ , 1 , 2) 
insert into tb values(5 , ‘搞笑‘ , 2 , 1) 
go

--查询指定节点及其所有子节点的函数 
CREATE FUNCTION f_Cid(@ID int) RETURNS @t_Level TABLE(ID int,Level int) 
AS 
BEGIN 
  DECLARE @Level int 
  SET @Level=1 
  INSERT @t_Level SELECT @ID,@Level 
  WHILE @@ROWCOUNT>0 
  BEGIN 
    SET @Level=@Level+1 
    INSERT @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 

--调用函数查询id = 1及其所有子节点 
SELECT a.* FROM tb a,f_Cid(1) b WHERE a.ID=b.ID 
/*
id          name       pid         px          
----------- ---------- ----------- ----------- 
1           动物         0           1
3           老虎         1           1
4           狮子         1           2
（所影响的行数为 3 行）
*/

drop table tb
drop function dbo.f_cid


--------------------------------

--BOM算法
--产品配件清单查询示例(邹建)
CREATE TABLE Item(ID int,Name varchar(10),Wast decimal(2,2))
INSERT Item SELECT 1,N‘A产品‘,0.01
UNION  ALL  SELECT 2,N‘B产品‘,0.02
UNION  ALL  SELECT 3,N‘C产品‘,0.10
UNION  ALL  SELECT 4,N‘D配件‘,0.15
UNION  ALL  SELECT 5,N‘E物料‘,0.03
UNION  ALL  SELECT 6,N‘F物料‘,0.01
UNION  ALL  SELECT 7,N‘G配件‘,0.02

CREATE TABLE Bom(ItemID int,ChildId int)
INSERT Bom SELECT 1,4
UNION  ALL SELECT 1,7   --A产品由D配件和G配件组成
UNION  ALL SELECT 2,1
UNION  ALL SELECT 2,6
UNION  ALL SELECT 2,7   --B产品由F物料及G配件组成
UNION  ALL SELECT 4,5
UNION  ALL SELECT 4,6    --D配件由F物料组成
UNION  ALL SELECT 3,2
UNION  ALL SELECT 3,1    --C产品由A产品和B产品组成
GO

CREATE FUNCTION f_Bom(
@ItemIDs varchar(1000), --要查询物料清单及生产量的产品编号列表(逗号分隔)
@Num   int          --要生产的数量
)RETURNS @t TABLE(ItemID int,ChildId int,Nums int,Level int)
AS
BEGIN
    DECLARE @Level int
    SET @Level=1
    INSERT @t SELECT a.ItemID,a.ChildId,ROUND(@Num/(1-b.Wast),0),@Level
    FROM Bom a,Item b
    WHERE a.ChildId=b.ID
        AND CHARINDEX(‘,‘+RTRIM(a.ItemID)+‘,‘,‘,‘+@ItemIDs+‘,‘)>0
    WHILE @@ROWCOUNT>0 and @Level<140
    BEGIN
        SET @Level=@Level+1
        INSERT @t SELECT a.ItemID,b.ChildId,ROUND(a.Nums/(1-c.Wast),0),@Level
        FROM @t a,Bom b,Item c
        WHERE a.ChildId=b.ItemID
            AND b.ChildId=c.ID
            AND a.Level=@Level-1
    END
    RETURN
END
GO

--调用函数展开产品1、2、3的结构及计算生产10个产品时,各需要多少个配件
SELECT a.ItemID,ItemName=b.Name,
    a.ChildId,ChildName=c.Name,
    a.Nums,a.Level
FROM f_Bom(‘1,2,3‘,10) a,Item b,Item c
WHERE a.ItemID=b.ID
    AND a.ChildId=c.ID
ORDER BY a.ItemID,a.Level,a.ChildId

/*
ItemID      ItemName   ChildId     ChildName  Nums        Level
----------- ---------- ----------- ---------- ----------- -----------
1           A产品        4           D配件        12          1
1           A产品        7           G配件        10          1
1           A产品        5           E物料        12          2
1           A产品        6           F物料        12          2
2           B产品        1           A产品        10          1
2           B产品        6           F物料        10          1
2           B产品        7           G配件        10          1
2           B产品        4           D配件        12          2
2           B产品        7           G配件        10          2
2           B产品        5           E物料        12          3
2           B产品        6           F物料        12          3
3           C产品        1           A产品        10          1
3           C产品        2           B产品        10          1
3           C产品        1           A产品        10          2
3           C产品        4           D配件        12          2
3           C产品        6           F物料        10          2
3           C产品        7           G配件        10          2
3           C产品        7           G配件        10          2
3           C产品        4           D配件        12          3
3           C产品        5           E物料        12          3
3           C产品        6           F物料        12          3
3           C产品        7           G配件        10          3
3           C产品        5           E物料        12          4
3           C产品        6           F物料        12          4

(24 row(s) affected)


*/
drop table item
drop table bom
drop function f_Bom

-------------------------------------------------------------
就用树型表,可到N级.参考如下:

create table tb(id int, name varchar(10), pid int, px int)
insert into tb values(0 , ‘栏目分类‘, 0 , 1)
insert into tb values(1 , ‘动物‘ , 0 , 1) 
insert into tb values(2 , ‘视频‘ , 0 , 2) 
insert into tb values(3 , ‘老虎‘ , 1 , 1) 
insert into tb values(4 , ‘狮子‘ , 1 , 2) 
insert into tb values(5 , ‘搞笑‘ , 2 , 1) 
go

--查询指定节点及其所有子节点的函数 
CREATE FUNCTION f_Cid(@ID int) RETURNS @t_Level TABLE(ID int,Level int) 
AS 
BEGIN 
  DECLARE @Level int 
  SET @Level=1 
  INSERT @t_Level SELECT @ID,@Level 
  WHILE @@ROWCOUNT>0 
  BEGIN 
    SET @Level=@Level+1 
    INSERT @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 

--调用函数查询id = 1及其所有子节点 
SELECT a.* FROM tb a,f_Cid(1) b WHERE a.ID=b.ID 
/*
id          name       pid         px          
----------- ---------- ----------- ----------- 
1           动物         0           1
3           老虎         1           1
4           狮子         1           2
（所影响的行数为 3 行）
*/

drop table tb
drop function dbo.f_cid
-----------------------------------------------------
--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT ‘001‘,NULL ,‘山东省‘
UNION ALL SELECT ‘002‘,‘001‘,‘烟台市‘
UNION ALL SELECT ‘004‘,‘002‘,‘招远市‘
UNION ALL SELECT ‘003‘,‘001‘,‘青岛市‘
UNION ALL SELECT ‘005‘,NULL ,‘四会市‘
UNION ALL SELECT ‘006‘,‘005‘,‘清远市‘
UNION ALL SELECT ‘007‘,‘006‘,‘小分市‘
GO

--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
    DECLARE @Level int
    SET @Level=1
    INSERT @t_Level SELECT @ID,@Level
    WHILE @@ROWCOUNT>0
    BEGIN
        SET @Level=@Level+1
        INSERT @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

--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid(‘002‘) b
WHERE a.ID=b.ID
/*--结果
ID   PID  Name       
------ ------- ---------- 
002  001  烟台市
004  002  招远市
--*/



--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT ‘001‘,NULL ,‘山东省‘
UNION ALL SELECT ‘002‘,‘001‘,‘烟台市‘
UNION ALL SELECT ‘004‘,‘002‘,‘招远市‘
UNION ALL SELECT ‘003‘,‘001‘,‘青岛市‘
UNION ALL SELECT ‘005‘,NULL ,‘四会市‘
UNION ALL SELECT ‘006‘,‘005‘,‘清远市‘
UNION ALL SELECT ‘007‘,‘006‘,‘小分市‘

--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
    SET @Level=@Level+1
    INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
    FROM @t a,@t_Level b
    WHERE a.PID=b.ID
        AND b.Level=@Level-1
END

--显示结果
SELECT SPACE(b.Level*2)+‘|--‘+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
  |--烟台市
    |--招远市
  |--青岛市
|--四会市
  |--清远市
    |--小分市
--*/
-----------------------------------------

sql经典语句大全,古老的榕树,5-wow.com