随笔分类 -  SQL

SQL 数字函数
摘要:FLOOR 返回小于或等于指定数值表达式的最大整数。 CEILING 返回大于或等于指定数值表达式的最小整数。 round是四舍五入 阅读全文

posted @ 2012-05-28 10:37 笑看风云淡 阅读(156) 评论(0) 推荐(0)

SQL技巧(多行合并一列)
摘要:DECLARE @NAMES VARCHAR(1000)SELECT @NAMES = ISNULL(@NAMES+',','')+ColumnCode FROM DB_TableColumnsWHERE TableCode='faFixedAssets' ORDER BY UIDselect @NAMES 阅读全文

posted @ 2012-01-05 08:16 笑看风云淡 阅读(243) 评论(0) 推荐(0)

SQL流水号
摘要:--创建生成流水号的触发器CREATE TRIGGER [dbo].[T_arDtl_INSERT] ON [dbo].[arDtl]INSTEAD OF INSERTASDECLARE @ID INTSELECT * INTO #TB FROM insertedSELECT @ID=max(RUID) FROM TESTif @ID is null SET @ID=0update #TB SET @ID=@ID+1,RUID=@ID WHERE RUID=0 OR RUID IS NULLINSERT INTO TEST SELECT * FROM #tb 阅读全文

posted @ 2011-12-07 16:33 笑看风云淡 阅读(248) 评论(0) 推荐(0)

读取sql2000,sql2005中的表结构[转贴]
摘要:这是2005的SELECTTableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END,TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''),Column_id=C.column_id,ColumnName=C.name,PrimaryKey=ISNULL(IDX.PrimaryKey,N''),[IDENTITY]=CASE WHEN C.is_identity=1 THEN N'√'ELSE 阅读全文

posted @ 2011-12-07 16:10 笑看风云淡 阅读(136) 评论(0) 推荐(0)

SQL删除日志
摘要:backup log Appdata with NO_LOGbackup log Appdata with TRUNCATE_ONLYDBCC SHRINKDATABASE(Appdata) 阅读全文

posted @ 2011-10-31 13:42 笑看风云淡 阅读(209) 评论(0) 推荐(0)

OPENROWSET
摘要:SELECT * FROMOPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=192.168.0.1;UID=sa;PWD=1234',ERP.dbo.Employee) 阅读全文

posted @ 2011-10-31 09:26 笑看风云淡 阅读(159) 评论(0) 推荐(0)

取数据库字段信息
摘要:create procedure [dbo].[proGetColumnInfo] @tblName nvarchar(50) asselect table_name=c.Name, col_name=a.Name, is_identity, is_primary=case when exists(select 1 from sys.objects INNER JOIN sys.indexes ON sys.indexes.Name=sys.objects.Name INNER JOIN sysindexkeys ON sysindexkeys.indid=sys.indexes.index_ 阅读全文

posted @ 2011-05-24 09:31 笑看风云淡 阅读(154) 评论(0) 推荐(0)

sql 人民币数字转大写
摘要:CREATE FUNCTION [dbo].[f_num_chn] (@num numeric(18,2)) RETURNS varchar(100) WITH ENCRYPTION AS BEGIN --版权所有:weihuachao@sina.com DECLARE @n_data VARCHAR(20),@c_data VARCHAR(100),@n_str VARCHAR(10),@i int SET @n_data=RIGHT(SPACE(14)+CAST(CAST(ABS(@num*100) AS bigint) AS varchar(20)),14) SET @c_data=&# 阅读全文

posted @ 2011-05-10 17:16 笑看风云淡 阅读(400) 评论(0) 推荐(0)

sql server 提取汉字/数字/字母的方法
摘要:sql server 提取汉字/数字/字母的方法,作者:xys_777,转自xys_777的专栏--提取数字IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULLDROP FUNCTION DBO.GET_NUMBER2GOCREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))RETURNS VARCHAR(100)ASBE... 阅读全文

posted @ 2010-10-08 14:29 笑看风云淡 阅读(963) 评论(0) 推荐(0)

导航