MSSQL数据库和t-sql技巧集锦

核心提示：八、 树型的实现--参考--树形数据查询示例--作者: 邹建--示例数据create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))insert [tb] select 0,'中国'union all select 0,'

八、 树型的实现

--参考

--树形数据查询示例
--作者: 邹建

--示例数据
create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))
insert [tb] select 0,'中国'
union all select 0,'美国'
union all select 0,'加拿大'
union all select 1,'北京'
union all select 1,'上海'
union all select 1,'江苏'
union all select 6,'苏州'
union all select 7,'常熟'
union all select 6,'南京'
union all select 6,'无锡'
union all select 2,'纽约'
union all select 2,'旧金山'
go

--查询指定id的所有子
create function f_cid(
@id int
)returns @re table([id] int,[level] int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[id],@l
from [tb] a,@re b
where a.[pid]=b.[id] and b.[level]=@l-1
end
/**//**//**//**//**//**//**//*--如果只显示最明细的子(下面没有子),则加上这个删除
delete a from @re a
where exists(
select 1 from [tb] where [pid]=a.[id])
--*/
return
end
go

--调用(查询所有的子)
select a.*,层次=b.[level] from [tb] a,f_cid(2)b where a.[id]=b.[id]
go

--删除测试
drop table [tb]
drop function f_cid
go

九、 排序问题

CREATE TABLE [t] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[GUID] [uniqueidentifier] NULL
) ON [PRIMARY]
GO

下面这句执行5次

insert t values (newid())

查看执行结果

select * from t

1、 第一种

select * from t
order by case id when 4 then 1
when 5 then 2
when 1 then 3
when 2 then 4
when 3 then 5 end

2、 第二种

select * from t order by (id+2)%6

3、 第三种

select * from t order by charindex(cast(id as varchar),'45123')

4、 第四种

select * from t
WHERE id between 0 and 5
order by charindex(cast(id as varchar),'45123')

5、 第五种

select * from t order by case when id >3 then id-5 else id end

6、 第六种

select * from t order by id / 4 desc,id asc

十、 一条语句删除一批记录
首先id列是int标识类类型，然后删除ID值为5,6,8,9,10,11的列，这里的cast函数不能用convert函数代替，而且转换的类型必须是varchar，而不能是char，否则就会执行出你不希望的结果，这里的"5,6,8,9,10,11"可以是你在页面上获取的一个chkboxlist构建成的值，然后用下面的一句就全部删
除了，比循环用多条语句高效吧应该。

delete from [fujian] where charindex(','+cast([id] as varchar)+',',','+'5,6,8,9,10,11,'+',')>0

还有一种就是

delete from table1 where id in(1,2,3,4 )

十一、获取子表内的一列数据的组合字符串
下面这个函数获取05年已经注册了的某个所的律师，唯一一个参数就是事务所的名称，然后返回zhuce字段里包含05字样的所有律师。

CREATE FUNCTION fn_Get05LvshiNameBySuo (@p_suo Nvarchar(50))
RETURNS Nvarchar(2000)
AS
BEGIN
DECLARE @LvshiNames varchar(2000), @name varchar(50)
select @LvshiNames=''
DECLARE lvshi_cursor CURSOR FOR

数据库里有1,2,3,4,5 共5条记录,要用一条sql语句让其排序,使它排列成4,5,1,2,3,怎么写?
　　 --数据操作

SELECT --从数据库表中检索数据行和列
　　　　　　 INSERT --向数据库表添加新数据行
　　　　　　 DELETE --从数据库表中删除数据行
　　　　　　 UPDATE --更新数据库表中的数据

--数据定义

CREATE TABLE --创建一个数据库表
　　　　　　 DROP TABLE --从数据库中删除表
　　　　　　 ALTER TABLE --修改数据库表结构
　　　　　　 CREATE VIEW --创建一个视图
　　　　　　 DROP VIEW --从数据库中删除视图
　　　　　　 CREATE INDEX --为数据库表创建一个索引
　　　　　　 DROP INDEX --从数据库中删除索引
　　　　　　 CREATE PROCEDURE --创建一个存储过程
　　　　　　 DROP PROCEDURE --从数据库中删除存储过程
　　　　　　 CREATE TRIGGER --创建一个触发器
　　　　　　 DROP TRIGGER --从数据库中删除触发器
　　　　　　 CREATE SCHEMA --向数据库添加一个新模式
　　　　　　 DROP SCHEMA --从数据库中删除一个模式
　　　　　　 CREATE DOMAIN --创建一个数据值域
　　　　　　 ALTER DOMAIN --改变域定义
　　　　　　 DROP DOMAIN --从数据库中删除一个域

--数据控制

GRANT --授予用户访问权限
　　　　　　 DENY --拒绝用户访问
　　　　　　 REVOKE --解除用户访问权限

--事务控制

COMMIT --结束当前事务
　　　　　　 ROLLBACK --中止当前事务
　　　　　　 SET TRANSACTION --定义当前事务数据访问特征

--程序化SQL

DECLARE --为查询设定游标
　　　　　　 EXPLAN --为查询描述数据访问计划
　　　　　　 OPEN --检索查询结果打开一个游标
　　　　　　 FETCH --检索一行查询结果
　　　　　　 CLOSE --关闭游标
　　　　　　 PREPARE --为动态执行准备SQL 语句
　　　　　　 EXECUTE --动态地执行SQL 语句
　　　　　　 DESCRIBE --描述准备好的查询　　

---局部变量

declare @id char(10)
　　　　　　 --set @id = '10010001'
　　　　　　 select @id = '10010001'　　

---全局变量

---必须以@@开头　　

--IF ELSE

declare @x int @y int @z int
　　　　　　 select @x = 1 @y = 2 @z=3
　　　　　　 if @x > @y
　　　　　　 print 'x > y' --打印字符串'x > y'
　　　　　　 else if @y > @z
　　　　　　 print 'y > z'
　　　　　　 else print 'z > y'
　　　　　　 --CASE
　　　　　　 use pangu
　　　　　　 update employee
　　　　　　 set e_wage =
　　　　　　 case
　　　　　　 when job_level = ’1’ then e_wage*1.08
　　　　　　 when job_level = ’2’ then e_wage*1.07
　　　　　　 when job_level = ’3’ then e_wage*1.06
　　　　　　 else e_wage*1.05
　　　　　　 end
　　　　　　 --WHILE CONTINUE BREAK
　　　　　　 declare @x int @y int @c int
　　　　　　 select @x = 1 @y=1
　　　　　　 while @x < 3
　　　　　　 begin
　　　　　　 print @x --打印变量x 的值
　　　　　　 while @y < 3
　　　　　　 begin
　　　　　　 select @c = 100*@x + @y
　　　　　　 print @c --打印变量c 的值
　　　　　　 select @y = @y + 1
　　　　　　 end
　　　　　　 select @x = @x + 1
　　　　　　 select @y = 1
　　　　　　 end
　　　　　　 --WAITFOR