set language 'us_english'
select convert(char, getdate(), 107)
2017年10月27日 星期五
列出所有 Table 的資料結構.
/* 列出所有 Table 的資料結構.sql */
select C.name as TableName,
case A.status
when '16' then 'PK'
else ''
end as [Key], A.name as FieldName, '' as [Desc], D.name as type, A.length,
case A.isnullable
when '1' then 'V'
else ''
end as isnullable, isnull(B.text, '') as [Default],
case C.type
when 'U' then 'Table'
when 'V' then 'View'
else ''
end as TableOrView
from syscolumns as A, syscomments as B, systypes as D, sysobjects as C
where A.cdefault = B.id and A.xtype = D.xtype and D.status <> '1' -- 排除sysname 和 nvarchar 同 xtype = 231
and A.id = C.id
and (C.type = 'U' or C.type = 'V')
order by C.name, A.colid
轉貼至 http://tw.myblog.yahoo.com/jw!nowr4YGVQE4pAgs1INa7JnTZmg--/article?mid=509
select C.name as TableName,
case A.status
when '16' then 'PK'
else ''
end as [Key], A.name as FieldName, '' as [Desc], D.name as type, A.length,
case A.isnullable
when '1' then 'V'
else ''
end as isnullable, isnull(B.text, '') as [Default],
case C.type
when 'U' then 'Table'
when 'V' then 'View'
else ''
end as TableOrView
from syscolumns as A, syscomments as B, systypes as D, sysobjects as C
where A.cdefault = B.id and A.xtype = D.xtype and D.status <> '1' -- 排除sysname 和 nvarchar 同 xtype = 231
and A.id = C.id
and (C.type = 'U' or C.type = 'V')
order by C.name, A.colid
轉貼至 http://tw.myblog.yahoo.com/jw!nowr4YGVQE4pAgs1INa7JnTZmg--/article?mid=509
將多筆資料的特定欄位依分隔符號組成字串
select STUFF(
(select ',' +field1
from table1
where ..................
FOR XML PATH('')
), 1, 1, '') aa
參考至 http://www.dotblogs.com.tw/terrychuang/archive/2011/04/16/22867.aspx
(select ',' +field1
from table1
where ..................
FOR XML PATH('')
), 1, 1, '') aa
參考至 http://www.dotblogs.com.tw/terrychuang/archive/2011/04/16/22867.aspx
TSQL 解析JSON-fn_ParseJSON (轉貼)
Create FUNCTION [dbo].[fn_ParseJSON]( @json nvarchar(max) )
RETURNS @hierarchy table
(
element_id int IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */
parent_id int NOT NULL, /* [0 -- Root] if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */
object_id int NOT NULL, /* [0 -- Not an object] each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
name nvarchar(2000), /* the name of the object */
stringvalue nvarchar(4000) NOT NULL, /*the string representation of the value of the element. */
valuetype nvarchar(100) NOT NULL, /* the declared type of the value represented as a string in stringvalue*/
bigintvalue bigint
)
AS
BEGIN
--20140703 Peter Copy
--select * from dbo.fn_ParseJSON('{"a":"1", "b":"2"}')
DECLARE
@firstobject int, --the index of the first open bracket found in the JSON string
@opendelimiter int, --the index of the next open bracket found in the JSON string
@nextopendelimiter int,--the index of subsequent open bracket found in the JSON string
@nextclosedelimiter int,--the index of subsequent close bracket found in the JSON string
@type nvarchar(10),--whether it denotes an object or an array
@nextclosedelimiterChar CHAR(1),--either a '}' or a ']'
@contents nvarchar(MAX), --the unparsed contents of the bracketed expression
@start int, --index of the start of the token that you are parsing
@end int,--index of the end of the token that you are parsing
@param int,--the parameter at the end of the next Object/Array token
@endofname int,--the index of the start of the parameter at end of Object/Array token
@token nvarchar(4000),--either a string or object
@value nvarchar(MAX), -- the value as a string
@name nvarchar(200), --the name as a string
@parent_id int,--the next parent ID to allocate
@lenjson int,--the current length of the JSON String
@characters NCHAR(62),--used to convert hex to decimal
@result BIGINT,--the value of the hex symbol being parsed
@index SMALLINT,--used for parsing the hex value
@escape int; --the index of the next escape character
/* in this temporary table we keep all strings, even the names of the elements, since they are 'escaped'
* in a different way, and may contain, unescaped, brackets denoting objects or lists. These are replaced in
* the JSON string by tokens representing the string
*/
DECLARE @strings table
(
string_id int IDENTITY(1, 1),
stringvalue nvarchar(MAX)
)
/* initialise the characters to convert hex to ascii */
SET @characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
SET @parent_id = 0;
/* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */
WHILE 1 = 1 /* forever until there is nothing more to do */
BEGIN
SET @start = PATINDEX('%[^a-zA-Z]["]%', @json collate SQL_Latin1_General_CP850_Bin); /* next delimited string */
IF @start = 0 BREAK; /*no more so drop through the WHILE loop */
IF SUBSTRING(@json, @start+1, 1) = '"'
BEGIN /* Delimited name */
SET @start = @start+1;
SET @end = PATINDEX('%[^\]["]%', RIGHT(@json, LEN(@json+'|')-@start) collate SQL_Latin1_General_CP850_Bin);
END
IF @end = 0 /*no end delimiter to last string*/
BREAK; /* no more */
SELECT @token = SUBSTRING(@json, @start+1, @end-1)
/* now put in the escaped control characters */
SELECT @token = REPLACE(@token, from_string, to_string)
FROM
(
SELECT '\"' AS from_string, '"' AS to_string
UNION ALL
SELECT '\\', '\'
UNION ALL
SELECT '\/', '/'
UNION ALL
SELECT '\b', CHAR(08)
UNION ALL
SELECT '\f', CHAR(12)
UNION ALL
SELECT '\n', CHAR(10)
UNION ALL
SELECT '\r', CHAR(13)
UNION ALL
SELECT '\t', CHAR(09)
) substitutions;
SET @result = 0;
SET @escape = 1;
/*Begin to take out any hex escape codes*/
WHILE @escape > 0
BEGIN
/* find the next hex escape sequence */
SET @index = 0;
SET @escape = PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token collate SQL_Latin1_General_CP850_Bin);
IF @escape > 0 /* if there is one */
BEGIN
WHILE @index < 4 /* there are always four digits to a \x sequence */
BEGIN
/* determine its value */
SET @result = @result + POWER(16, @index) * (CHARINDEX(SUBSTRING(@token, @escape + 2 + 3 - @index, 1), @characters) - 1);
SET @index = @index + 1;
END
/* and replace the hex sequence by its unicode value */
SET @token = STUFF(@token, @escape, 6, NCHAR(@result));
END
END
/* now store the string away */
INSERT INTO @strings (stringvalue) SELECT @token;
/* and replace the string with a token */
SET @json = STUFF(@json, @start, @end + 1, '@string' + CONVERT(nvarchar(5), @@identity));
END
/* all strings are now removed. Now we find the first leaf. */
WHILE 1 = 1 /* forever until there is nothing more to do */
BEGIN
SET @parent_id = @parent_id + 1;
/* find the first object or list by looking for the open bracket */
SET @firstobject = PATINDEX('%[{[[]%', @json collate SQL_Latin1_General_CP850_Bin); /*object or array*/
IF @firstobject = 0 BREAK;
IF (SUBSTRING(@json, @firstobject, 1) = '{')
SELECT @nextclosedelimiterChar = '}', @type = 'object';
ELSE
SELECT @nextclosedelimiterChar = ']', @type = 'array';
SET @opendelimiter = @firstobject;
WHILE 1 = 1 --find the innermost object or list...
BEGIN
SET @lenjson = LEN(@json+'|') - 1;
/* find the matching close-delimiter proceeding after the open-delimiter */
SET @nextclosedelimiter = CHARINDEX(@nextclosedelimiterChar, @json, @opendelimiter + 1);
/* is there an intervening open-delimiter of either type */
SET @nextopendelimiter = PATINDEX('%[{[[]%',RIGHT(@json, @lenjson-@opendelimiter) collate SQL_Latin1_General_CP850_Bin); /*object*/
IF @nextopendelimiter = 0 BREAK;
SET @nextopendelimiter = @nextopendelimiter + @opendelimiter;
IF @nextclosedelimiter < @nextopendelimiter BREAK;
IF SUBSTRING(@json, @nextopendelimiter, 1) = '{'
SELECT @nextclosedelimiterChar = '}', @type = 'object';
ELSE
SELECT @nextclosedelimiterChar = ']', @type = 'array';
SET @opendelimiter = @nextopendelimiter;
END
/* and parse out the list or name/value pairs */
SET @contents = SUBSTRING(@json, @opendelimiter+1, @nextclosedelimiter-@opendelimiter - 1);
SET @json = STUFF(@json, @opendelimiter, @nextclosedelimiter - @opendelimiter + 1, '@' + @type + CONVERT(nvarchar(5), @parent_id));
WHILE (PATINDEX('%[A-Za-z0-9@+.e]%', @contents collate SQL_Latin1_General_CP850_Bin)) < > 0
BEGIN /* WHILE PATINDEX */
IF @type = 'object' /*it will be a 0-n list containing a string followed by a string, number,boolean, or null*/
BEGIN
SET @end = CHARINDEX(':', ' '+@contents); /*if there is anything, it will be a string-based name.*/
SET @start = PATINDEX('%[^A-Za-z@][@]%', ' ' + @contents collate SQL_Latin1_General_CP850_Bin); /*AAAAAAAA*/
SET @token = SUBSTRING(' '+@contents, @start + 1, @end - @start - 1);
SET @endofname = PATINDEX('%[0-9]%', @token collate SQL_Latin1_General_CP850_Bin);
SET @param = RIGHT(@token, LEN(@token)-@endofname + 1);
SET @token = LEFT(@token, @endofname - 1);
SET @contents = RIGHT(' ' + @contents, LEN(' ' + @contents + '|') - @end - 1);
SELECT @name = stringvalue FROM @strings WHERE string_id = @param; /*fetch the name*/
END
ELSE
BEGIN
SET @name = null;
END
SET @end = CHARINDEX(',', @contents); /*a string-token, object-token, list-token, number,boolean, or null*/
IF @end = 0
SET @end = PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @contents+' ' collate SQL_Latin1_General_CP850_Bin) + 1;
SET @start = PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' ' + @contents collate SQL_Latin1_General_CP850_Bin);
/*select @start,@end, LEN(@contents+'|'), @contents */
SET @value = RTRIM(SUBSTRING(@contents, @start, @end-@start));
SET @contents = RIGHT(@contents + ' ', LEN(@contents+'|') - @end);
IF SUBSTRING(@value, 1, 7) = '@object'
INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype)
SELECT @name, @parent_id, SUBSTRING(@value, 8, 5), SUBSTRING(@value, 8, 5), 'object';
ELSE
IF SUBSTRING(@value, 1, 6) = '@array'
INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype)
SELECT @name, @parent_id, SUBSTRING(@value, 7, 5), SUBSTRING(@value, 7, 5), 'array';
ELSE
IF SUBSTRING(@value, 1, 7) = '@string'
INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id)
SELECT @name, @parent_id, stringvalue, 'string', 0
FROM @strings
WHERE string_id = SUBSTRING(@value, 8, 5);
ELSE
IF @value IN ('true', 'false')
INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id)
SELECT @name, @parent_id, @value, 'boolean', 0;
ELSE
IF @value = 'null'
INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id)
SELECT @name, @parent_id, @value, 'null', 0;
ELSE
IF PATINDEX('%[^0-9]%', @value collate SQL_Latin1_General_CP850_Bin) > 0
INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id)
SELECT @name, @parent_id, @value, 'real', 0;
ELSE
INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id, bigintvalue)
SELECT @name, @parent_id, @value, 'bigint', 0, dbo.TryConvertBigInt(@value);
END /* WHILE PATINDEX */
END /* WHILE 1=1 forever until there is nothing more to do */
INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype)
SELECT '', 0, '', @parent_id - 1, @type;
RETURN;
END
RETURNS @hierarchy table
(
element_id int IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */
parent_id int NOT NULL, /* [0 -- Root] if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */
object_id int NOT NULL, /* [0 -- Not an object] each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
name nvarchar(2000), /* the name of the object */
stringvalue nvarchar(4000) NOT NULL, /*the string representation of the value of the element. */
valuetype nvarchar(100) NOT NULL, /* the declared type of the value represented as a string in stringvalue*/
bigintvalue bigint
)
AS
BEGIN
--20140703 Peter Copy
--select * from dbo.fn_ParseJSON('{"a":"1", "b":"2"}')
DECLARE
@firstobject int, --the index of the first open bracket found in the JSON string
@opendelimiter int, --the index of the next open bracket found in the JSON string
@nextopendelimiter int,--the index of subsequent open bracket found in the JSON string
@nextclosedelimiter int,--the index of subsequent close bracket found in the JSON string
@type nvarchar(10),--whether it denotes an object or an array
@nextclosedelimiterChar CHAR(1),--either a '}' or a ']'
@contents nvarchar(MAX), --the unparsed contents of the bracketed expression
@start int, --index of the start of the token that you are parsing
@end int,--index of the end of the token that you are parsing
@param int,--the parameter at the end of the next Object/Array token
@endofname int,--the index of the start of the parameter at end of Object/Array token
@token nvarchar(4000),--either a string or object
@value nvarchar(MAX), -- the value as a string
@name nvarchar(200), --the name as a string
@parent_id int,--the next parent ID to allocate
@lenjson int,--the current length of the JSON String
@characters NCHAR(62),--used to convert hex to decimal
@result BIGINT,--the value of the hex symbol being parsed
@index SMALLINT,--used for parsing the hex value
@escape int; --the index of the next escape character
/* in this temporary table we keep all strings, even the names of the elements, since they are 'escaped'
* in a different way, and may contain, unescaped, brackets denoting objects or lists. These are replaced in
* the JSON string by tokens representing the string
*/
DECLARE @strings table
(
string_id int IDENTITY(1, 1),
stringvalue nvarchar(MAX)
)
/* initialise the characters to convert hex to ascii */
SET @characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
SET @parent_id = 0;
/* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */
WHILE 1 = 1 /* forever until there is nothing more to do */
BEGIN
SET @start = PATINDEX('%[^a-zA-Z]["]%', @json collate SQL_Latin1_General_CP850_Bin); /* next delimited string */
IF @start = 0 BREAK; /*no more so drop through the WHILE loop */
IF SUBSTRING(@json, @start+1, 1) = '"'
BEGIN /* Delimited name */
SET @start = @start+1;
SET @end = PATINDEX('%[^\]["]%', RIGHT(@json, LEN(@json+'|')-@start) collate SQL_Latin1_General_CP850_Bin);
END
IF @end = 0 /*no end delimiter to last string*/
BREAK; /* no more */
SELECT @token = SUBSTRING(@json, @start+1, @end-1)
/* now put in the escaped control characters */
SELECT @token = REPLACE(@token, from_string, to_string)
FROM
(
SELECT '\"' AS from_string, '"' AS to_string
UNION ALL
SELECT '\\', '\'
UNION ALL
SELECT '\/', '/'
UNION ALL
SELECT '\b', CHAR(08)
UNION ALL
SELECT '\f', CHAR(12)
UNION ALL
SELECT '\n', CHAR(10)
UNION ALL
SELECT '\r', CHAR(13)
UNION ALL
SELECT '\t', CHAR(09)
) substitutions;
SET @result = 0;
SET @escape = 1;
/*Begin to take out any hex escape codes*/
WHILE @escape > 0
BEGIN
/* find the next hex escape sequence */
SET @index = 0;
SET @escape = PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token collate SQL_Latin1_General_CP850_Bin);
IF @escape > 0 /* if there is one */
BEGIN
WHILE @index < 4 /* there are always four digits to a \x sequence */
BEGIN
/* determine its value */
SET @result = @result + POWER(16, @index) * (CHARINDEX(SUBSTRING(@token, @escape + 2 + 3 - @index, 1), @characters) - 1);
SET @index = @index + 1;
END
/* and replace the hex sequence by its unicode value */
SET @token = STUFF(@token, @escape, 6, NCHAR(@result));
END
END
/* now store the string away */
INSERT INTO @strings (stringvalue) SELECT @token;
/* and replace the string with a token */
SET @json = STUFF(@json, @start, @end + 1, '@string' + CONVERT(nvarchar(5), @@identity));
END
/* all strings are now removed. Now we find the first leaf. */
WHILE 1 = 1 /* forever until there is nothing more to do */
BEGIN
SET @parent_id = @parent_id + 1;
/* find the first object or list by looking for the open bracket */
SET @firstobject = PATINDEX('%[{[[]%', @json collate SQL_Latin1_General_CP850_Bin); /*object or array*/
IF @firstobject = 0 BREAK;
IF (SUBSTRING(@json, @firstobject, 1) = '{')
SELECT @nextclosedelimiterChar = '}', @type = 'object';
ELSE
SELECT @nextclosedelimiterChar = ']', @type = 'array';
SET @opendelimiter = @firstobject;
WHILE 1 = 1 --find the innermost object or list...
BEGIN
SET @lenjson = LEN(@json+'|') - 1;
/* find the matching close-delimiter proceeding after the open-delimiter */
SET @nextclosedelimiter = CHARINDEX(@nextclosedelimiterChar, @json, @opendelimiter + 1);
/* is there an intervening open-delimiter of either type */
SET @nextopendelimiter = PATINDEX('%[{[[]%',RIGHT(@json, @lenjson-@opendelimiter) collate SQL_Latin1_General_CP850_Bin); /*object*/
IF @nextopendelimiter = 0 BREAK;
SET @nextopendelimiter = @nextopendelimiter + @opendelimiter;
IF @nextclosedelimiter < @nextopendelimiter BREAK;
IF SUBSTRING(@json, @nextopendelimiter, 1) = '{'
SELECT @nextclosedelimiterChar = '}', @type = 'object';
ELSE
SELECT @nextclosedelimiterChar = ']', @type = 'array';
SET @opendelimiter = @nextopendelimiter;
END
/* and parse out the list or name/value pairs */
SET @contents = SUBSTRING(@json, @opendelimiter+1, @nextclosedelimiter-@opendelimiter - 1);
SET @json = STUFF(@json, @opendelimiter, @nextclosedelimiter - @opendelimiter + 1, '@' + @type + CONVERT(nvarchar(5), @parent_id));
WHILE (PATINDEX('%[A-Za-z0-9@+.e]%', @contents collate SQL_Latin1_General_CP850_Bin)) < > 0
BEGIN /* WHILE PATINDEX */
IF @type = 'object' /*it will be a 0-n list containing a string followed by a string, number,boolean, or null*/
BEGIN
SET @end = CHARINDEX(':', ' '+@contents); /*if there is anything, it will be a string-based name.*/
SET @start = PATINDEX('%[^A-Za-z@][@]%', ' ' + @contents collate SQL_Latin1_General_CP850_Bin); /*AAAAAAAA*/
SET @token = SUBSTRING(' '+@contents, @start + 1, @end - @start - 1);
SET @endofname = PATINDEX('%[0-9]%', @token collate SQL_Latin1_General_CP850_Bin);
SET @param = RIGHT(@token, LEN(@token)-@endofname + 1);
SET @token = LEFT(@token, @endofname - 1);
SET @contents = RIGHT(' ' + @contents, LEN(' ' + @contents + '|') - @end - 1);
SELECT @name = stringvalue FROM @strings WHERE string_id = @param; /*fetch the name*/
END
ELSE
BEGIN
SET @name = null;
END
SET @end = CHARINDEX(',', @contents); /*a string-token, object-token, list-token, number,boolean, or null*/
IF @end = 0
SET @end = PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @contents+' ' collate SQL_Latin1_General_CP850_Bin) + 1;
SET @start = PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' ' + @contents collate SQL_Latin1_General_CP850_Bin);
/*select @start,@end, LEN(@contents+'|'), @contents */
SET @value = RTRIM(SUBSTRING(@contents, @start, @end-@start));
SET @contents = RIGHT(@contents + ' ', LEN(@contents+'|') - @end);
IF SUBSTRING(@value, 1, 7) = '@object'
INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype)
SELECT @name, @parent_id, SUBSTRING(@value, 8, 5), SUBSTRING(@value, 8, 5), 'object';
ELSE
IF SUBSTRING(@value, 1, 6) = '@array'
INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype)
SELECT @name, @parent_id, SUBSTRING(@value, 7, 5), SUBSTRING(@value, 7, 5), 'array';
ELSE
IF SUBSTRING(@value, 1, 7) = '@string'
INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id)
SELECT @name, @parent_id, stringvalue, 'string', 0
FROM @strings
WHERE string_id = SUBSTRING(@value, 8, 5);
ELSE
IF @value IN ('true', 'false')
INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id)
SELECT @name, @parent_id, @value, 'boolean', 0;
ELSE
IF @value = 'null'
INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id)
SELECT @name, @parent_id, @value, 'null', 0;
ELSE
IF PATINDEX('%[^0-9]%', @value collate SQL_Latin1_General_CP850_Bin) > 0
INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id)
SELECT @name, @parent_id, @value, 'real', 0;
ELSE
INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype, object_id, bigintvalue)
SELECT @name, @parent_id, @value, 'bigint', 0, dbo.TryConvertBigInt(@value);
END /* WHILE PATINDEX */
END /* WHILE 1=1 forever until there is nothing more to do */
INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype)
SELECT '', 0, '', @parent_id - 1, @type;
RETURN;
END
TSQL 小數轉分數-fn_DftOf
Create [dbo].[fn_DftOf](@JSON nvarchar(2000))
RETURNS varchar(100)
as
begin
-----------------
--小數轉分數-----
-----------------
declare
@type varchar(50)='',
@value varchar(50)='',
@num1 varchar(50)='',
@num2 varchar(50)=''
declare
@sStr varchar(50),
@iValue decimal(12,6),
@sResult varchar(50),
@sFh varchar(20)='', --負數符號
@sZs varchar(20), --整數
@sXs varchar(20), --小數
@iPos int, --小數點位置
@iFm int, --分母
@iFz int, --分子
@iZdgys int --最大公約數
declare
@iYs int, --餘數
@iNum1 int,
@iNum2 int
select @type=stringvalue from dbo.fn_parsejson(@JSON) where name='type'
select @value=stringvalue from dbo.fn_parsejson(@JSON) where name='value'
select @num1=stringvalue from dbo.fn_parsejson(@JSON) where name='num1'
select @num2=stringvalue from dbo.fn_parsejson(@JSON) where name='num2'
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
--小數轉分數
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
if @type=''
begin
--select dbo.fn_DftOf('{"value":"0.5"}')
--select dbo.fn_DftOf('{"value":"-3.5"}')
--select dbo.fn_DftOf('{"value":"90.000000"}')
--select dbo.fn_DftOf('{"value":"90"}')
--select dbo.fn_DftOf('{"value":"26.156250"}')
set @iValue = Convert(Decimal(12,6), @value)
if @iValue<0
set @sFh = '-'
set @sZs = Convert(varchar(50), Abs(Cast(@iValue as int))) --整數
set @iValue = @iValue - cast(@iValue as int) --Ex: -1.2345 -> -0.2345
set @iValue = Abs(@iValue) --Ex: -0.2345 -> 0.2345
set @sXs = Convert(varchar(50), @iValue)
set @sXs = convert(varchar(50), convert(int, right(@sXs, len(@sXs)-2))) --小數
if @sXs = '0'
begin
return @sZs --純整數
end
else
begin
set @iFz = convert(int, @sXs) --分子
set @iFm = power(10, len(@sXs)) --分母
set @iZdgys = (select dbo.fn_DftOf('{"type":"getZDGYS", "num1":"'+convert(varchar(10), @iFm)+'", "num2":"'+convert(varchar(10), @iFz)+'"}')) --求最大公約數
set @sResult = convert(varchar(50),(@iFz/@iZdgys))+'/'+convert(varchar(50),(@iFm/@iZdgys))
if @sZs='0'
set @sZs = ''
else
set @sZs = @sZs +'-' --整數與分數的連接符號
if @sFh='-'
set @sResult = '-(' + @sZs + @sResult + ')' --Ex: -(3-1/2)
else
set @sResult = @sZs + @sResult --Ex: 3-1/2
end
return @sResult
end
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
--取最大公約數
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
else if @type='getZDGYS'
begin
--select dbo.fn_DftOf('{"type":"getZDGYS", "num1":"10", "num2":"5"}') --求最大公約數
--select dbo.fn_DftOf('{"type":"getZDGYS", "num1":"100", "num2":"75"}') --求最大公約數
--輾轉相除法
set @iNum1=convert(int, @num1)
set @iNum2=convert(int, @num2)
set @iYs=@iNum1 - @iNum2 * convert(int, @iNum1/@iNum2) --MOD
while @iYs <> 0
begin
set @iNum1 = @iNum2
set @iNum2 = @iYs
set @iYs = @iNum1 - @iNum2 * convert(int, @iNum1/@iNum2) --MOD
end
set @sResult = convert(varchar(50), @iNum2)
return @sResult
end
return ''
end
改編至 http://blog.csdn.net/yzsind/article/details/2604798
RETURNS varchar(100)
as
begin
-----------------
--小數轉分數-----
-----------------
declare
@type varchar(50)='',
@value varchar(50)='',
@num1 varchar(50)='',
@num2 varchar(50)=''
declare
@sStr varchar(50),
@iValue decimal(12,6),
@sResult varchar(50),
@sFh varchar(20)='', --負數符號
@sZs varchar(20), --整數
@sXs varchar(20), --小數
@iPos int, --小數點位置
@iFm int, --分母
@iFz int, --分子
@iZdgys int --最大公約數
declare
@iYs int, --餘數
@iNum1 int,
@iNum2 int
select @type=stringvalue from dbo.fn_parsejson(@JSON) where name='type'
select @value=stringvalue from dbo.fn_parsejson(@JSON) where name='value'
select @num1=stringvalue from dbo.fn_parsejson(@JSON) where name='num1'
select @num2=stringvalue from dbo.fn_parsejson(@JSON) where name='num2'
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
--小數轉分數
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
if @type=''
begin
--select dbo.fn_DftOf('{"value":"0.5"}')
--select dbo.fn_DftOf('{"value":"-3.5"}')
--select dbo.fn_DftOf('{"value":"90.000000"}')
--select dbo.fn_DftOf('{"value":"90"}')
--select dbo.fn_DftOf('{"value":"26.156250"}')
set @iValue = Convert(Decimal(12,6), @value)
if @iValue<0
set @sFh = '-'
set @sZs = Convert(varchar(50), Abs(Cast(@iValue as int))) --整數
set @iValue = @iValue - cast(@iValue as int) --Ex: -1.2345 -> -0.2345
set @iValue = Abs(@iValue) --Ex: -0.2345 -> 0.2345
set @sXs = Convert(varchar(50), @iValue)
set @sXs = convert(varchar(50), convert(int, right(@sXs, len(@sXs)-2))) --小數
if @sXs = '0'
begin
return @sZs --純整數
end
else
begin
set @iFz = convert(int, @sXs) --分子
set @iFm = power(10, len(@sXs)) --分母
set @iZdgys = (select dbo.fn_DftOf('{"type":"getZDGYS", "num1":"'+convert(varchar(10), @iFm)+'", "num2":"'+convert(varchar(10), @iFz)+'"}')) --求最大公約數
set @sResult = convert(varchar(50),(@iFz/@iZdgys))+'/'+convert(varchar(50),(@iFm/@iZdgys))
if @sZs='0'
set @sZs = ''
else
set @sZs = @sZs +'-' --整數與分數的連接符號
if @sFh='-'
set @sResult = '-(' + @sZs + @sResult + ')' --Ex: -(3-1/2)
else
set @sResult = @sZs + @sResult --Ex: 3-1/2
end
return @sResult
end
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
--取最大公約數
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
else if @type='getZDGYS'
begin
--select dbo.fn_DftOf('{"type":"getZDGYS", "num1":"10", "num2":"5"}') --求最大公約數
--select dbo.fn_DftOf('{"type":"getZDGYS", "num1":"100", "num2":"75"}') --求最大公約數
--輾轉相除法
set @iNum1=convert(int, @num1)
set @iNum2=convert(int, @num2)
set @iYs=@iNum1 - @iNum2 * convert(int, @iNum1/@iNum2) --MOD
while @iYs <> 0
begin
set @iNum1 = @iNum2
set @iNum2 = @iYs
set @iYs = @iNum1 - @iNum2 * convert(int, @iNum1/@iNum2) --MOD
end
set @sResult = convert(varchar(50), @iNum2)
return @sResult
end
return ''
end
改編至 http://blog.csdn.net/yzsind/article/details/2604798
SQL 常用數值函式
-------------------------------------------------------------------------------------------------------
--數字
-------------------------------------------------------------------------------------------------------
--取整數 select
select cast(12.345 as int)
--取小數
select 12.345 - cast(12.345 as int)
-------------------------------------------------------------------------------------------------------
--字串
-------------------------------------------------------------------------------------------------------
--字串前面補零
select REPLICATE('0',10-LEN(Field)) + RTRIM(CAST(Field AS CHAR)) FROM Table
-------------------------------------------------------------------------------------------------------
--日期
-------------------------------------------------------------------------------------------------------
--年初
select DATEADD(yy, datediff(yy, 0, getdate()), 0) as 年初
--年底
select DATEADD(yy, datediff(yy, 0, getdate())+1, -1) as 年底
--月初
select DATEADD(mm, DATEDIFF(mm, '', getdate()), '') as 月初
--月底
select DATEADD(day, -1, DATEADD(mm, DATEDIFF(mm, '', getdate())+1, '')) as 月底
--上月初
select DATEADD(mm, -1, DATEADD(mm, DATEDIFF(mm, '', getdate()), '')) as 上月初
--上月底
select DATEADD(day, -1, DATEADD(mm, DATEDIFF(mm, '', getdate()), '')) as 上月底
--前二個月初
select DATEADD(mm, DATEDIFF(mm, '', getdate())-2, '') as 前二個月初
--前二個月底
select DATEADD(day, -1, DATEADD(mm, DATEDIFF(mm, '', getdate())-1, '')) as 前二個月底
--下個月初
select DATEADD(mm, DATEDIFF(mm, '', getdate())+1, '') as 下個月初
--下個月底
select DATEADD(day, -1, DATEADD(mm, DATEDIFF(mm, '', getdate())+2, '')) as 下個月底
--本週起始日_週日起算
select Getdate()-DATEPART(dw, GETDATE()-7)+1 as 本週起始日_週日起算
--本週末_週六
select Getdate()-DATEPART(dw, GETDATE()-7)+7 as 本週末_週六
--本週起始日_週一起算
select Getdate()-DATEPART(dw, GETDATE())+2 as 本週起始日_週一起算
--本週末_週日
select Getdate()-DATEPART(dw, GETDATE())+8 as 本週末_週日
--上週起始日_週日起算
select Getdate()-7-DATEPART(dw, GETDATE()-7)+1 as 上週起始日_週日起算
--上週末(週六)
select Getdate()-7-DATEPART(dw, GETDATE()-7)+7 as 上週末_週六
--上週起始日_週一起算
select Getdate()-7-DATEPART(dw, GETDATE()-7)+2 as 上週起始日_週一起算
--上週末_週日
select Getdate()-7-DATEPART(dw, GETDATE()-7)+8 as 上週末_週日
--數字
-------------------------------------------------------------------------------------------------------
--取整數 select
select cast(12.345 as int)
--取小數
select 12.345 - cast(12.345 as int)
-------------------------------------------------------------------------------------------------------
--字串
-------------------------------------------------------------------------------------------------------
--字串前面補零
select REPLICATE('0',10-LEN(Field)) + RTRIM(CAST(Field AS CHAR)) FROM Table
-------------------------------------------------------------------------------------------------------
--日期
-------------------------------------------------------------------------------------------------------
--年初
select DATEADD(yy, datediff(yy, 0, getdate()), 0) as 年初
--年底
select DATEADD(yy, datediff(yy, 0, getdate())+1, -1) as 年底
--月初
select DATEADD(mm, DATEDIFF(mm, '', getdate()), '') as 月初
--月底
select DATEADD(day, -1, DATEADD(mm, DATEDIFF(mm, '', getdate())+1, '')) as 月底
--上月初
select DATEADD(mm, -1, DATEADD(mm, DATEDIFF(mm, '', getdate()), '')) as 上月初
--上月底
select DATEADD(day, -1, DATEADD(mm, DATEDIFF(mm, '', getdate()), '')) as 上月底
--前二個月初
select DATEADD(mm, DATEDIFF(mm, '', getdate())-2, '') as 前二個月初
--前二個月底
select DATEADD(day, -1, DATEADD(mm, DATEDIFF(mm, '', getdate())-1, '')) as 前二個月底
--下個月初
select DATEADD(mm, DATEDIFF(mm, '', getdate())+1, '') as 下個月初
--下個月底
select DATEADD(day, -1, DATEADD(mm, DATEDIFF(mm, '', getdate())+2, '')) as 下個月底
--本週起始日_週日起算
select Getdate()-DATEPART(dw, GETDATE()-7)+1 as 本週起始日_週日起算
--本週末_週六
select Getdate()-DATEPART(dw, GETDATE()-7)+7 as 本週末_週六
--本週起始日_週一起算
select Getdate()-DATEPART(dw, GETDATE())+2 as 本週起始日_週一起算
--本週末_週日
select Getdate()-DATEPART(dw, GETDATE())+8 as 本週末_週日
--上週起始日_週日起算
select Getdate()-7-DATEPART(dw, GETDATE()-7)+1 as 上週起始日_週日起算
--上週末(週六)
select Getdate()-7-DATEPART(dw, GETDATE()-7)+7 as 上週末_週六
--上週起始日_週一起算
select Getdate()-7-DATEPART(dw, GETDATE()-7)+2 as 上週起始日_週一起算
--上週末_週日
select Getdate()-7-DATEPART(dw, GETDATE()-7)+8 as 上週末_週日
MSSQL RowID By Group
Select ROW_NUMBER() OVER(PARTITION BY [Field1] ORDER BY [Field1] DESC) AS RowID, *
from [Table]
from [Table]
SQL 找不到要更新的資料列。最後讀取的值已被變更
Ex:
SQL Server
Close;
SQL.Text := 'SET NOCOUNT OFF';
ExecSQL;
UpdateBatch();
Close;
SQL.Text := 'SET NOCOUNT ON';
ExecSQL;
SQL Server
Close;
SQL.Text := 'SET NOCOUNT OFF';
ExecSQL;
UpdateBatch();
Close;
SQL.Text := 'SET NOCOUNT ON';
ExecSQL;
SQL 常用日期函式
本週星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
第一個星期一
select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
月初
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
月底
SELECT DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
上個月底
SELECT DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
年初
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
年底
SELECT DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))
去年底
SELECT DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
第一個星期一
select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
月初
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
月底
SELECT DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
上個月底
SELECT DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
年初
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
年底
SELECT DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))
去年底
SELECT DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
訂閱：
文章 (Atom)