SQL學習
1.模糊表名的联合查询..
create table tz2008_1_1(id int,name varchar(50))
insert into tz2008_1_1 select 1,'a'
create table tz2008_1_2(id int,name varchar(50))
insert into tz2008_1_2 select 2,'b'
create table tz2008_1_3(id int,name varchar(50))
insert into tz2008_1_3 select 3,'c'
declare @sql varchar(8000)
select @sql=isnull(@sql+' union all ','')+' select * from ['+name+']'
from sysobjects where xtype='u' and name like 'tz2008%'
exec(@sql)
2.
--查询表的默认值
if object_id('tb') is not null
drop table tb
go
create table tb(id int,name varchar(50) default 'abc',num int default 5)
insert into tb(id) select 1
insert into tb select 1,'oo',100
insert into tb(id,name) select 1,'oo'
go
declare @tbname varchar(50)
set @tbname='tb'--表名
select @tbname as tbname,c.name as colname,replace(replace(replace(replace(b.[text],'(''',''),''')',''),'((',''),'))','') as defaultvalue
from sysconstraints a join syscomments b on a.constid=b.id
join syscolumns c on a.id=c.id and a.colid=c.colid
where a.id=object_id(@tbname) and object_name(a.constid) like '%df%'
3.
--存储过程语句查询
if object_id('proc_ttt') is not null
drop proc proc_ttt
go
create proc proc_ttt
as
select 1 union select 2
go
select [text] from syscomments
where id=object_id('proc_ttt')
EXEC SP_HELPTEXT 'proc_ttt'
4.
5.
6.
/*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。
问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/
create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go
--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名
--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')
---------------------------------
/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名
--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')
drop table tb
------------------
------------------
/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果)：
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/
create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go
--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end
--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')
--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t
--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。
--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程 分数
---- ------ ------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
------------------
*/
select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
drop table tb
7.
--按某一字段分组取最大(小)值所在行的数据
--(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-10-23于浙江杭州)
/*
数据如下：
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go
--一、按name分组取val最大的值所在行的数据。
--方法1：
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3：
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name val memo
---------- ----------- --------------------
a 3 a3:a的第三个值
b 5 b5b5b5b5b5
*/
--二、按name分组取val最小的值所在行的数据。
--方法1：
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3：
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
*/
--三、按name分组取第一次出现的行所在的数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
*/
--四、按name分组随机取一条数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 5 b5b5b5b5b5
*/
--五、按name分组取最小的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
b 2 b2b2b2b2
*/
--六、按name分组取最大的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
a 3 a3:a的第三个值
b 4 b4b4
b 5 b5b5b5b5b5
*/
--七，如果整行数据有重复，所有的列都相同。
/*
数据如下：
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 1 a1--a的第一个值
a 3 a3:a的第三个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
--在sql server 2000中只能用一个临时表来解决，生成一个自增列，先对val取最大或最小，然后再通过自增列来取数据。
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go
select * , px = identity(int,1,1) into tmp from tb
select m.name,m.val,m.memo from
(
select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) m where px = (select min(px) from
(
select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) n where n.name = m.name)
drop table tb,tmp
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
(2 行受影响)
*/
--在sql server 2005中可以使用row_number函数，不需要使用临时表。
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go
select m.name,m.val,m.memo from
(
select * , px = row_number() over(order by name , val) from tb
) m where px = (select min(px) from
(
select * , px = row_number() over(order by name , val) from tb
) n where n.name = m.name)
drop table tb
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
(2 行受影响)
*/
8.合并列值
原著：邹建
改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳
表结构，数据如下：
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果：
id values
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即：group by id, 求 value 的和（字符串相加）
1. 旧的解决方法(在sql server 2000中只能用函数解决。)
--1. 创建处理函数
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + value FROM tb WHERE id=@id
RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数
SELECt id, value = dbo.f_str(id) FROM tb GROUP BY id
drop table tb
drop function dbo.f_str
/*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
（所影响的行数为 2 行）
*/
--2、另外一种函数.
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
--创建一个合并的函数
create function f_hb(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(value as varchar) from tb where id = @id
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
--调用自定义函数得到结果：
select distinct id ,dbo.f_hb(id) as value from tb
drop table tb
drop function dbo.f_hb
/*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
（所影响的行数为 2 行）
*/
2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
-- 查询处理
SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM tb N
WHERE id = A.id
FOR XML AUTO
), ' <N value="', ','), '"/>', ''), 1, 1, '')
)N
drop table tb
/*
id values
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(2 行受影响)
*/
--SQL2005中的方法2
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id
/*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc
(2 row(s) affected)
*/
drop table tb
9.分拆列值
原著：邹建
改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳
有表tb, 如下:
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id value
----------- --------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
1. 旧的解决方法(sql server 2000)
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','
DROP TABLE #
2. 新的解决方法(sql server 2005)
create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
SELECT A.id, B.value
FROM(
SELECT id, [value] = CONVERT(xml,' <root> <v>' + REPLACE([value], ',', ' </v> <v>') + ' </v> </root>') FROM tb
)A
OUTER APPLY(
SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)B
DROP TABLE tb
/*
id value
----------- ------------------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
(5 行受影响)
*/
10.
11.
12.
--递归删除父节点及所有子节点
create table tb(Id int, ParentId int, Name varchar(5))
insert into tb select 1, 0, 'a1'
union all select 2,2, 'a2'
union all select 14, 1, 'b11'
union all select 15, 1, 'b12'
union all select 16, 14, 'c13'
union all select 17, 14, 'c14'
union all select 104,17,'d15'
go
WITH temptab(id, parentid, name) AS
( SELECT root.id, root.parentid, root.name
FROM tb root
WHERE id=1
UNION ALL
SELECT sub.id, sub.parentid, sub.name
FROM tb sub, temptab super
WHERE sub.parentid = super.id
)
delete from tb where id in(
select id from temptab
)
select * from tb
go
drop table tb
/*
Id ParentId Name
----------- ----------- -----
2 2 a2
14.精妙SQL语句
明：复制表(只复制结构,源表名：a 新表名：b)
SQL: select * into b from a where 1 <>1
说明：拷贝表(拷贝数据,源表名：a 目标表名：b)
SQL: insert into b(a, b, c) select d,e,f from b;
说明：显示文章、提交人和最后回复时间
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
说明：外连接查询(表名1：a 表名2：b)
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff( <|>minute <|>,f开始时间,getdate())>5
说明：两张关联表，删除主表中已经在副表中没有的信息
SQL:
delete from info where not exists ( select * from infobz where info.infid=infobz.infid )
说明：--
SQL:
SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
FROM TABLE1,
(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE
FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE, <|>YYYY/MM <|>) = TO_CHAR(SYSDATE, <|>YYYY/MM <|>)) X,
(SELECT NUM, UPD_DATE, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE, <|>YYYY/MM <|>) =
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, <|>YYYY/MM <|>) ||
<|>/01 <|>, <|>YYYY/MM/DD <|>) - 1,
<|>YYYY/MM <|>) ) Y,
WHERE X.NUM = Y.NUM （+）
AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B
WHERE A.NUM = B.NUM
说明：--
SQL:
select * from studentinfo where not exists(select * from student where
studentinfo.id=student.id) and 系名称=
<|>"&strdepartmentname&" <|> and 专业名称=
<|>"&strprofessionname&" <|> order by 性别,生源地,高考总成绩
说明：
从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）
SQL:
SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, <|>yyyy <|>) AS telyear,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>01 <|>, a.factration)) AS JAN,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>02 <|>, a.factration)) AS FRI,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>03 <|>, a.factration)) AS MAR,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>04 <|>, a.factration)) AS APR,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>05 <|>, a.factration)) AS MAY,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>06 <|>, a.factration)) AS JUE,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>07 <|>, a.factration)) AS JUL,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>08 <|>, a.factration)) AS AGU,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>09 <|>, a.factration)) AS SEP,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>10 <|>, a.factration)) AS OCT,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>11 <|>, a.factration)) AS NOV,
SUM(decode(TO_CHAR(a.telfeedate, <|>mm <|>), <|>12 <|>, a.factration)) AS DEC
FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration
FROM TELFEESTAND a, TELFEE b
WHERE a.tel = b.telfax) a
GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, <|>yyyy <|>)
说明：四表联查问题：
SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
说明：得到表中最小的未使用的ID号
SQL:
SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID
FROM Handle
WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)
15.ip各段内容提取，类似有3或4段字串通过一特殊字符连接的字串提取
-----------------------------------
declare @a varchar(50)
set @a='192.168.1.123'
SELECT PARSENAME(@a,1),PARSENAME(@a,2),PARSENAME(@a,3),PARSENAME(@a,4)
set @a='100,200,300'
SELECT PARSENAME(replace(@a,',','.'),1)高,PARSENAME(replace(@a,',','.'),2)宽
,PARSENAME(replace(@a,',','.'),3)长
16.--备份
declare @sql varchar(8000)
set @sql='backup database mis to disk=''d:\databack\mis\mis'
+rtrim(convert(varchar,getdate(),112))+'.bak'''
exec(@sql)
--删除15天前备份文件
set @sql='del d:\databack\mis\mis'
+rtrim(convert(varchar,getdate()-15,112))+'.bak'''
exec master..xp_cmdshell @sql
17.判断sql执行所花的时间（精度为毫秒）
DECLARE @begin datetime
DECLARE @chaju bigint
DECLARE @end datetime
SET @begin=getdate()
要执行的sql语句......
SET @end=getdate()
SELECT @chaju = datediff(Millisecond, @begin, @end)
PRINT @chaju
18.USE MASTER--连接系统数据库
IF EXISTS(select 1 from master..sysdatabases where name='bankDB')
DROP DATABASE bankDB
GO
-----------------------------------------------建库------------------
--打开外围服务器
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
--新建文件夹
exec xp_cmdshell 'MD e:\数据库'
CREATE DATABASE bankDB
ON
(
NAME ='bankDB',
FILENAME='e:\数据库\bankDB.mdf',
SIZE = 10,
MAXSIZE=500,
FILEGROWTH=15%
)
GO
--------------------------------------------------建表-----------------
USE bankDB
--用户信息表
IF EXISTS(select 1 from bankDB..sysobjects where name='userInfo')
DROP TABLE userInfo
GO
CREATE TABLE userInfo
(
customerID int identity(1,1) PRIMARY KEY , --顾客编号(自动增长 主键)
customerName varchar(20) not null, --开户名
PID varchar(20)UNIQUE not null, --身份证(18-15位数 唯一约束)
telephone varchar(20) not null, --联系电话(****-********或手机号11位数)
[address] ntext --联系地址
)
--银行卡信息表
IF EXISTS(select 1 from bankDB..sysobjects where name='cardInfo')
DROP TABLE cardInfo
GO
CREATE TABLE cardInfo
(
cardID varchar(20) primary key , --卡号 (格式为1010 3576 **** ***(*部分是随机产生))
curType varchar(10) default('RMB') not null,--货币种类 (默认为RMB)
savingType varchar(10), --存款类型 (活期/定活两便/定期)
openDate datetime default(getdate()) not null,--开户日期 (默认为当前时间)
openMoney money check(openMoney <1) not null, --开户金额 (不能低于1元)
dalance money check(dalance <1) not null, --余额 (不能低于1元 否则将销户)
pass varchar(20) default(888888) not null,--密码 (6位数 开户时默认为6个8)
IsReportLoss bit default(0) not null, --是否过失 (是/否 默认为否 1是 0否)
customerID int not null --顾客编号 (外键 该卡号对应的顾客编号 一个用户可办多张卡)
)
--交易信息表
IF EXISTS(select 1 from bankDB..sysobjects where name='transInfo')
DROP TABLE transInfo
GO
CREATE TABLE transInfo
(
transDate datetime not null, --交易日期(默认为当前时间)
cardID varchar(20) not null, --卡号(外键 可重复索引)
transType varchar(10) not null, --交易类型(只能是存入/支取)
transMoney money check(transMoney>0) not null, --交易金额(大于0)
remark ntext --备注(其它说明)
)
------------------------------约束-------------------------------
--约束电话和手机号码
if(object_id('uq_pid') is not null)
begin
alter table userinfo
drop constraint uq_pid
end
if(object_id('ck_PID') is not null)
begin
alter table userinfo
drop constraint ck_PID
end
if(object_id('ck_telephone') is not null)
begin
alter table userinfo
drop constraint ck_telephone
end
alter table userInfo
add constraint ck_PID check(len(pid) in (15,18)),
constraint uq_PID unique(pid),
constraint ck_telephone check(telephone like '1[35][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'--约束手机号码
or
telephone like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'--010-12345678
or
telephone like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'--0719-12345678
or
telephone like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]')--0719-1234567
----------------------------------------------设置卡号为随机数(方法1)---------------------------
declare @r decimal(10,8)
declare @time varchar(25)
set @time=convert(varchar,getdate(),120)+convert(varchar,datepart(ms,getdate()))
set @time=replace(@time,' ','')
set @time=replace(@time,':','')
set @time=replace(@time,'-','')
set @time=substring(@time,8,len(@time)-1)
--select @time--查看获得的随机数
select @r=rand(convert(bigint,@time))
set @time=cast(@r as varchar)
--select @time
set @time=substring(@time,3,len(@time)-1)
print @time
print '1001'+convert(varchar(10),@time)
----------------------------------------------设置卡号为随机数(方法2用了 存储过程)---------------------------
create proc proc_randCardID
@cardid varchar(19) output
as
declare @r numeric(8,8)
set @cardid='1010 3657 '
while(1=1)
begin
set @r=rand(datepart(mm,getdate())*100000+datepart(ss,getdate())*1000+datepart(ms,getdate()))
declare @temp char(8)
set @temp=substring(convert(varchar,@r),3,8)
set @cardid=@cardid+substring(@temp,1,4)+' '+substring(@temp,5,4)
if not exists(select 1 from cardinfo where cardid=@cardid)
break
end
--测试（调用存储过程）
declare @card varchar(19)
exec proc_randCardID @card output
print @card
*/
19./*通用分页存储过程*/
USE HotelManagementSystem
GO
IF EXISTS(SELECT * FROM sys.objects WHERE NAME='up_GetPageOfRecords')
DROP PROCEDURE up_GetPageOfRecords
GO
--创建存储过程
CREATE PROCEDURE up_GetPageOfRecords
@pageSize int = 20, --分页大小
@currentPage int , --第几页
@columns varchar(1000) = '*', --需要得到的字段
@tableName varchar(100), --需要查询的表
@condition varchar(1000) = '', --查询条件, 不用加where关键字
@ascColumn varchar(100) = '', --排序的字段名 (即 order by column asc/desc)
@bitOrderType bit = 0, --排序的类型 (0为升序,1为降序)
@pkColumn varchar(50) = '' --主键名称
AS
BEGIN --存储过程开始
DECLARE @strTemp varchar(300)
DECLARE @strSql varchar(5000) --该存储过程最后执行的语句
DECLARE @strOrderType varchar(1000) --排序类型语句 (order by column asc或者order by column desc)
BEGIN
IF @bitOrderType = 1 --降序
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC'
SET @strTemp = ' <(SELECT min'
END
ELSE --升序
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC'
SET @strTemp = '>(SELECT max'
END
IF @currentPage = 1 --第一页
BEGIN
IF @condition != ''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
' WHERE '+@condition+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+@strOrderType
END
ELSE -- 其他页
BEGIN
IF @condition !=''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
' WHERE '+@condition+' AND '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+
' '+@pkColumn+' FROM '+@tableName+@strOrderType+') AS TabTemp)'+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
' WHERE '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+' '+@pkColumn+
' FROM '+@tableName+@strOrderType+') AS TabTemp)'+@strOrderType
END
END
EXEC (@strSql)
END
20.双色球和值选球
Create proc up_getSumball
@XmlString varchar(2000),
@Max int
as
begin
declare @idtb table(id int)
create table #tb (id int,num int)
declare @num int,@id int,@sum int,@times int
declare @idHandle int
set @sum=0
set @times=0
EXEC sp_xml_preparedocument @idHandle OUTPUT, @XmlString
insert into #tb(id,num)
select * from openxml(@idHandle,N'/root/tb_ball')
with #tb
while (@sum <>@max or @times <>6)
begin
select top 1 @id=id,@num=num from #tb where num <=33 order by newid()
if @num <>@max begin
if not exists(select 1 from @idtb where id=@id)
insert into @idtb select @id
end
select @sum=sum(num) from #tb where id in(select id from @idtb)
select @times=count(1) from @idtb
if (@times>6 ) begin
delete @idtb
end
if ((@Max=@num) and (@times <6)) begin
delete @idtb
end
select @times=count(1) from @idtb
end
select * from #tb where id in(select id from @idtb)
drop table #tb
end
go
declare @tb table(id int,num int)
insert into @tb select 1,1
insert into @tb select 2,2
insert into @tb select 3,3
insert into @tb select 4,4
insert into @tb select 5,5
insert into @tb select 6,6
insert into @tb select 7,7
insert into @tb select 8,8
insert into @tb select 9,9
insert into @tb select 11,11
insert into @tb select 12,12
insert into @tb select 13,13
insert into @tb select 14,14
insert into @tb select 15,15
insert into @tb select 16,16
insert into @tb select 17,17
insert into @tb select 18,18
insert into @tb select 19,19
insert into @tb select 20,20
insert into @tb select 21,21
insert into @tb select 22,22
insert into @tb select 23,23
insert into @tb select 24,24
insert into @tb select 25,25
insert into @tb select 26,26
insert into @tb select 27,27
insert into @tb select 28,28
insert into @tb select 29,29
insert into @tb select 30,30
insert into @tb select 31,31
insert into @tb select 32,32
insert into @tb select 33,33
declare @dataxml xml
declare @strXml varchar(2000)
set @dataxml=(select * from @tb as tb_ball for xml auto,root('root'))
set @strXml=convert(varchar(2000),@dataxml)
exec up_getSumball @strXml,50
21.根据身份证计算性别函数（原创）
- SQL code
CREATE FUNCTION [dbo].[sex]
(
@IDCardNo NVARCHAR(50)
)
RETURNS int
AS
BEGIN
DECLARE @sex int
if (LEN(@IDCardNo)=18 and ISNUMERIC(SUBSTRING(@IDCardNo,17,1))=1 )
SET @sex= (
case
when SUBSTRING(@IDCardNo,17,1) in(1,3,5,7,9) then 1
when SUBSTRING(@IDCardNo,17,1) in(2,4,6,7,0) then 2
else
0
end
)
else if (LEN(@IDCardNo)=15 and ISNUMERIC(SUBSTRING(@IDCardNo,15,1))=1 )
SET @sex= (
case
when SUBSTRING(@IDCardNo,15,1) in(1,3,5,7,9) then 1
when SUBSTRING(@IDCardNo,15,1) in(2,4,6,7,0) then 2
else
0
end
)
else
SET @sex=0
RETURN(@sex)
END
--下面的SQL语句可以直接更改一个表的所有者，前提是要testuser用户存在
--EXEC sp_changeobjectowner 'dob.product', 'testuser'
----下面是通过修改系统表的相应值来达到修改所有者的目的。在sql2000下可用。在2005下还没有测试。
----创建存储过程
--更改单个表的所有者
if exists ( select name from sysobjects where name = 'ChangeTableOwner' and type= 'P')
drop procedure ChangeTableOwner
go
create procedure ChangeTableOwner
@TableName varchar(50),
@newUserName varchar(50)
as
Begin
exec sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
declare @newId int
if exists (select uid from sysusers where name = @newUserName)
BEGIN
select @newId = uid from sysusers where name = @newUserName
update sysobjects set uid = @newId where name=@TableName and type='u'
END
else
BEGIN
print N'the @newUserName does not exist,please check it and try again!'
END
exec sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
End
go
--更改所有表的所有者（不会更改系统表）
if exists ( select name from sysobjects where name = 'ChangeUser' and type= 'P')
drop procedure ChangeUser
go
create procedure ChangeUser
@newUserName varchar(50)
as
Begin
sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
declare @newId int
if exists (select uid from sysusers where name = @newUserName)
BEGIN
select @newId = uid from sysusers where name = @newUserName
update sysobjects set uid = @newId where type='u'
END
else
BEGIN
print N'the @newUserName does not exist,please check it and try again!'
END
sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
End
23.use master
go
if exists(select * from sysdatabases where name = 'stuDB')
drop database stuDB
create database stuDB
on
(
name = 'stuDB_data', --主数据文件名
filename = 'E:\stuDB_data.mdf', --主数据文件的物理名称
size = 5mb, --主数据文件的初始大小
maxsize = 10mb, --主数据文件的最大值
filegrowth = 15% --主数据文件的增长值
)
log on
(
name = 'stuDB_log', --日志文件名
filename = 'E:\stuDB_log.ldf', --日志文件的物理名称
size = 2mb, --日志文件的初始大小
filegrowth = 15% --日志文件的增长率
)
go
/*--创建学生信息表stuInfo--*/
use stuDB --设置当前数据库为stuDB数据库，以便在stuDB中创建表
go
create table stuInfo /*--创建学员信息表--*/
(
stuName nvarchar(20) not null, --学生姓名，非空
stuNo nchar(6) not null, --学生学号，非空
stuSex nchar(1) not null, --学生性别
stuAge int not null, --学生年龄
stuSeat int identity(1,1), --座位编号
stuAddress text --学生地址
)
go
/*--为stuInfo表添加约束条件--*/
--添加主键约束，学号作为主键
alter table stuInfo
add constraint pk_stuNo primary key(stuNo)
--添加检查约束，要求学号必须是"s253**"
alter table stuInfo
add constraint ck_stuNo check(stuNo like 's253__')
--添加检查约束，要求性别必须是男或女
alter table stuInfo
add constraint ck_stuSex check(stuSex = '男' or stuSex = '女')
--添加检查约束，要求年龄必须在15~40岁之间
alter table stuInfo
add constraint ck_stuAge check(stuAge between 15 and 40)
--添加检查约束，要求座位号必须在1~30之间
alter table stuInfo
add constraint ck_stuSeat check(stuSeat <= 30)
--添加默认约束，如果地址不填就默认为"地址不详"
alter table stuInfo
add constraint df_stuAddress default('地址不详') for stuAddress
go
/*--向学生信息表中(stuInfo)插入数据--*/
insert into stuInfo values('张秋丽','s25301','男',18,default)
insert into stuInfo
select '李斯文','s25303','女',22,'荷兰洛阳' union
select '李文才','s25302','男',31,'北京海淀' union
select '欧阳俊雄','s25304','男',28,'新建威武哈' union
select '果冻','s25305','男',25,'成都锦江区'
/*--创建学员成绩表(stuMarks)--*/
create table stuMarks
(
examNO nchar(7) not null, --考号
stuNo nchar(6) not null, --学号
writtenExam int not null, --笔试成绩
labExam int not null --机试成绩
)
go
/*--向学员成绩表(stuMarks)中插入信息--*/
insert stuMarks
select 's2007','s25301',80,58 union
select 's2008','s25303',50,90 union
select 's2009','s25302',65,0 union
select 's2010','s25304',77,82
select * from stuMarks
select * from stuInfo
set nocount on
/*--创建储存过程--*/
if exists(select * from sysobjects where name = 'proc_stu')
drop proc proc_stu
go
create proc proc_stu
@writtenExam int = 60,
@labExam int = 60
as
select stuInfo.stuName as 姓名,stuInfo.stuNo as 学号
,[笔试成绩] = case
when stuMarks.writtenExam is null then '缺考'
else convert(nvarchar(3),writtenExam)
end
,[机试成绩] = case
when stuMarks.labExam is null then '缺考'
else convert(nvarchar(3),labExam)
end
,[是否通过] = case
when stuMarks.labExam >= @labExam and stuMarks.writtenExam >= @writtenExam then '是'
else '否'
end
from stuInfo left join stuMarks on stuInfo.stuNo = stuMarks.stuNo
go
exec proc_stu
/*--创建带三个参数的储存过程--*/
if exists(select * from sysobjects where name = 'proc_stu2')
drop proc proc_stu2
go
create proc proc_stu2
@noPass int output,
@writtenExam int = 60,
@labExam int = 60
as
select @noPass = count(*)
from(select stuInfo.stuName as 姓名,stuInfo.stuNo as 学号
,[笔试成绩] = case
when stuMarks.writtenExam is null then '缺考'
else convert(nvarchar(3),writtenExam)
end
,[机试成绩] = case
when stuMarks.labExam is null then '缺考'
else convert(nvarchar(3),labExam)
end
,[是否通过] = case
when stuMarks.labExam >= @labExam and stuMarks.writtenExam >= @writtenExam then '是'
else '否'
end
from stuInfo left join stuMarks on stuInfo.stuNo = stuMarks.stuNo) as temp where temp.[是否通过] = '否'
go
declare @noPass int
exec proc_stu2 @noPass output,60,60
print '没有通过的人数为' + convert(nvarchar(2),@noPass) + '人'
/*--创建统计没有通过的学员信息的储存过程--*/
if exists(select * from sysobjects where name = 'proc_stu3')
drop proc proc_stu3
go
create proc proc_stu3
@writtenExam int = 60,
@labExam int = 60
as
declare @IsPass int
declare @sum int
declare @noPass int
declare @noPass1 int
select 笔试及格线 = @writtenExam,机试及格线 = @labExam
--查询没有通过考试的学员信息
select *
from(select stuInfo.stuName as 姓名,stuInfo.stuNo as 学号
,[笔试成绩] = case
when stuMarks.writtenExam is null then '缺考'
else convert(nvarchar(3),writtenExam)
end
,[机试成绩] = case
when stuMarks.labExam is null then '缺考'
else convert(nvarchar(3),labExam)
end
,[是否通过] = case
when stuMarks.labExam >= @labExam and stuMarks.writtenExam >= @writtenExam then '是'
else '否'
end
from stuInfo left join stuMarks on stuInfo.stuNo = stuMarks.stuNo) as temp where temp.[是否通过] = '否'
--统计通过的人数
select @IsPass = count(*)
from(select stuInfo.stuName as 姓名,stuInfo.stuNo as 学号
,[笔试成绩] = case
when stuMarks.writtenExam is null then '缺考'
else convert(nvarchar(3),writtenExam)
end
,[机试成绩] = case
when stuMarks.labExam is null then '缺考'
else convert(nvarchar(3),labExam)
end
,[是否通过] = case
when stuMarks.labExam >= @labExam and stuMarks.writtenExam >= @writtenExam then '是'
else '否'
end
from stuInfo left join stuMarks on stuInfo.stuNo = stuMarks.stuNo) as temp where temp.[是否通过] = '是'
select @sum = count(*) from stuInfo
set @noPass = @sum - @IsPass
set @noPass1 = @noPass * 100/@sum
if @noPass1 > 60
select 未通过人数 = @noPass,结论 = '超过60%及格分数还应下调'
else
select 未通过人数 = @noPass
go
exec proc_stu3 50,50
24.在前人基础上修改过的分页存储过程，单表多表都行，性能经过千万级数据验证
CREATE procedure [dbo].[userpage]
(
@SqlWhere varchar(1000)='', --查询条件, 可为空
@pagenum int=20, --每页的记录数
@beginline int=1, --第几页,默认第一页
@SqlTable varchar(1000), --要查询的表或视图,也可以一句sql语句
@SqlColumn varchar(1000), --查询的字段
@SqlPK varchar(50), --主键 必须填写，自动编号字段
@SqlOrder varchar(200) , --排序，可为空，则默认为以主键倒序排列
@GetCount bit=0, --0为取查询结果，1为取查询总数
@totalCount int --0为不限制结果总数，大于0即为取@totalCount条数据，其他忽视
)
as
set nocount on
declare @PageLowerBound int
declare @PageUpperBound int
declare @sqlstr nvarchar(2000)
declare @d datetime
if @SqlWhere=''
begin
set @SqlWhere=' where 1=1 '
end
else
begin
set @SqlWhere=' where 1=1 and '+@SqlWhere+' '
end
if @SqlOrder=''
begin
set @SqlOrder=' order by '+@SqlPK+' desc'
end
else
begin
set @SqlOrder=' order by '+@SqlOrder
end
if @SqlColumn='' set @SqlColumn=' * '
if @beginline=0 set @beginline=1
if @totalcount>0
begin
declare @totalpage int
set @totalpage=@totalcount/@pagenum
if @totalcount%@pagenum >0 set @totalpage=@totalpage+1
if @beginline>@totalpage set @beginline=@totalpage
end
declare @mytop nvarchar(20)
set @mytop=''
if @totalcount>0 set @mytop=' top '+convert(nvarchar(10),@totalcount)+' '
IF @GetCount=1
BEGIN
declare @Count int
declare @sCount int
if @totalCount=0
begin
set @sqlstr=N'select @sCount=count(1) FROM '+@SqlTable+@SqlWhere
end
else
begin
set @sqlstr=N'select @sCount=count(1) FROM '+@SqlTable+' where '+@SqlPK+' in (select top '+convert(nvarchar(10),@totalcount)+' '+@SqlPK+' from '+@SqlTable+' '+@Sqlwhere+')'
end
Exec sp_executesql @sqlstr,N'@sCount int outPut',@Count output
select @count as totalCount
END
else
begin
if @beginline=1
begin
set @sqlstr='select '+@SqlColumn+' from '+@SqlTable+' where '+@sqlPK+' in (select top '+str(@pagenum)+' '+@SqlPK+' from '+@SqlTable+@SqlWhere+@SqlOrder+')'+@SqlOrder
Exec sp_executesql @sqlstr
set nocount off
end
else
begin
set @PageLowerBound=(@beginline-1)*@pagenum
set @PageUpperBound=@PageLowerBound+@pagenum
create table #pageindex(temporary_id int identity(1,1) not null,temporary_nid int)
create unique clustered index index_nid_pageindex on #pageindex(temporary_id)
set rowcount @PageUpperBound
set @sqlstr=N'insert into #pageindex(temporary_nid) select '+@mytop+@SqlPK+' from '+@SqlTable+@SqlWhere+@SqlOrder
Exec sp_executesql @sqlstr
set @sqlstr='select '+@SqlColumn+' FROM '+ @SqlTable +' inner join #pageindex p on '+@SqlPK+'=p.temporary_nid and (p.temporary_id>'+STR(@PageLowerBound)+') and (p.temporary_id <='+STR(@PageUpperBound)+')' +@SqlOrder
Exec sp_executesql @sqlstr
set nocount off
drop table #pageindex
end
end
GO
25.--MS SQLSERVER 清空所有表的数据
CREATE PROC P_DelAllUserTableData
as
Begin
declare @name varchar(20)
Declare Cur Cursor For
select Name from sysobjects where xtype='u' and status>=0 and Name like '%WQ_%'
declare @SQL Varchar(20)
Open Cur
Fetch Cur Into @name
While @@FETCH_STATUS=0
BEGIN
Set @sql='DELETE '+@name+''
Exec(@sql)
Fetch Cur Into @name
End
Close Cur
Deallocate cur
End
GO
exec P_DelAllUserTableData
