SQL2000常用函数OR语句
====================================================================
随机取不相同的数
========================================
/*
功能：随机从（1，2，3，4，5，6，7，8，9，10）取得4个不相同的数
设计：over
时间：GetDate()
*/
declare @x int
create table #temp (dd int)
set @x=1
while @x<=10
begin
insert into #temp select @x
set @x=@x+1
end
select * FROM #temp
select top 4 * from #temp order by newid()
drop table #temp
======================================================
NOT EXISTS的妙用
======================================================
CREATE TABLE #Temp(学号 int ,姓名 nvarchar(20),科目 nvarchar(30),成绩 float)
INSERT INTO #Temp SELECT 1,'张三', '语文', 80
UNION SELECT 2,'李四' ,'语文', 83
UNION SELECT 3,'王五' ,'英语', 99
UNION SELECT 4,'李四' ,'数学', 88
UNION SELECT 5,'张三' ,'英语', 66
UNION SELECT 6,'王五' ,'数学', 87
UNION SELECT 7,'李四' ,'英语', 69
UNION SELECT 8,'张三' ,'数学', 63
UNION SELECT 9,'王五' ,'语文', 77
SELECT * FROM #Temp
SELECT A.* FROM #Temp AS A WHERE NOT EXISTS (SELECT 1 FROM #Temp AS B WHERE B.科目=A.科目 AND B.成绩>A.成绩 )
DROP TABLE #Temp
==============================================================
删除当前数据库有用户表数据
==============================================================
/*
功能：删除当前数据库有用户表数据
设计：
时间：2006-05
备注：该脚本执行危险性为★★★★★，不要随意执行
*/
DECLARE @tablename varchar(50)
DECLARE @truncatesql varchar(255)
DECLARE TrCun_Cursor CURSOR FOR
select [name] from sysobjects where type = 'U'
OPEN TrCun_Cursor
FETCH TrCun_Cursor INTO @tablename
WHILE(@@fetch_status = 0)
BEGIN
SET @truncatesql = 'truncate table ' + @tablename
--exec(@truncatesql) --当要删除时，就去掉--
PRINT @truncatesql
FETCH TrCun_Cursor INTO @tablename
END
CLOSE TrCun_Cursor
DEALLOCATE TrCun_Cursor
===========================================================
返回某一表的所有字段、存储过程、函数的参数信息
===========================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FN_GetObjColInfo]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FN_GetObjColInfo]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*
功能：返回某一表的所有字段、存储过程、函数的参数信息
设计：OK_008
时间：2006-05
*/
CREATE FUNCTION FN_GetObjColInfo
(@ObjName varchar(50))
RETURNS @Return_Table TABLE(
TName nvarchar(50),
TypeName nvarchar(50),
TypeLength nvarchar(50),
Colstat Bit
)
AS
BEGIN
INSERT @Return_Table
select b.name as 字段名,c.name as 字段类型,b.length/2 as 字段长度,b.colstat as 是否自动增长
from sysobjects a
inner join syscolumns b on a.id=b.id
inner join systypes c on c.xusertype=b.xtype
where a.name =@ObjName
order by B.ColID
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|
