XiaoLian

因工作需要將Table前面加入流水號序號，作為識別或排序之用途。 SQL 參考語法： --原始Table select * from [dbo].[BUYER]  GO --寫入暫時Table(#NewBUYER)  select  ID_Num= IDENTITY(INT,1,1) , * INTO #NewBUYER from [dbo].[BUYER] --查詢並排序 SELECT * FROM #NewBUYER order by ID_Num; GO --刪除暫時Table DROP TABLE #NewBUYER; 備註：IDENTITY 寫法有兩種，一種是上述的寫法，另一種是 IDENTITY(INT, 1,1) AS ID_Num    執行畫面：

工作之需求，將欄位中的資料，以底線分隔之文字 ， 變成多欄位顯示。 SQL 參考語法 ： select RefSysArgID, SUBSTRING(RefSysArgID,1,CHARINDEX('_',RefSysArgID)-1) as RefSysArgID_0 , SUBSTRING(RefSysArgID,CHARINDEX('_',RefSysArgID)+1,len(RefSysArgID)) as RefSysArgID_1 , RefSysArgName, RefSysArgRemark from dispRefSysArg where RefSysArgParentID=N'MSS_HWMS_AREA' 執行畫面： SQL 參考語法2： --Ex ： Hello_Jason select SUBSTRING( 'Hello_Jason' ,1,CHARINDEX('_', 'Hello_Jason' )-1) '前面的文字' , SUBSTRING( 'Hello_Jason' ,CHARINDEX('_', 'Hello_Jason' )+1,len( 'Hello_Jason' )) '後面的文字' 執行畫面：

在SQL Server 2000中，欲修改系統資料目錄內容時，通常 預設不允許被更新 的，此時會出現 『系統資料目錄的特定更新並未啟用。系統管理員必須重新組態SQL Server來啟用它。』 錯誤。 必須透過下面語法啟用功能： 啟用系統資料目錄的特定更新語法： sp_configure 'allow updates','1' reconfigure with override go 關閉系統資料目錄的特定更新語法： sp_configure 'allow updates','0' reconfigure with override go SQL Server 2000為例： 欲變更tmpresdb之建立日期 select * from sysobjects where name='tmpresdb' 步驟： 變更後：

/*列出重複出現一次以上的資料*/ SELECT 欄位名稱, COUNT(*) FROM Table_Name GROUP BY 欄位名稱 HAVING COUNT(*) > 1 COUNT(*) /*重複出現的次數*/ Ex1：找出客戶資料表中有哪些 客戶名稱重複 SELECT Company,COUNT(*) as counts FROM [Customer] GROUP BY [Company] HAVING COUNT(*) > 1 Ex2：客戶 aim 是否在客戶資料表中重複 SELECT Company,COUNT(*) as counts FROM [Customer] GROUP BY [Company] HAVING COUNT(*) > 1 and [Company] ='aim'

工作之需求，將欄位中的資料，以逗號分隔之數字作加總。 原始資料內容如下：   首先，建立 function： /* @str：資料內容。 @split：以什麼符號或字元作為分割之依據。 */ create function func_splitstring (@str nvarchar(50),@split varchar(10) ) returns varchar(1000) as begin    /*宣告 */     declare @i int    declare @s int    declare @j int    /*小計 */    /*設定初始值 */    set @i=1    set @s=1    set @j=0        while(@i>0)     begin        set @i=charindex(@split,@str,@s)       if(@i>0)             begin                select   @j =  @j +  cast(substring(@str,@s,@i-@s) as int)             end        else begin                select   @j =  @j +  cast(substring(@str,@s,len(@str)-@s+1) as int)     ...

由於工作上需要，將Table中相同的父ID【Parent_Id 欄位】，找出其最新一筆子ID【id 欄位】內容。 原始資料內容如下： 方法一（create function）： create function Concat (@Col1 int) returns varchar(1000) as begin declare @resultStr varchar(1000) select top 1  @resultStr =[id] + ''  from  dbo.CheckList_Change where Parent_Id = @Col1 order by Create_Date desc return @resultstr end Select Parent_Id,dbo.Concat(Parent_Id) as id  from dbo.CheckList_Change group by Parent_Id order by Parent_Id 註：For SQL Server 2000以上，下面有產生create function位置畫面 ================================================================================================= 方法二（FOR XML PATH）： SELECT  T1.Parent_Id,(   SELECT top 1 [id] + ''  FROM    dbo.CheckList_Change T2  WHERE  T2.Parent_Id = T1.Parent_Id order by Create_Date desc FOR XML PATH('')) AS [id] FROM  dbo.CheckList_Change T1  GROUP BY  Parent_Id  order by Parent_Id 註：For SQL Server 2005以上，語法可參考 My blog ===========================================...

假設 run_cost 欄位名稱，在Table中資料型態為 Money '插入方法 INSERT INTO [itemcost] (run_cost) VALUES ( CAST('687,123' AS MONEY) ) '更新方法 UPDATE         itemcost SET        run_cost = CAST('687,123' AS MONEY)

在工作上同仁遇到一個關於SQL排序問題，資料是英文+數字〔有小數〕，該如何做排序？ 若用文字型態排序，所得到的結果並非是想要的。 假設欲排序資料 A1.1、A1.2、A1.3…A2.1…A2.10等 觀念作法： 1.首先將英文部份抽離。 【 假設欄位名稱Code_Desc1，substring(Code_Desc1,2,LEN(Code_Desc1)) => S_ID 】 2.接著比對小數點前面數字。 【 cast(SUBSTRING(S_ID,1,CHARINDEX('.',S_ID,1)-1) as money) 】 3.最後比對小數點後面數字。 【 cast(SUBSTRING(S_ID,CHARINDEX('.',S_ID,1)+1,LEN(S_ID)) as money) 】 語法： Select 欄位名稱  from Table order by 小數點前面數字, 小數點後面數字 語法（參考）: select substring(Code_Desc1,2,LEN(Code_Desc1)) as SID, Code_Desc1, Code_Desc2 FROM System_Code Where UpLevelID in('A001', 'A002','B003') order by cast(SUBSTRING(substring(Code_Desc1,2,LEN(Code_Desc1)),1,CHARINDEX('.',substring(Code_Desc1,2,LEN(Code_Desc1)),1)-1) as money), cast(SUBSTRING(substring(Code_Desc1,2,LEN(Code_Desc1)),CHARINDEX('.',substring(Code_Desc1,2,LEN(Code_Desc1)),1)+1,LEN(substring(Code_Desc1,2,LEN(Code_Desc1)))) as money) 執行結果：

CHARINDEX和PATINDEX函數常常用來在一段字符中搜索字符或者字符串。 如果被搜索的字符中包含有要搜索的字符，那麼這兩個函數返回一個非零的整數，這個整數是要搜索的字符在被搜索的字符中的開始位數。 PATINDEX函數支持使用通配符來進行搜索，然而CHARINDEX不支持通佩符。 CHARINDEX： 此函數返回一個整數，返回的整數是要找的字符串在被找的字符串中的位置。假如CHARINDEX沒有找到要找的字符串，那麼函數整數「0」。 語法： CHARINDEX ( expression1 , expression2 [ , start_location ] )  說明： expression1是要到expression2中尋找的字符中，start_location是CHARINDEX函數開始在expression2中找expression1的位置。 Ex： SELECT CHARINDEX('Jason', 'Hello~Welcome to Jason blog') => 18 PATINDEX： 函數返回字符或者字符串在另一個字符串或者表達式中的起始位置，PATINDEX函數支持搜索字符串中使用通配符，這使PATINDEX函數對於變化的搜索字符串很有價值。 語法： PATINDEX ( '%pattern%' , expression ) 說明： pattern是你要搜索的字符串，expression是被搜索的字符串。一般情況下expression是一個表中的一個字段，pattern的前後需要用「%」標記，除非你搜索的字符串在被收縮的字符串的最前面或者最後面。 Ex： SELECT PATINDEX('%Jason%', 'Hi~My name is Jason Lian') => 15

根據不同型態做除法（/）動作，得到結果不一，如果 被除數為整數（int型態）做除法（/）動作，得到的結果都是整數，若要有小數位的話，必須將被除數（int型態）轉換成float， 然後再做除法動作即可。 int 型態： select 11 /3  value       --> 結果： 3 轉換成float 型態： select CAST(11 AS float) /3  value --> 結果： 3.6666666666666665 無條件進位-ceiling函數 select  ceiling(CAST(11 AS float) /3) as  value --> 結果： 4.0 轉換成int 型態 select cast(ceiling(CAST(11 AS float) /3) as int) as value --> 結果： 4

通常在撰寫程式中，會用字串(yyyymmdd EX:20121205)來代表日期，字串的好處方便直接相加減與拆開合併及轉換(西元<->民國)等運用，然而要寫入日期格式欄位時，就必須做轉換。轉換方是很多種， 一種是將字串(yyyymmdd)轉換成(yyyy/mm/dd)或(yyyy-mm-dd)格式填入;另一種最快的方式直接運用SQL內建函數做轉換，格式為CONVERT(datetime, 'yyyymmdd')即可 。前提之下字串(yyyymmdd)必須要是合法的日期。所以在寫入前，最好先做判斷。 PS:要測試(yyyymmdd)是否為合法日期可以下 ISDATE函數 select ISDATE ('20130105') as T 輸出： 1 代表合法日期 select ISDATE ('20121131') as T 輸出： 0 代表不合法日期 字串[西元(yyyymmdd)]轉日期格式 select CONVERT(datetime, '20121205') as date_1 輸出： 2012-12-05 00:00:00.000 參考資料： http://msdn.microsoft.com/zh-tw/library/ms187347.aspx MSDN ISDATE() 說明 註：Convert 日期用法可參考 My blog

SQL 常用字串處理語法說明：包含 substring、left、right、upper、lower、ltrim、rtrim 取字串中部分字元 語法： substring(欄位, 起始字元, 取幾字元) Ex: SELECT  substring('A123456789',4,6) => 345678 取左側字元 語法： left(欄位, 位數) Ex: SELECT LEFT('ABCDEFG',5) => ABCDE 取右側字元 語法： right(欄位, 位數) Ex: SELECT RIGHT('ABCDEFG',4) => DEFG 字串小寫轉換大寫 語法： upper(欄位) Ex: SELECT upper('jason')    => JASON 字串大寫轉換小寫 語法： lower(欄位） Ex: SELECT lower('JASON BLOG')   => jason blog 去除左邊無謂空白 語法： ltrim(欄位) Ex: SELECT LTRIM('  Hello')  => "Hello" 去除右邊無謂空白 語法： rtrim(欄位) Ex: SELECT RTRIM('Hello World.  ')   => "Hello World." 參考資源:  http://technet.microsoft.com/zh-tw/library/ms181984.aspx SQL 字串處理函式

之前從未想過這問題，要不是友人提問，然後用Google查詢相關資料，原來要 用case when去判斷要order by 的欄位 Ex: (以"英文Table"欄位作排序) declare @col varchar(20) set @col='English'  -- @col='Chinese' 以"中文Table"欄位作排序 select * from dbo.UDF_Info order by            case @col when 'English' then TableName                              when 'Chinese' then CTableName          end [英文Table] 欄位作排序 [中文Table] 欄位作排序

識別前 20 個在讀取 I/O 時耗用最多資源的查詢    SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,         ((CASE qs.statement_end_offset           WHEN -1 THEN DATALENGTH(qt.text)          ELSE qs.statement_end_offset          END - qs.statement_start_offset)/2)+1), qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.min_logical_reads, qs.max_logical_reads, qs.total_elapsed_time, qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time, qs.last_execution_time FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt WHERE qt.encrypted=0 ORDER BY qs.total_logical_reads DESC 執行結果(參考): 列出執行速度較慢的SQL statement select top 50 * from ( SELECT    SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs...

觀念 : 利用程式化方式，將冗長SQL字串做有效率的處理，易於管理維護及彈性重覆使用 作法 : 找出共同規則及相似語法，搭配迴圈及變數，並使用 print 方式輸出變數於畫面上，            以方便Debug，最後拼出完整SQL字串，用 Execute 執行產生結果 範例 : 列出該年度內公司同仁特休統計分析            粉紅色框框是說明兩者相同之處            Print 說明SQL字串最後呈現與一般傳統寫法相同            Execute 兩者執行結果相同 程式化寫法 : 一般傳統寫法 : 執行結果 :

語法: Case when 欄位=值1 then 文字1 when 欄位=值2 then 文字2 else null End Ex: SELECT T0.[U_EmpID], T0.[firstName], Case when T0.Sex='F' then '女' when T0.Sex='M' then '男' else null End as N'性別', Case when  T0.martStatus='S' then '未婚' when T0.martStatus='M' then '已婚' else null End as N'婚姻狀態' , Case when   T0.dept=-2 then '管理部' when T0.dept = 4 then '業務一部'  when T0.dept=5 then '業務二部'            when T0.dept=6 then '資材部' when T0.dept=7 then '研發部' when T0.dept=8 then '工程部'            when T0.dept=9 then 'FAE' when T0.dept=11 then '中國業務部' else null End as N'部門',  Case when   T0.Status=1 then '外調' when T0.Status=2 then '在職' when T0.Status=3 then '離職'             when T0.Status=4 then '考核' else null End as N'就職狀態' FROM OHEM T0 WHERE T0.citizenshp = 'CN'  and T0.Stat...

觀念一:在SQL語法內加入一筆空白Item  SqlStr="select  0 as 縣市代號,'請選擇' as 縣市名稱 from dbo.縣市 " & _              " UNION " & _               "select 縣市代號,縣市名稱 from dbo.縣市 " & _              "order by  縣市代號,  縣市名稱 desc" 說明：第一句"select" 代表新增空白列              利用 UNION 方式將兩個Table合在一起             "order by"視情況而定不一定要加；為了要讓空白列放在第一筆 觀念二:在控制項內加入一筆空白Item If IsPostBack = False Then             With DropDownList1                 .DataSource = SqlDataSource1   '資料來源可以 DataTable, DataView 格式            ...

在最前面插入一欄自動編號 select Row_Number() over ( order by id) as RowNum,id,firstName,lastName from dbo.USERS 分頁(Paging)寫法 假設取第11筆~第20筆資料 select * from ( select row_number() over ( order by id) as RowNum,id,firstName,lastName from dbo.USERS ) as NewTable where RowNum >= 11 and RowNum<= 20

getdate() '代表今天日期 星期日代表一周的第一天 星期六代表一周的最後一天 格式 : SELECT DATEADD(week, DATEDIFF(week, '', getdate() ), -1 ) as 星期日 SELECT DATEADD(week, DATEDIFF(week, '', getdate() ), 0 ) as 星期一 OR SELECT DATEADD(week, DATEDIFF(week, '', getdate() ), '' ) as 星期一 SELECT DATEADD(week, DATEDIFF(week, '', getdate() ), 1 ) as 星期二 SELECT DATEADD(week, DATEDIFF(week, '', getdate() ), 2 ) as 星期三 SELECT DATEADD(week, DATEDIFF(week, '', getdate() ), 3 ) as 星期四 SELECT DATEADD(week, DATEDIFF(week, '', getdate() ), 4 ) as 星期五 SELECT DATEADD(week, DATEDIFF(week, '', getdate() ), 5 ) as 星期六 Ex:今天是2011/7/12 則上面會依序show 星期日 ~ 星期六 星期日            星期一            星期二           星期三            星期四           星期五         ...

用途: 通常用在計數器上面,例如:到訪人數,點擊率等 範例語法: Update TableName set counter=counter+1 where Hits='blog' 若要自動加多少只要把上面的+1改成符合自己的即可!