【网学网提醒】：文章导读：在新的一年中，各位网友都进入紧张的学习或是工作阶段。网学会员整理了一些sql技巧的相关内容供大家参考，祝大家在新的一年里工作和学习顺利！
ifexists(select*fromsysobjectswherename='t_tree')
droptablet_tree
go
createtablet_tree(
[serial_no][int]identity(1,1)notnull,
[p_serial_no][int]nulldefault(0),
[title][varchar](50)
)on
go
/*********************************************************************/
ifexists(select1fromsysobjectswherename='fun_get_sub_tree')
dropFUNCTIONfun_get_sub_tree
go
CREATEFUNCTIONfun_get_sub_tree
(@p_serial_noint)
RETURNS@table_subTABLE
(serial_noint,
p_serial_noint,
titlevarchar(50))
AS
BEGIN
/*
说明:获取树型结构表中任意节点下的子树
返回类型:Table
调用:select*fromdbo.fun_get_sub_tree(11)
*/
insertinto@table_sub
selectserial_no,@p_serial_no,titlefromt_treewherep_serial_no=@p_serial_no
while@@rowcount>0
begin
insertinto@table_subselectserial_no,p_serial_no,titlefromt_tree
wherep_serial_noin(selectserial_nofrom@table_sub)andserial_nonotin(selectserial_nofrom@table_sub)
end
RETURN
END
go
/*********************************************************************/
ifexists(select1fromsysobjectswherename='fun_get_tree_path')
dropfunctionfun_get_tree_path
go
createfunctionfun_get_tree_path
(@serial_noint)
returns@path_tabletable
(serial_noint,
p_serial_noint,
titlevarchar(50))
as
/*
说明:获取树型结构表中任意节点的全路径列表
返回类型:Table
调用:select*fromdbo.fun_get_tree_path(19)
*/
begin
declare@v_serial_noint
set@v_serial_no=@serial_no
insertinto@path_table
selectserial_no,p_serial_no,titlefromt_treewhereserial_no=@serial_no
while@@rowcount>0
begin
insertinto@path_table
selectserial_no,p_serial_no,titlefromt_treewhereserial_no=(selectp_serial_nofromt_treewhereserial_no=@v_serial_no)
select@v_serial_no=serial_nofromt_treewhereserial_no=(selectp_serial_nofromt_treewhereserial_no=@v_serial_no)
end
return
end
go
/*********************************************************************/
ifexists(select1fromsysobjectswherename='proc_get_sub_tree')
dropprocproc_get_sub_tree
go
createprocproc_get_sub_tree(
@table_namevarchar(50),
@key_columnvarchar(50),
@p_key_columnvarchar(50),
@name_columnvarchar(50),
@key_valueint
)
AS
/*
说明:获取树型结构表中任意节点下的子树
调用:execproc_get_sub_tree't_tree','serial_no','p_serial_no','title',7
*/
BEGIN
declare@sqlvarchar(2000)
set@sql='declare@return_tabletable('+@key_column+'int,'+@p_key_column+'int,'+@name_column+'varchar(20))'
set@sql=@sql+'insertinto@return_tableselect'+@key_column+','+cast(@key_valueasvarchar(5))+','+@name_column
set@sql=@sql+'from'+@table_name+'where'+@p_key_column+'='+cast(@key_valueasvarchar(5))
set@sql=@sql+'while@@rowcount>0'
set@sql=@sql+'begin'
set@sql=@sql+ 'insertinto@return_tableselect'+@key_column+','+@p_key_column+','+@name_column+'fromt_tree'
set@sql=@sql+ 'where'+@p_key_column+'in(select'+@key_column+'from@return_table)and'+@key_column+'notin(select'+@key_column+'from@return_table)'
set@sql=@sql+'end'
set@sql=@sql+'select*from@return_table'
exec(@sql)
END
go
/*********************************************************************/
当field1是否等于某个特定值查询是否需要执行field1=v_value的条件
declarev_valueinteger
select*fromtablenamewhere(field1=v_valueorfield1=0)
/*********************************************************************/
SQLcode--按某一字段分组取最大(小)值所在行的数据
(爱新觉罗.毓华2007-10-23于浙江杭州)
/*
数据如下：
namevalmemo
a2a2(a的第二个值)
a1a1--a的第一个值
a3a3:a的第三个值
b1b1--b的第一个值
b3b3:b的第三个值
b2b2b2b2b2
b4b4b4
b5b5b5b5b5b5
*/
--创建表并插入数据：
createtabletb(namevarchar(10),valint,memovarchar(20))
insertintotbvalues('a',2,'a2(a的第二个值)')
insertintotbvalues('a',1,'a1--a的第一个值')
insertintotbvalues('a',3,'a3:a的第三个值')
insertintotbvalues('b',1,'b1--b的第一个值')
insertintotbvalues('b',3,'b3:b的第三个值')
insertintotbvalues('b',2,'b2b2b2b2')
insertintotbvalues('b',4,'b4b4')
insertintotbvalues('b',5,'b5b5b5b5b5')
go
--一、按name分组取val最大的值所在行的数据。
--方法1：
selecta.*fromtbawhereval=(selectmax(val)fromtbwherename=a.name)orderbya.name
--方法2：
selecta.*fromtbawherenotexists(select1fromtbwherename=a.nameandval>a.val)
--方法3：
selecta.*fromtba,(selectname,max(val)valfromtbgroupbyname)bwherea.name=b.nameanda.val=b.valorderbya.name
--方法4：
selecta.*fromtbainnerjoin(selectname,max(val)valfromtbgroupbyname)bona.name=b.nameanda.val=b.valorderbya.name
--方法5
selecta.*fromtbawhere1>(selectcount(*)fromtbwherename=a.nameandval>a.val)orderbya.name
/*
namevalmemo
-----------------------------------------
a3a3:a的第三个值
b5b5b5b5b5b5
*/
--二、按name分组取val最小的值所在行的数据。
--方法1：
selecta.*fromtbawhereval=(selectmin(val)fromtbwherename=a.name)orderbya.name
--方法2：
selecta.*fromtbawherenotexists(select1fromtbwherename=a.nameandval
--方法3：
selecta.*fromtba,(selectname,min(val)valfromtbgroupbyname)bwherea.name=b.nameanda.val=b.valorderbya.name
--方法4：
selecta.*fromtbainnerjoin(selectname,min(val)valfromtb
groupbyname)bona.name=b.nameanda.val=b.valorderbya.name
--方法5
selecta.*fromtbawhere1>(selectcount(*)fromtbwherename=a.nameandval /*
namevalmemo
-----------------------------------------
a1a1--a的第一个值
b1b1--b的第一个值
*/
--三、按name分组取第一次出现的行所在的数据。
selecta.*fromtbawhereval=(selecttop1valfromtbwherename=a.name)orderbya.name
/*
namevalmemo
-----------------------------------------
a2a2(a的第二个值)
b1b1--b的第一个值
*/
--四、按name分组随机取一条数据。
selecta.*fromtbawhereval=(selecttop1valfromtbwherename=a.nameorderbynewid())orderbya.name
/*
namevalmemo
-----------------------------------------
a1a1--a的第一个值
b5b5b5b5b5b5
*/
--五、按name分组取最小的两个(N个)val
selecta.*fromtbawhere2>(selectcount(*)fromtbwherename=a.nameandval selecta.*fromtbawherevalin(selecttop2valfromtbwherename=a.nameorderbyval)orderbya.name,a.val
selecta.*fromtbawhereexists(selectcount(*)fromtbwherename=a.nameandval /*
namevalmemo
-----------------------------------------
a1a1--a的第一个值
a2a2(a的第二个值)
b1b1--b的第一个值
b2b2b2b2b2
*/
--六、按name分组取最大的两个(N个)val
selecta.*fromtbawhere2>(selectcount(*)fromtbwherename=a.nameandval>a.val)orderbya.name,a.val
selecta.*fromtbawherevalin(selecttop2valfromtbwherename=a.nameorderbyvaldesc)orderbya.name,a.val
selecta.*fromtbawhereexists(selectcount(*)fromtbwherename=a.nameandval>a.valhavingCount(*)<2)orderbya.name
/*
namevalmemo
-----------------------------------------
a2a2(a的第二个值)
a3a3:a的第三个值
b4b4b4
b5b5b5b5b5b5
*/
--七，如果整行数据有重复，所有的列都相同。
/*
数据如下：
namevalmemo
a2a2(a的第二个值)
a1a1--a的第一个值
a1a1--a的第一个值
a3a3:a的第三个值
a3a3:a的第三个值
b1b1--b的第一个值
b3b3:b的第三个值
b2b2b2b2b2
b4b4b4
b5b5b5b5b5b5
*/
--在sqlserver2000中只能用一个临时表来解决，生成一个自增列，先对val取最大或最小，然后再通过自增列来取数据。
--创建表并插入数据：
cr
eatetabletb(namevarchar(10),valint,memovarchar(20))
insertintotbvalues('a',2,'a2(a的第二个值)')
insertintotbvalues('a',1,'a1--a的第一个值')
insertintotbvalues('a',1,'a1--a的第一个值')
insertintotbvalues('a',3,'a3:a的第三个值')
insertintotbvalues('a',3,'a3:a的第三个值')
insertintotbvalues('b',1,'b1--b的第一个值')
insertintotbvalues('b',3,'b3:b的第三个值')
insertintotbvalues('b',2,'b2b2b2b2')
insertintotbvalues('b',4,'b4b4')
insertintotbvalues('b',5,'b5b5b5b5b5')
go
select*,px=identity(int,1,1)intotmpfromtb
selectm.name,m.val,m.memofrom
(
selectt.*fromtmptwhereval=(selectmin(val)fromtmpwherename=t.name)
)mwherepx=(selectmin(px)from
(
selectt.*fromtmptwhereval=(selectmin(val)fromtmpwherename=t.name)
)nwheren.name=m.name)
droptabletb,tmp
/*
namevalmemo
-----------------------------------------
a1a1--a的第一个值
b1b1--b的第一个值
(2行受影响)
*/
--在sqlserver2005中可以使用row_number函数，不需要使用临时表。
--创建表并插入数据：
createtabletb(namevarchar(10),valint,memovarchar(20))
insertintotbvalues('a',2,'a2(a的第二个值)')
insertintotbvalues('a',1,'a1--a的第一个值')
insertintotbvalues('a',1,'a1--a的第一个值')
insertintotbvalues('a',3,'a3:a的第三个值')
insertintotbvalues('a',3,'a3:a的第三个值')
insertintotbvalues('b',1,'b1--b的第一个值')
insertintotbvalues('b',3,'b3:b的第三个值')
insertintotbvalues('b',2,'b2b2b2b2')
insertintotbvalues('b',4,'b4b4')
insertintotbvalues('b',5,'b5b5b5b5b5')
go
selectm.name,m.val,m.memofrom
(
select*,px=row_number()over(orderbyname,val)fromtb
)mwherepx=(selectmin(px)from
(
select*,px=row_number()over(orderbyname,val)fromtb
)nwheren.name=m.name)
droptabletb
/*
namevalmemo
-----------------------------------------
a1a1--a的第一个值
b1b1--b的第一个值
(2行受影响)
*/
