转载 藏经阁第2卷-SQL常用的函数
原文地址：http://topic.csdn.net/u/20080803/21/1270777B-A2D6-4323-92D5-CDAA9CF30CF8.html
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等于几,无论是什么语言版本的SQLServer下面永远恒成立!
(@@Datefirst+datepart(weekday,@Date)):2、3、4、5、6、0、1分别代表周一到周日
--*/
createfunctionudf_GetAge(@StartDatedatetime,@EndDatedatetime)
returnsinteger
--返回精确年龄selectdbo.udf_GetAge('1949-10-01',getdate())
begin
returndatediff(year,@StartDate,@EndDate)
-casewhendatediff(day,dateadd(year,datediff(year,@StartDate,@EndDate),@StartDate),@EndDate)>=0
then0
else
1
end
end
go
createfunctionudf_DaysOfYearByDate(@Datedatetime)
RETURNSinteger
--返回年的天数可判断平(365)、润(366)年
begin
returndatediff(day,dateadd(year,datediff(year,0,@Date),0),dateadd(year,datediff(year,0,@Date)+1,0))
end
go
createfunctionudf_DaysOfYear(@Yearinteger)
RETURNSinteger
--返回年的天数可判断平(365)、润(366)年
begin
returndatediff(day,dateadd(year,@year-year(0),0),dateadd(year,@year-year(0)+1,0))
end
go
createfunctionudf_HalfDay(@Datedatetime)
returnsdatetime
--返回@Date是上午返回@Date的零点,@Date是下午返回@Date的十二点
as
begin
returncasewhendatepart(hour,@Date)
go
createfunctionudf_WeekDiff(@StartDatedatetime,@EndDatedatetime)
returnsinteger
--返回[@StartDate,@EndDate]之间周数周日是当周的最后一天
begin
returndatediff(week,@StartDate,@EndDate)--+1
+casewhen(@@Datefirst+datepart(weekday,@StartDate))%7=1
then1
else
0
end
-casewhen(@@Datefirst+datepart(weekday,@EndDate))%7=1
then1
else0
end
end
go
createfunctionudf_WeekOfMonth(@Datedatetime)
--返回@Date是所在月的第几周周日是当周的最后一天
returnsinteger
as
begin
returndatediff(week
,casewhen(@@Datefirst+datepart(weekday,dateadd(month,datediff(month,0,@Date),0)))%7=1
thendateadd(month,datediff(month,0,@Date),0)-1
else
dateadd(month,datediff(month,0,@Date),0)
end
,casewhen(@@Datefirst+datepart(weekday,@Date))%7=1
then@Date-1
else@Date
end
)+1
end
go
createfunctionudf_WeekOfQuarter(@Datedatetime)
--返回@Date是所在季度的第几周周日是当周的最后一天
returnsint
as
begin
returndatediff(week
,casewhen(@@Datefirst+datepart(weekday,dateadd(Quarter,datediff(Quarter,0,@Date),0)))%7=1
thendateadd(Quarter,datediff(Quarter,0,@Date),0)-1
else
dateadd(Quarter,datediff(Quarter,0,@Date),0)
end
,casewhen(@@Datefirst+datepart(weekday,@Date))%7=1
then@Date-1
else
@Date
end
)+1
end
go
createfunctionudf_WeekOfYear(@Datedatetime)
--返回@Date是所在年的第几周周日是当周的最后一天
returnsint
as
begin
returndatediff(week
,casewhen(@@Datefirst+datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@Date),0)))))%7=1
thendateadd(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
,casewhen(@@Datefirst+datepart(weekday,@Date))%7=1
thendateadd(day,-1,@Date)
else
@Date
end
)+1
end
go
createfunctionudf_WeekDay(@int,@Datedatetime)
returnsdatetime
--返回@Date映射到所在周的其他天周日是当周的最后一天
begin
/*
--周日算作(上一)周的最后一天
当@=7代表将@Date映射到所在周的星期日
可用于按周汇总Groupby,均支持跨年跨月数据
*/
returndateadd(day
,casewhen(@@Datefirst+datepart(weekday,@Date))%7=0--周六
thencasewhen@between1and6
then@-6
else
1
end
when(@@Datefirst+datepart(weekday,@Date))%7=1--周日(七)
thencasewhen@between1and6
then@-7
else
0
end
when(@@Datefirst+datepart(weekday,@Date))%7between2and6--周一至周五
thencasewhen@between1and6
then@+1-(@@Datefirst+datepart(weekday,@Date))%7
else
8-(@@Datefirst+datepart(weekday,@Date))%7
end
end
,@Date)
end
go
createfunctionudf_WeekdayDiff(@Weekdayinteger,@StartDatedatetime,@EndDatedatetime)
returnsinteger
----返回[@StartDate,@EndDate]之间周一到周日的个数周日是当周的最后一天
begin
--@Weekday:1:Monday,,7:Sunday
returndatediff(week,@StartDate,@EndDate)
+casewhen(@@Datefirst+datepart(weekday,@StartDate))%7
+casewhen(@@Datefirst+datepart(weekday,@StartDate))%7=0
then7
else
0
end>@Weekday%7+1
then0
else1
end
-casewhen(@@Datefirst+datepart(weekday,@EndDate))%7
+casewhen(@@Datefirst+datepart(weekday,@EndDate))%7=0
then7
else0
end>=@Weekday%7+1
then
0
else
1
end
/*test:
declare@bdatetime
declare@edatetime
set@b='2004-01-29'
set@e='2004-09-05'
select@basBeginDate,@easEndDate
,dbo.udf_WeekdayDiff(1,@b,@e)asCountOfMonday
,dbo.udf_WeekdayDiff(2,@b,@e)asCountOfTuesday
,dbo.udf_WeekdayDiff(3,@b,@e)asCountOfWednesday
,dbo.udf_WeekdayDiff(4,@b,@e)asCountOfThursday
,dbo.udf_WeekdayDiff(5,@b,@e)asCountOfFriday
,dbo.udf_WeekdayDiff(6,@b,@e)asCountOfSaturday
,dbo.udf_WeekdayDiff(7,@b,@e)asCountOfSunday
*/
end
go
createfunctionudf_WeekdayID(@Datedatetime)
returnsinteger
--返回@Date是Monday返回1,,是Sunday返回1
begin
--1:Monday,,7:Sunday
return(@@Datefirst+datepart(weekday,@Date))%7
+casewhen(@@Datefirst+datepart(weekday,@Date))%7
go
createfunctionudf_NextWorkDate(@Datedatetime)
returnsdatetime
--返回@Date的下一个工作日
begin
/*
declare@iint
set@i=3
declare@Datedatetime
set@Date='2005-01-02'
--*/
returncasewhen(@@Datefirst+datepart(weekday,@Date))%7=6--Friday
thendateadd(day,3,@Date)
when(@@Datefirst+datepart(weekday,@Date))%7=0--saturday
thendateadd(day,2,@Date)
else
dateadd(day,1,@Date)
end
end
go
SQLcode
createfunctionudf_PreviousWorkDate(@Datedatetime)
returnsdatetime
--返回@Date的上一个工作日
begin
/*
declare@iint
set@i=3
declare@Datedatetime
set@Date='2005-01-02'
--*/
returncasewhen(@@Datefirst+datepart(weekday,@Date))%7=2--Monday
thendateadd(day,-3,@Date)
when(@@Datefirst+datepart(weekday,@Date))%7=1--Sunday
thendateadd(day,-2,@Date)
else
dateadd(day,-1,@Date)
end
end
go
createfunctionudf_WorkDateAdd(@iinteger,@Datedatetime)
returnsdatetime
--返回@Date加上一段@i个工作日的新值
begin
declare@int
set@=0
while@=0
then--dbo.udf_nextworkdate(@Date)
casewhen(@@Datefirst+datepart(weekday,@Date))%7=6--Friday
thendateadd(day,3,@Date)
when(@@Datefirst+datepart(weekday,@Date))%7=0--saturday
thendateadd(day,2,@Date)
else
dateadd(day,1,@Date)
end
else
--dbo.udf_previousworkdate(@Date)
casewhen(@@Datefirst+datepart(weekday,@Date))%7=2--Monday
thendateadd(day,-3,@Date)
when(@@Datefirst+datepart(weekday,@Date))%7=1--Sunday
thendateadd(day,-2,@Date)
else
dateadd(day,-1,@Date)
end
end
set@=@+1
end
return@Date
end
go
createfunctionudf_GetStar(@datetime)
RETURNSvarchar(100)
--返回日期所属星座
BEGIN
RETURN
(
--declare@datetime
--set@=getdate()
selectmax(star)
from
(
select'魔羯座'asstar,1as[month],1as[day]
unionallselect'水瓶座',1,20
unionallselect'双鱼座',2,19
unionallselect'牡羊座',3,21
unionallselect'金牛座',4,20
unionallselect'双子座',5,21
unionallselect'巨蟹座',6,22
unionallselect'狮子座',7,23
unionallselect'处女座',8,23
unionallselect'天秤座',9,23
unionallselect'天蝎座',10,24
unionallselect'射手座',11,22
unionallselect'魔羯座',12,22
)stars
wheredateadd(month,[month]-1,dateadd(year,year(@)-year(0),0))+[day]-1=
(
selectmax(dateadd(month,[month]-1,dateadd(year,year(@)-year(0),0))+[day]-1)
from(
select'魔羯座'asstar,1as[month],1as[day]
unionallselect'水瓶座',1,20
unionallselect'双鱼座',2,19
unionallselect'牡羊座',3,21
unionallselect'金牛座',4,20
unionallselect'双子座',5,21
unionallselect'巨蟹座',6,22
unionallselect'狮子座',7,23
unionallselect'处女座',8,23
unionallselect'天秤座',9,23
unionallselect'天蝎座',10,24
unionallselect'射手座',11,22
unionallselect'魔羯座',12,22
)stars
where@>=dateadd(month,[month]-1,dateadd(year,year(@)-year(0),0))+[day]-1
)
)
end
SQLServer日期算法
一周的第一天
select@@DATEFIRST
一个月的第一天
selectdateadd(mm,datediff(mm,0,getdate()),0)
本周的星期一
selectdateadd(wk,datediff(wk,0,getdate()),0)
一年的第一天
SELECTDATEADD(yy,DATEDIFF(yy,0,getdate()),0)
季度的第一天
SELECTDATEADD(qq,DATEDIFF(qq,0,getdate()),0)
当天的零时
SELECTDATEADD(dd,DATEDIFF(dd,0,getdate()),0)
上个月的最后一天：本月第一天减2ms.
SELECTdateadd(ms,-2,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))
本月的最后一天
SELECTdateadd(ms,-2,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))
本月的第一个星期一
去掉时分秒
DATEADD(day,DATEDIFF(day,0,getdate()),0)
显示星期几
selectdatename(weekday,getdate())
如何取得某个月的天数
SELECTDay(dateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0)))
判断是否闰年：
SELECTcaseday(dateadd(mm,2,dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))))
when28then'平年'else'闰年'end
一个季度多少天
declare@mtinyint,@timesmalldatetime
select@m=month(getdate())
select@m=casewhen@mbetween1and3then1
when@mbetween4and6then4
when@mbetween7and9then7
else10end
select@time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01'
selectdatediff(day,@time,dateadd(mm,3,@time))
/*=============================================*/
/*Author:roy_88*/
--2实现全角与半角字符转换的处理函数
CREATEFUNCTIONf_Convert(
@strNVARCHAR(4000),--要转换的字符串
@flagbit--转换标志,0转换成半角,1转换成全角
)RETURNSnvarchar(4000)
AS
BEGIN
DECLARE@patnvarchar(8),@stepint,@iint,@spcint
IF@flag=0
SELECT@pat=N'%[！-～]%',@step=-65248,
@str=REPLACE(@str,N' ',N'')
ELSE
SELECT@pat=N'%[!-~]%',@step=65248,
@str=REPLACE(@str,N'',N' ')
SET@i=PATINDEX(@patCOLLATELATIN1_GENERAL_BIN,@str)
WHILE@i>0
SELECT@str=REPLACE(@str,
SUBSTRING(@str,@i,1),
NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step))
,@i=PATINDEX(@patCOLLATELATIN1_GENERAL_BIN,@str)
RETURN(@str)
END
GO
declareT_cursorcursorlocalfor
select
a.Name,b.Name
from
sysobjectsa
join
syscolumnsbona.ID=b.ID
join
systypesconc.xusertype=b.Xtype
where
a.xtype='U'andc.Namein('nvarchar','nchar','varchar','char')
declare@tabNamesysname,@ColNamesysname
openT_cursor
fetchnextfromT_cursorinto@tabName,@ColName
while@@fetch_status=0
begin
exec('update'+@tabName+'set'+@ColName+'=dbo.f_Convert('+@ColName+',0)wherePATINDEX(N''%[！-～]%''COLLATELATIN1_GENERAL_BIN'+','+@ColName+')>0'
fetchnextfromT_cursorinto@tabName,@ColName
end
closeT_cursor
deallocateT_cursor
---------------------------------------
--改列的数据全角为半角
declareT_cursorcursorlocalfor
select
a.Name,b.Name
from
sysobjectsa
join
syscolumnsbona.ID=b.ID
join
systypesconc.xusertype=b.Xtype
where
a.xtype='U'andc.Namein('nvarchar','nchar','varchar','char')
declare@tabNamesysname,@ColNamesysname
openT_cursor
fetchnextfromT_cursorinto@tabName,@ColName
while@@fetch_status=0
begin
exec('update'+@tabName+'set'+@ColName+'=dbo.f_Convert('+@ColName+',0)wherePATINDEX(N''%[！-～]%''COLLATELATIN1_GENERAL_BIN'+','+@ColName+')>0')--少了)
fetchnextfromT_cursorinto@tabName,@ColName
end
closeT_cursor
deallocateT_cursor
go
--改列名全角为半角
declareT_cursorcursorlocalfor
select
a.Name,b.Name
from
sysobjectsa
join
syscolumnsbona.ID=b.ID
where
a.xtype='U'andPATINDEX(N'%[！-～]%'COLLATELATIN1_GENERAL_BIN,b.Name)>0
declare@tabNamesysname,@ColNamesysname
openT_cursor
fetchnextfromT_cursorinto@tabName,@ColName
while@@fetch_status=0
begin
exec('exesp_rename'''+@tabName+'.'+@ColName+''',''dbo.f_Convert('+@ColName+',0)''')
fetchnextfromT_cursorinto@tabName,@ColName
end
closeT_cursor
deallocateT_cursor
------------------------------------------------------
--改列名全角为半角
declareT_cursorcursorlocalfor
select
a.Name+'.'+b.Name,NameNew=dbo.f_Convert(b.Name)
from
sysobjectsa
join
syscolumnsbona.ID=b.ID
where
a.xtype='U'andPATINDEX(N'%[！-～]%'COLLATELATIN1_GENERAL_BIN,b.Name)>0
declare@tabNamesysname,@ColNamesysname
openT_cursor
fetchnextfromT_cursorinto@tabName,@ColName
while@@fetch_status=0
begin
exesp_rename@tabName,@ColName
fetchnextfromT_cursorinto@tabName,@ColName
end
closeT_cursor
deallocateT_cursor
---------------------------
就用树型表,可到N级.参考如下:
createtabletb(idint,namevarchar(10),pidint,pxint)
insertintotbvalues(0,'栏目分类',0,1)
insertintotbvalues(1,'动物',0,1)
insertintotbvalues(2,'视频',0,2)
insertintotbvalues(3,'老虎',1,1)
insertintotbvalues(4,'狮子',1,2)
insertintotbvalues(5,'搞笑',2,1)
go
--查询指定节点及其所有子节点的函数
CREATEFUNCTIONf_Cid(@IDint)RETURNS@t_LevelTABLE(IDint,Levelint)
AS
BEGIN
DECLARE@Levelint
SET@Level=1
INSERT@t_LevelSELECT@ID,@Level
WHILE@@ROWCOUNT>0
BEGIN
SET@Level=@Level+1
INSERT@t_LevelSELECTa.ID,@Level
FROMtba,@t_Levelb
WHEREa.PID=b.ID
ANDb.Level=@Level-1
END
RETURN
END
GO
--调用函数查询id=1及其所有子节点
SELECTa.*FROMtba,f_Cid(1)bWHEREa.ID=b.ID
/*
idnamepidpx
-------------------------------------------
1动物01
3老虎11
4狮子12
（所影响的行数为3行）
*/
droptabletb
dropfunctiondbo.f_cid
--------------------------------
--BOM算法
--产品配件清单查询示例(邹建)
CREATETABLEItem(IDint,Namevarchar(10),Wastdecimal(2,2))
INSERTItemSELECT1,N'A产品',0.01
UNIONALLSELECT2,N'B产品',0.02
UNIONALLSELECT3,N'C产品',0.10
UNIONALLSELECT4,N'D配件',0.15
UNIONALLSELECT5,N'E物料',0.03
UNIONALLSELECT6,N'F物料',0.01
UNIONALLSELECT7,N'G配件',0.02
CREATETABLEBom(ItemIDint,ChildIdint)
INSERTBomSELECT1,4
UNIONALLSELECT1,7--A产品由D配件和G配件组成
UNIONALLSELECT2,1
UNIONALLSELECT2,6
UNIONALLSELECT2,7--B产品由F物料及G配件组成
UNIONALLSELECT4,5
UNIONALLSELECT4,6--D配件由F物料组成
UNIONALLSELECT3,2
UNIONALLSELECT3,1--C产品由A产品和B产品组成
GO
CREATEFUNCTIONf_Bom(
@ItemIDsvarchar(1000),--要查询物料清单及生产量的产品编号列表(逗号分隔)
@Numint--要生产的数量
)RETURNS@tTABLE(ItemIDint,ChildIdint,Numsint,Levelint)
AS
BEGIN
DECLARE@Levelint
SET@Level=1
INSERT@tSELECTa.ItemID,a.ChildId,ROUND(@Num/(1-b.Wast),0),@Level
FROMBoma,Itemb
WHEREa.ChildId=b.ID
ANDCHARINDEX(','+RTRIM(a.ItemID)+',',','+@ItemIDs+',')>0
WHILE@@ROWCOUNT>0and@Level<140
BEGIN
SET@Level=@Level+1
INSERT@tSELECTa.ItemID,b.ChildId,ROUND(a.Nums/(1-c.Wast),0),@Level
FROM@ta,Bomb,Itemc
WHEREa.ChildId=b.ItemID
ANDb.ChildId=c.ID
ANDa.Level=@Level-1
END
RETURN
END
GO
--调用函数展开产品1、2、3的结构及计算生产10个产品时,各需要多少个配件
SELECTa.ItemID,ItemName=b.Name,
a.ChildId,ChildName=c.Name,
a.Nums,a.Level
FROMf_Bom('1,2,3',10)a,Itemb,Itemc
WHEREa.ItemID=b.ID
ANDa.ChildId=c.ID
ORDERBYa.ItemID,a.Level,a.ChildId
/*
ItemIDItemNameChildIdChildNameNumsLevel
----------------------------------------------------------------
1A产品4D配件121
1A产品7G配件101
1A产品5E物料122
1A产品6F物料122
2B产品1A产品101
2B产品6F物料101
2B产品7G配件101
2B产品4D配件122
2B产品7G配件102
2B产品5E物料123
2B产品6F物料123
3C产品1A产品101
3C产品2B产品101
3C产品1A产品102
3C产品4D配件122
3C产品6F物料102
3C产品7G配件102
3C产品7G配件102
3C产品4D配件123
3C产品5E物料123
3C产品6F物料123
3C产品7G配件103
3C产品5E物料124
3C产品6F物料124
(24row(s)affected)
*/
droptableitem
droptablebom
dropfunctionf_Bom
-------------------------------------------------------------
就用树型表,可到N级.参考如下:
createtabletb(idint,namevarchar(10),pidint,pxint)
insertintotbvalues(0,'栏目分类',0,1)
insertintotbvalues(1,'动物',0,1)
insertintotbvalues(2,'视频',0,2)
insertintotbvalues(3,'老虎',1,1)
insertintotbvalues(4,'狮子',1,2)
insertintotbvalues(5,'搞笑',2,1)
go
--查询指定节点及其所有子节点的函数
CREATEFUNCTIONf_Cid(@IDint)RETURNS@t_LevelTABLE(IDint,Levelint)
AS
BEGIN
DECLARE@Levelint
SET@Level=1
INSERT@t_LevelSELECT@ID,@Level
WHILE@@ROWCOUNT>0
BEGIN
SET@Level=@Level+1
INSERT@t_LevelSELECTa.ID,@Level
FROMtba,@t_Levelb
WHEREa.PID=b.ID
ANDb.Level=@Level-1
END
RETURN
END
GO
--调用函数查询id=1及其所有子节点
SELECTa.*FROMtba,f_Cid(1)bWHEREa.ID=b.ID
/*
idnamepidpx
-------------------------------------------
1动物01
3老虎11
4狮子12
（所影响的行数为3行）
*/
droptabletb
dropfunctiondbo.f_cid
-----------------------------------------------------
--测试数据
CREATETABLEtb(IDchar(3),PIDchar(3),Namenvarchar(10))
INSERTtbSELECT'001',NULL,'山东省'
UNIONALLSELECT'002','001','烟台市'
UNIONALLSELECT'004','002','招远市'
UNIONALLSELECT'003','001','青岛市'
UNIONALLSELECT'005',NULL,'四会市'
UNIONALLSELECT'006','005','清远市'
UNIONALLSELECT'007','006','小分市'
GO
--查询指定节点及其所有子节点的函数
CREATEFUNCTIONf_Cid(@IDchar(3))
RETURNS@t_LevelTABLE(IDchar(3),Levelint)
AS
BEGIN
DECLARE@Levelint
SET@Level=1
INSERT@t_LevelSELECT@ID,@Level
WHILE@@ROWCOUNT>0
BEGIN
SET@Level=@Level+1
INSERT@t_LevelSELECTa.ID,@Level
FROMtba,@t_Levelb
WHEREa.PID=b.ID
ANDb.Level=@Level-1
END
RETURN
END
GO
--调用函数查询002及其所有子节点
SELECTa.*
FROMtba,f_Cid('002')b
WHEREa.ID=b.ID
/*--结果
IDPIDName
-----------------------
002001烟台市
004002招远市
--*/
--测试数据
DECLARE@tTABLE(IDchar(3),PIDchar(3),Namenvarchar(10))
INSERT@tSELECT'001',NULL,'山东省'
UNIONALLSELECT'002','001','烟台市'
UNIONALLSELECT'004','002','招远市'
UNIONALLSELECT'003','001','青岛市'
UNIONALLSELECT'005',NULL,'四会市'
UNIONALLSELECT'006','005','清远市'
UNIONALLSELECT'007','006','小分市'
--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE@t_LevelTABLE(IDchar(3),Levelint,Sortvarchar(8000))
DECLARE@Levelint
SET@Level=0
INSERT@t_LevelSELECTID,@Level,ID
FROM@t
WHEREPIDISNULL
WHILE@@ROWCOUNT>0
BEGIN
SET@Level=@Level+1
INSERT@t_LevelSELECTa.ID,@Level,b.Sort+a.ID
FROM@ta,@t_Levelb
WHEREa.PID=b.ID
ANDb.Level=@Level-1
END
--显示结果
SELECTSPACE(b.Level*2)+'|--'+a.Name
FROM@ta,@t_Levelb
WHEREa.ID=b.ID
ORDERBYb.Sort
/*--结果
|--山东省
|--烟台市
|--招远市
|--青岛市
|--四会市
|--清远市
|--小分市
--*/
----------------------------------------- 优质内容筛选与推荐>>
1、2006中国互联网最具潜力项目奖
2、net客户段验证
3、简单的文件处理类
4、防止微软故技重施 欧盟提前限制Vista捆绑过多软件
5、ERP系统BOM详细解析（一）
T-SQL:15个与日期时间相关的自定义函数(UDF),周日作为周的最后一天,均不受@@DateFirst、语言版本影响
都是从老文章里收集或提炼出来的!
提示:
(@@Datefirst+datepart(weekday,@Date))%7判断周几是最保险的!与@@DateFirst无关,与语言版本无关
@@DateFirst可能会导致datepart(weekday,@Date)不一样!
无论@@DateFirst等于几,无论是什么语言版本的SQLServer下面永远恒成立!
(@@Datefirst+datepart(weekday,@Date)):2、3、4、5、6、0、1分别代表周一到周日
--*/
createfunctionudf_GetAge(@StartDatedatetime,@EndDatedatetime)
returnsinteger
--返回精确年龄selectdbo.udf_GetAge('1949-10-01',getdate())
begin
returndatediff(year,@StartDate,@EndDate)
-casewhendatediff(day,dateadd(year,datediff(year,@StartDate,@EndDate),@StartDate),@EndDate)>=0
then0
else
1
end
end
go
createfunctionudf_DaysOfYearByDate(@Datedatetime)
RETURNSinteger
--返回年的天数可判断平(365)、润(366)年
begin
returndatediff(day,dateadd(year,datediff(year,0,@Date),0),dateadd(year,datediff(year,0,@Date)+1,0))
end
go
createfunctionudf_DaysOfYear(@Yearinteger)
RETURNSinteger
--返回年的天数可判断平(365)、润(366)年
begin
returndatediff(day,dateadd(year,@year-year(0),0),dateadd(year,@year-year(0)+1,0))
end
go
createfunctionudf_HalfDay(@Datedatetime)
returnsdatetime
--返回@Date是上午返回@Date的零点,@Date是下午返回@Date的十二点
as
begin
returncasewhendatepart(hour,@Date)
go
createfunctionudf_WeekDiff(@StartDatedatetime,@EndDatedatetime)
returnsinteger
--返回[@StartDate,@EndDate]之间周数周日是当周的最后一天
begin
returndatediff(week,@StartDate,@EndDate)--+1
+casewhen(@@Datefirst+datepart(weekday,@StartDate))%7=1
then1
else
0
end
-casewhen(@@Datefirst+datepart(weekday,@EndDate))%7=1
then1
else0
end
end
go
createfunctionudf_WeekOfMonth(@Datedatetime)
--返回@Date是所在月的第几周周日是当周的最后一天
returnsinteger
as
begin
returndatediff(week
,casewhen(@@Datefirst+datepart(weekday,dateadd(month,datediff(month,0,@Date),0)))%7=1
thendateadd(month,datediff(month,0,@Date),0)-1
else
dateadd(month,datediff(month,0,@Date),0)
end
,casewhen(@@Datefirst+datepart(weekday,@Date))%7=1
then@Date-1
else@Date
end
)+1
end
go
createfunctionudf_WeekOfQuarter(@Datedatetime)
--返回@Date是所在季度的第几周周日是当周的最后一天
returnsint
as
begin
returndatediff(week
,casewhen(@@Datefirst+datepart(weekday,dateadd(Quarter,datediff(Quarter,0,@Date),0)))%7=1
thendateadd(Quarter,datediff(Quarter,0,@Date),0)-1
else
dateadd(Quarter,datediff(Quarter,0,@Date),0)
end
,casewhen(@@Datefirst+datepart(weekday,@Date))%7=1
then@Date-1
else
@Date
end
)+1
end
go
createfunctionudf_WeekOfYear(@Datedatetime)
--返回@Date是所在年的第几周周日是当周的最后一天
returnsint
as
begin
returndatediff(week
,casewhen(@@Datefirst+datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@Date),0)))))%7=1
thendateadd(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
,casewhen(@@Datefirst+datepart(weekday,@Date))%7=1
thendateadd(day,-1,@Date)
else
@Date
end
)+1
end
go
createfunctionudf_WeekDay(@int,@Datedatetime)
returnsdatetime
--返回@Date映射到所在周的其他天周日是当周的最后一天
begin
/*
--周日算作(上一)周的最后一天
当@=7代表将@Date映射到所在周的星期日
可用于按周汇总Groupby,均支持跨年跨月数据
*/
returndateadd(day
,casewhen(@@Datefirst+datepart(weekday,@Date))%7=0--周六
thencasewhen@between1and6
then@-6
else
1
end
when(@@Datefirst+datepart(weekday,@Date))%7=1--周日(七)
thencasewhen@between1and6
then@-7
else
0
end
when(@@Datefirst+datepart(weekday,@Date))%7between2and6--周一至周五
thencasewhen@between1and6
then@+1-(@@Datefirst+datepart(weekday,@Date))%7
else
8-(@@Datefirst+datepart(weekday,@Date))%7
end
end
,@Date)
end
go
createfunctionudf_WeekdayDiff(@Weekdayinteger,@StartDatedatetime,@EndDatedatetime)
returnsinteger
----返回[@StartDate,@EndDate]之间周一到周日的个数周日是当周的最后一天
begin
--@Weekday:1:Monday,,7:Sunday
returndatediff(week,@StartDate,@EndDate)
+casewhen(@@Datefirst+datepart(weekday,@StartDate))%7
+casewhen(@@Datefirst+datepart(weekday,@StartDate))%7=0
then7
else
0
end>@Weekday%7+1
then0
else1
end
-casewhen(@@Datefirst+datepart(weekday,@EndDate))%7
+casewhen(@@Datefirst+datepart(weekday,@EndDate))%7=0
then7
else0
end>=@Weekday%7+1
then
0
else
1
end
/*test:
declare@bdatetime
declare@edatetime
set@b='2004-01-29'
set@e='2004-09-05'
select@basBeginDate,@easEndDate
,dbo.udf_WeekdayDiff(1,@b,@e)asCountOfMonday
,dbo.udf_WeekdayDiff(2,@b,@e)asCountOfTuesday
,dbo.udf_WeekdayDiff(3,@b,@e)asCountOfWednesday
,dbo.udf_WeekdayDiff(4,@b,@e)asCountOfThursday
,dbo.udf_WeekdayDiff(5,@b,@e)asCountOfFriday
,dbo.udf_WeekdayDiff(6,@b,@e)asCountOfSaturday
,dbo.udf_WeekdayDiff(7,@b,@e)asCountOfSunday
*/
end
go
createfunctionudf_WeekdayID(@Datedatetime)
returnsinteger
--返回@Date是Monday返回1,,是Sunday返回1
begin
--1:Monday,,7:Sunday
return(@@Datefirst+datepart(weekday,@Date))%7
+casewhen(@@Datefirst+datepart(weekday,@Date))%7
go
createfunctionudf_NextWorkDate(@Datedatetime)
returnsdatetime
--返回@Date的下一个工作日
begin
/*
declare@iint
set@i=3
declare@Datedatetime
set@Date='2005-01-02'
--*/
returncasewhen(@@Datefirst+datepart(weekday,@Date))%7=6--Friday
thendateadd(day,3,@Date)
when(@@Datefirst+datepart(weekday,@Date))%7=0--saturday
thendateadd(day,2,@Date)
else
dateadd(day,1,@Date)
end
end
go
SQLcode
createfunctionudf_PreviousWorkDate(@Datedatetime)
returnsdatetime
--返回@Date的上一个工作日
begin
/*
declare@iint
set@i=3
declare@Datedatetime
set@Date='2005-01-02'
--*/
returncasewhen(@@Datefirst+datepart(weekday,@Date))%7=2--Monday
thendateadd(day,-3,@Date)
when(@@Datefirst+datepart(weekday,@Date))%7=1--Sunday
thendateadd(day,-2,@Date)
else
dateadd(day,-1,@Date)
end
end
go
createfunctionudf_WorkDateAdd(@iinteger,@Datedatetime)
returnsdatetime
--返回@Date加上一段@i个工作日的新值
begin
declare@int
set@=0
while@=0
then--dbo.udf_nextworkdate(@Date)
casewhen(@@Datefirst+datepart(weekday,@Date))%7=6--Friday
thendateadd(day,3,@Date)
when(@@Datefirst+datepart(weekday,@Date))%7=0--saturday
thendateadd(day,2,@Date)
else
dateadd(day,1,@Date)
end
else
--dbo.udf_previousworkdate(@Date)
casewhen(@@Datefirst+datepart(weekday,@Date))%7=2--Monday
thendateadd(day,-3,@Date)
when(@@Datefirst+datepart(weekday,@Date))%7=1--Sunday
thendateadd(day,-2,@Date)
else
dateadd(day,-1,@Date)
end
end
set@=@+1
end
return@Date
end
go
createfunctionudf_GetStar(@datetime)
RETURNSvarchar(100)
--返回日期所属星座
BEGIN
RETURN
(
--declare@datetime
--set@=getdate()
selectmax(star)
from
(
select'魔羯座'asstar,1as[month],1as[day]
unionallselect'水瓶座',1,20
unionallselect'双鱼座',2,19
unionallselect'牡羊座',3,21
unionallselect'金牛座',4,20
unionallselect'双子座',5,21
unionallselect'巨蟹座',6,22
unionallselect'狮子座',7,23
unionallselect'处女座',8,23
unionallselect'天秤座',9,23
unionallselect'天蝎座',10,24
unionallselect'射手座',11,22
unionallselect'魔羯座',12,22
)stars
wheredateadd(month,[month]-1,dateadd(year,year(@)-year(0),0))+[day]-1=
(
selectmax(dateadd(month,[month]-1,dateadd(year,year(@)-year(0),0))+[day]-1)
from(
select'魔羯座'asstar,1as[month],1as[day]
unionallselect'水瓶座',1,20
unionallselect'双鱼座',2,19
unionallselect'牡羊座',3,21
unionallselect'金牛座',4,20
unionallselect'双子座',5,21
unionallselect'巨蟹座',6,22
unionallselect'狮子座',7,23
unionallselect'处女座',8,23
unionallselect'天秤座',9,23
unionallselect'天蝎座',10,24
unionallselect'射手座',11,22
unionallselect'魔羯座',12,22
)stars
where@>=dateadd(month,[month]-1,dateadd(year,year(@)-year(0),0))+[day]-1
)
)
end
SQLServer日期算法
一周的第一天
select@@DATEFIRST
一个月的第一天
selectdateadd(mm,datediff(mm,0,getdate()),0)
本周的星期一
selectdateadd(wk,datediff(wk,0,getdate()),0)
一年的第一天
SELECTDATEADD(yy,DATEDIFF(yy,0,getdate()),0)
季度的第一天
SELECTDATEADD(qq,DATEDIFF(qq,0,getdate()),0)
当天的零时
SELECTDATEADD(dd,DATEDIFF(dd,0,getdate()),0)
上个月的最后一天：本月第一天减2ms.
SELECTdateadd(ms,-2,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))
本月的最后一天
SELECTdateadd(ms,-2,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))
本月的第一个星期一
去掉时分秒
DATEADD(day,DATEDIFF(day,0,getdate()),0)
显示星期几
selectdatename(weekday,getdate())
如何取得某个月的天数
SELECTDay(dateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0)))
判断是否闰年：
SELECTcaseday(dateadd(mm,2,dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))))
when28then'平年'else'闰年'end
一个季度多少天
declare@mtinyint,@timesmalldatetime
select@m=month(getdate())
select@m=casewhen@mbetween1and3then1
when@mbetween4and6then4
when@mbetween7and9then7
else10end
select@time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01'
selectdatediff(day,@time,dateadd(mm,3,@time))
/*=============================================*/
/*Author:roy_88*/
--2实现全角与半角字符转换的处理函数
CREATEFUNCTIONf_Convert(
@strNVARCHAR(4000),--要转换的字符串
@flagbit--转换标志,0转换成半角,1转换成全角
)RETURNSnvarchar(4000)
AS
BEGIN
DECLARE@patnvarchar(8),@stepint,@iint,@spcint
IF@flag=0
SELECT@pat=N'%[！-～]%',@step=-65248,
@str=REPLACE(@str,N' ',N'')
ELSE
SELECT@pat=N'%[!-~]%',@step=65248,
@str=REPLACE(@str,N'',N' ')
SET@i=PATINDEX(@patCOLLATELATIN1_GENERAL_BIN,@str)
WHILE@i>0
SELECT@str=REPLACE(@str,
SUBSTRING(@str,@i,1),
NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step))
,@i=PATINDEX(@patCOLLATELATIN1_GENERAL_BIN,@str)
RETURN(@str)
END
GO
declareT_cursorcursorlocalfor
select
a.Name,b.Name
from
sysobjectsa
join
syscolumnsbona.ID=b.ID
join
systypesconc.xusertype=b.Xtype
where
a.xtype='U'andc.Namein('nvarchar','nchar','varchar','char')
declare@tabNamesysname,@ColNamesysname
openT_cursor
fetchnextfromT_cursorinto@tabName,@ColName
while@@fetch_status=0
begin
exec('update'+@tabName+'set'+@ColName+'=dbo.f_Convert('+@ColName+',0)wherePATINDEX(N''%[！-～]%''COLLATELATIN1_GENERAL_BIN'+','+@ColName+')>0'
fetchnextfromT_cursorinto@tabName,@ColName
end
closeT_cursor
deallocateT_cursor
---------------------------------------
--改列的数据全角为半角
declareT_cursorcursorlocalfor
select
a.Name,b.Name
from
sysobjectsa
join
syscolumnsbona.ID=b.ID
join
systypesconc.xusertype=b.Xtype
where
a.xtype='U'andc.Namein('nvarchar','nchar','varchar','char')
declare@tabNamesysname,@ColNamesysname
openT_cursor
fetchnextfromT_cursorinto@tabName,@ColName
while@@fetch_status=0
begin
exec('update'+@tabName+'set'+@ColName+'=dbo.f_Convert('+@ColName+',0)wherePATINDEX(N''%[！-～]%''COLLATELATIN1_GENERAL_BIN'+','+@ColName+')>0')--少了)
fetchnextfromT_cursorinto@tabName,@ColName
end
closeT_cursor
deallocateT_cursor
go
--改列名全角为半角
declareT_cursorcursorlocalfor
select
a.Name,b.Name
from
sysobjectsa
join
syscolumnsbona.ID=b.ID
where
a.xtype='U'andPATINDEX(N'%[！-～]%'COLLATELATIN1_GENERAL_BIN,b.Name)>0
declare@tabNamesysname,@ColNamesysname
openT_cursor
fetchnextfromT_cursorinto@tabName,@ColName
while@@fetch_status=0
begin
exec('exesp_rename'''+@tabName+'.'+@ColName+''',''dbo.f_Convert('+@ColName+',0)''')
fetchnextfromT_cursorinto@tabName,@ColName
end
closeT_cursor
deallocateT_cursor
------------------------------------------------------
--改列名全角为半角
declareT_cursorcursorlocalfor
select
a.Name+'.'+b.Name,NameNew=dbo.f_Convert(b.Name)
from
sysobjectsa
join
syscolumnsbona.ID=b.ID
where
a.xtype='U'andPATINDEX(N'%[！-～]%'COLLATELATIN1_GENERAL_BIN,b.Name)>0
declare@tabNamesysname,@ColNamesysname
openT_cursor
fetchnextfromT_cursorinto@tabName,@ColName
while@@fetch_status=0
begin
exesp_rename@tabName,@ColName
fetchnextfromT_cursorinto@tabName,@ColName
end
closeT_cursor
deallocateT_cursor
---------------------------
就用树型表,可到N级.参考如下:
createtabletb(idint,namevarchar(10),pidint,pxint)
insertintotbvalues(0,'栏目分类',0,1)
insertintotbvalues(1,'动物',0,1)
insertintotbvalues(2,'视频',0,2)
insertintotbvalues(3,'老虎',1,1)
insertintotbvalues(4,'狮子',1,2)
insertintotbvalues(5,'搞笑',2,1)
go
--查询指定节点及其所有子节点的函数
CREATEFUNCTIONf_Cid(@IDint)RETURNS@t_LevelTABLE(IDint,Levelint)
AS
BEGIN
DECLARE@Levelint
SET@Level=1
INSERT@t_LevelSELECT@ID,@Level
WHILE@@ROWCOUNT>0
BEGIN
SET@Level=@Level+1
INSERT@t_LevelSELECTa.ID,@Level
FROMtba,@t_Levelb
WHEREa.PID=b.ID
ANDb.Level=@Level-1
END
RETURN
END
GO
--调用函数查询id=1及其所有子节点
SELECTa.*FROMtba,f_Cid(1)bWHEREa.ID=b.ID
/*
idnamepidpx
-------------------------------------------
1动物01
3老虎11
4狮子12
（所影响的行数为3行）
*/
droptabletb
dropfunctiondbo.f_cid
--------------------------------
--BOM算法
--产品配件清单查询示例(邹建)
CREATETABLEItem(IDint,Namevarchar(10),Wastdecimal(2,2))
INSERTItemSELECT1,N'A产品',0.01
UNIONALLSELECT2,N'B产品',0.02
UNIONALLSELECT3,N'C产品',0.10
UNIONALLSELECT4,N'D配件',0.15
UNIONALLSELECT5,N'E物料',0.03
UNIONALLSELECT6,N'F物料',0.01
UNIONALLSELECT7,N'G配件',0.02
CREATETABLEBom(ItemIDint,ChildIdint)
INSERTBomSELECT1,4
UNIONALLSELECT1,7--A产品由D配件和G配件组成
UNIONALLSELECT2,1
UNIONALLSELECT2,6
UNIONALLSELECT2,7--B产品由F物料及G配件组成
UNIONALLSELECT4,5
UNIONALLSELECT4,6--D配件由F物料组成
UNIONALLSELECT3,2
UNIONALLSELECT3,1--C产品由A产品和B产品组成
GO
CREATEFUNCTIONf_Bom(
@ItemIDsvarchar(1000),--要查询物料清单及生产量的产品编号列表(逗号分隔)
@Numint--要生产的数量
)RETURNS@tTABLE(ItemIDint,ChildIdint,Numsint,Levelint)
AS
BEGIN
DECLARE@Levelint
SET@Level=1
INSERT@tSELECTa.ItemID,a.ChildId,ROUND(@Num/(1-b.Wast),0),@Level
FROMBoma,Itemb
WHEREa.ChildId=b.ID
ANDCHARINDEX(','+RTRIM(a.ItemID)+',',','+@ItemIDs+',')>0
WHILE@@ROWCOUNT>0and@Level<140
BEGIN
SET@Level=@Level+1
INSERT@tSELECTa.ItemID,b.ChildId,ROUND(a.Nums/(1-c.Wast),0),@Level
FROM@ta,Bomb,Itemc
WHEREa.ChildId=b.ItemID
ANDb.ChildId=c.ID
ANDa.Level=@Level-1
END
RETURN
END
GO
--调用函数展开产品1、2、3的结构及计算生产10个产品时,各需要多少个配件
SELECTa.ItemID,ItemName=b.Name,
a.ChildId,ChildName=c.Name,
a.Nums,a.Level
FROMf_Bom('1,2,3',10)a,Itemb,Itemc
WHEREa.ItemID=b.ID
ANDa.ChildId=c.ID
ORDERBYa.ItemID,a.Level,a.ChildId
/*
ItemIDItemNameChildIdChildNameNumsLevel
----------------------------------------------------------------
1A产品4D配件121
1A产品7G配件101
1A产品5E物料122
1A产品6F物料122
2B产品1A产品101
2B产品6F物料101
2B产品7G配件101
2B产品4D配件122
2B产品7G配件102
2B产品5E物料123
2B产品6F物料123
3C产品1A产品101
3C产品2B产品101
3C产品1A产品102
3C产品4D配件122
3C产品6F物料102
3C产品7G配件102
3C产品7G配件102
3C产品4D配件123
3C产品5E物料123
3C产品6F物料123
3C产品7G配件103
3C产品5E物料124
3C产品6F物料124
(24row(s)affected)
*/
droptableitem
droptablebom
dropfunctionf_Bom
-------------------------------------------------------------
就用树型表,可到N级.参考如下:
createtabletb(idint,namevarchar(10),pidint,pxint)
insertintotbvalues(0,'栏目分类',0,1)
insertintotbvalues(1,'动物',0,1)
insertintotbvalues(2,'视频',0,2)
insertintotbvalues(3,'老虎',1,1)
insertintotbvalues(4,'狮子',1,2)
insertintotbvalues(5,'搞笑',2,1)
go
--查询指定节点及其所有子节点的函数
CREATEFUNCTIONf_Cid(@IDint)RETURNS@t_LevelTABLE(IDint,Levelint)
AS
BEGIN
DECLARE@Levelint
SET@Level=1
INSERT@t_LevelSELECT@ID,@Level
WHILE@@ROWCOUNT>0
BEGIN
SET@Level=@Level+1
INSERT@t_LevelSELECTa.ID,@Level
FROMtba,@t_Levelb
WHEREa.PID=b.ID
ANDb.Level=@Level-1
END
RETURN
END
GO
--调用函数查询id=1及其所有子节点
SELECTa.*FROMtba,f_Cid(1)bWHEREa.ID=b.ID
/*
idnamepidpx
-------------------------------------------
1动物01
3老虎11
4狮子12
（所影响的行数为3行）
*/
droptabletb
dropfunctiondbo.f_cid
-----------------------------------------------------
--测试数据
CREATETABLEtb(IDchar(3),PIDchar(3),Namenvarchar(10))
INSERTtbSELECT'001',NULL,'山东省'
UNIONALLSELECT'002','001','烟台市'
UNIONALLSELECT'004','002','招远市'
UNIONALLSELECT'003','001','青岛市'
UNIONALLSELECT'005',NULL,'四会市'
UNIONALLSELECT'006','005','清远市'
UNIONALLSELECT'007','006','小分市'
GO
--查询指定节点及其所有子节点的函数
CREATEFUNCTIONf_Cid(@IDchar(3))
RETURNS@t_LevelTABLE(IDchar(3),Levelint)
AS
BEGIN
DECLARE@Levelint
SET@Level=1
INSERT@t_LevelSELECT@ID,@Level
WHILE@@ROWCOUNT>0
BEGIN
SET@Level=@Level+1
INSERT@t_LevelSELECTa.ID,@Level
FROMtba,@t_Levelb
WHEREa.PID=b.ID
ANDb.Level=@Level-1
END
RETURN
END
GO
--调用函数查询002及其所有子节点
SELECTa.*
FROMtba,f_Cid('002')b
WHEREa.ID=b.ID
/*--结果
IDPIDName
-----------------------
002001烟台市
004002招远市
--*/
--测试数据
DECLARE@tTABLE(IDchar(3),PIDchar(3),Namenvarchar(10))
INSERT@tSELECT'001',NULL,'山东省'
UNIONALLSELECT'002','001','烟台市'
UNIONALLSELECT'004','002','招远市'
UNIONALLSELECT'003','001','青岛市'
UNIONALLSELECT'005',NULL,'四会市'
UNIONALLSELECT'006','005','清远市'
UNIONALLSELECT'007','006','小分市'
--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE@t_LevelTABLE(IDchar(3),Levelint,Sortvarchar(8000))
DECLARE@Levelint
SET@Level=0
INSERT@t_LevelSELECTID,@Level,ID
FROM@t
WHEREPIDISNULL
WHILE@@ROWCOUNT>0
BEGIN
SET@Level=@Level+1
INSERT@t_LevelSELECTa.ID,@Level,b.Sort+a.ID
FROM@ta,@t_Levelb
WHEREa.PID=b.ID
ANDb.Level=@Level-1
END
--显示结果
SELECTSPACE(b.Level*2)+'|--'+a.Name
FROM@ta,@t_Levelb
WHEREa.ID=b.ID
ORDERBYb.Sort
/*--结果
|--山东省
|--烟台市
|--招远市
|--青岛市
|--四会市
|--清远市
|--小分市
--*/
----------------------------------------- 优质内容筛选与推荐>>
1、2006中国互联网最具潜力项目奖
2、net客户段验证
3、简单的文件处理类
4、防止微软故技重施 欧盟提前限制Vista捆绑过多软件
5、ERP系统BOM详细解析（一）