If 'Peter' and 'Steve' are unique in your table, this will do:
UPDATE TableX
SET ord = ( SELECT MIN(ord) + MAX(ord)
FROM TableX
WHERE name IN ('Peter', 'Steve')
) - ord
WHERE name IN ('Peter', 'Steve')or (improved by @Erwin):
SELECT m.OrderID ,left(m.productIDs,len(m.productIDs)-1) as productIDsFinal from
(SELECT OrderID,(SELECT cast(ProductID AS NVARCHAR ) + ',' from [Order Details]
SELECT
ROW_NUMBER() OVER(ORDER BY CustomerID) AS ROWID
DECLARE @valueList varchar(8000) DECLARE @pos INT DECLARE @len INT DECLARE @value varchar(8000) SET @valueList = 'aa,bb,cc,f,sduygfdctys,w,e,r,t,sd sdf sdf,yyy yyy yy,' set @pos = 0 set @len = 0 WHILE CHARINDEX(',', @valueList, @pos+1)>0 BEGIN set @len = CHARINDEX(',', @valueList, @pos+1) - @pos set @value = SUBSTRING(@valueList, @pos, @len) --SELECT @pos, @len, @value /*this is here for debugging*/ PRINT @value --Here is you value --DO YOUR STUFF HERE --DO YOUR STUFF HERE --DO YOUR STUFF HERE --DO YOUR STUFF HERE --DO YOUR STUFF HERE set @pos = CHARINDEX(',', @valueList, @pos+@len) +1 END
DECLARE @RowData VARCHAR(MAX);
SET @RowData = '1,2,3,;4,5,6,;7,8,9,;';
WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
SplitRows (row_data, rn) AS (
SELECT SUBSTRING(';' + @RowData + ';', n + 1,
CHARINDEX(';', ';' + @RowData + ';', n + 1) - n - 1),
n + 1 - LEN(REPLACE(LEFT(@RowData, n), ';', ''))
FROM Nums
WHERE SUBSTRING(';' + @RowData + ';', n, 1) = ';'
AND n < LEN(';' + @RowData + ';')),
SplitColumns (rn, column_data, column_idx) AS (
SELECT rn,
SUBSTRING(',' + row_data + ',', n + 1,
CHARINDEX(',', ',' + row_data + ',', n + 1) - n - 1),
n + 1 - LEN(REPLACE(LEFT(row_data, n), ',', ''))
FROM Nums
JOIN SplitRows
ON SUBSTRING(',' + row_data + ',', n, 1) = ','
AND n < LEN(',' + row_data + ',')),
Pivoted (col1, col2, col3) AS (
SELECT [1], [2], [3]
FROM SplitColumns AS S
PIVOT
(MAX(column_data) FOR column_idx IN ([1], [2], [3])) AS P)
SELECT col1, col2, col3
FROM Pivoted
WHERE col1 IS NOT NULL;
CREATEFUNCTION dbo.splitstring (@stringToSplit VARCHAR(MAX)) RETURNS @returnList TABLE([Name][nvarchar](500))ASBEGINDECLARE@name NVARCHAR(255)DECLARE@pos INT WHILE CHARINDEX(',',@stringToSplit)>0BEGINSELECT@pos = CHARINDEX(',',@stringToSplit)SELECT@name = SUBSTRING(@stringToSplit,1,@pos-1)INSERTINTO@returnList SELECT@name SELECT@stringToSplit = SUBSTRING(@stringToSplit,@pos+1, LEN(@stringToSplit)-@pos)ENDINSERTINTO@returnList SELECT@stringToSplit RETURNEND
declare@id int,@stuff varchar(20)declare@tmp table( id int notnull, stuff varchar(20)primarykey(id))insert@tmp select id, stuff from mastertable where condition1 > condition2 selecttop1@id=id,@stuff=stuff from@tmp while(@@rowcount>0)beginprint@stuff deletefrom@tmp where id=@id selecttop1@id=id,@stuff=stuff from@tmp end
declare@ids table(id int);
ALTER PROCEDURE [dbo].[一般搜尋EX]
@函式名稱 NVARCHAR(255) = ''
,@程式語言 NVARCHAR(255) = ''
,@起始行 INT = 0
,@結束行 INT = 0
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @語法 NVARCHAR(MAX) = ''
,@語法2 NVARCHAR(MAX) = ''
,@條件 NVARCHAR(1024) = ''
,@參數 NVARCHAR(1024) = ''
,@顯示欄位 NVARCHAR(512) = ''
SET @顯示欄位 =N'SELECT *'
SET @語法 = N' FROM (SELECT ROW_NUMBER() OVER (ORDER BY 內文流水號 DESC) ROWID,X2.*,X1.目錄名稱,X3.程式語言
FROM [目錄](NOLOCK) X1
LEFT JOIN [內文](NOLOCK) X2
ON X1.目錄流水號=X2.來源目錄
LEFT JOIN [程式語言](NOLOCK) X3
ON X2.程式語言id = X3.id
WHERE (1=1){0}
) A {1}';
SET @語法2 = @語法;
SET @語法 = @顯示欄位 + @語法;
IF @函式名稱 <> '' SET @條件 += N' OR (X2.函式名稱 LIKE ''%'+@函式名稱+'%'')';
IF @程式語言 <> '' SET @條件 += N' OR (X3.程式語言 LIKE ''%'+@程式語言+'%'')';
SET @參數 = N'@函式名稱 NVARCHAR(255),@程式語言 NVARCHAR(255),@起始行 INT,@結束行 INT';
SET @語法 = REPLACE(@語法,'{0}',@條件)
SET @語法2 = REPLACE(@語法2,'{0}',@條件)
SET @語法 = REPLACE(@語法,'(1=1) OR','(1=1) AND')
SET @語法2 = REPLACE(@語法2,'(1=1) OR','(1=1) AND')
-- 分頁
IF @起始行 > @結束行
BEGIN
SET @起始行 = 0
SET @結束行 = 0
END
IF @起始行 <> 0 AND @結束行 <> 0
BEGIN
SET @語法 = REPLACE(@語法,'{1}','WHERE ROWID BETWEEN @起始行 AND @結束行')
END
ELSE
BEGIN
SET @語法 = REPLACE(@語法,'{1}','')
END
PRINT @語法
EXEC Sp_ExecuteSQL @語法,@參數
,@函式名稱 = @函式名稱
,@程式語言 = @程式語言
,@起始行 = @起始行
,@結束行 = @結束行
-- 總筆數
SET @顯示欄位 = N'SELECT ISNULL(COUNT(*),0) AS 總數量'
SET @語法2 = @顯示欄位 + @語法2
SET @語法2 = REPLACE(@語法2,'{1}','')
PRINT ''
PRINT @語法2
EXEC Sp_ExecuteSQL @語法2,@參數
,@函式名稱 = @函式名稱
,@程式語言 = @程式語言
,@起始行 = @起始行
,@結束行 = @結束行
END
--SELECT *
-- FROM [活動資料].[dbo].[基金會歌曲_會員]
-- where 地址 like '%[台中市]%'
--select tblA.* from [活動資料].[dbo].[xxx_會員] tblA join
--(
-- select '%桃園%' as c1 union select '%台中%' as c1 union select '%基隆%' as c1
--) tblB on tblA.地址 like tblB.c1;
--select *
--from 目錄 as a
-- left join 內容 as b on a.編號=b.編號
-- left join 搜尋目標 as c on a.目錄 like c1 or b.內容 like c1
-- 數量 int
-- 條件1 nv
-- 條件2 nv = ''
-- 條件3 nv = ''
一、先用Vistual Studio Utimate的SQL Schema比對測試機DB和正式機DB差異。
二、與PM討論是否需要停機
SELECT B.name,A.ENCRYPTED,A.TEXT
FROM SYS.syscomments A,SYS.sysobjects B
http://vito-sql.blogspot.tw/2013/10/blog-post_1063.html
http://tharakaweb.com/tag/sql-2012/
