資料庫黑洞

紀錄一些同仁曾問過的、或是實用的語法

 

1.取字

(1) 某字串希望倒數第3字，取代為某字元

 

DECLARE @strReplace varchar(50) = 'c';
SELECT LEFT(@str, LEN(@str) - 3) + @strReplace + RIGHT(@str, 2);
--SELECT '10406011c01'

 

(2) 取得某字串在某個字元後的所有字

 

DECLARE @strSplit varchar(50) = ':';
SELECT SUBSTRING(@str, CHARINDEX(@strSplit, @str) + 1, 9999);
--SELECT '136103'

 

(3) 某字串格式: 任意字數的數字-任意字數的英數字-任意字數的數字，希望能取得最後一個 '-' 後面的所有數字

 

DECLARE @strSplit varchar(50) = '-';
DECLARE @strReversed varchar(50) SET @strReversed = REVERSE(@str);
SELECT REVERSE(LEFT(@strReversed, CHARINDEX(@strSplit, @strReversed)-1));
--SELECT '23'

 

2.找出種類筆數

 

 

3.Merge資料並 output

 

USING (SELECT
  AuthorizedBy,
  AuthorizedTo,
  EnrollTypeID
FROM vEnrGrantUserEnrollType) AS source (AuthorizedBy, AuthorizedTo, EnrollTypeID)
  ON (target.AuthorizedTo = source.AuthorizedTo AND target.EnrollTypeID = source.EnrollTypeID)
WHEN NOT MATCHED THEN
  INSERT (AuthorizedBy, AuthorizedTo, EnrollTypeID) VALUES (source.AuthorizedBy, source.AuthorizedTo, source.EnrollTypeID)
WHEN NOT MATCHED BY source AND (IsUsing = 1) THEN
  UPDATE SET IsUsing = 0
  OUTPUT INSERTED.EnrGrantUserEnrollTypeID, $ACTION, DELETED.IsUsing, DELETED.ModifiedDate,
    INSERTED.AuthorizedBy, INSERTED.AuthorizedTo, INSERTED.EnrollTypeID, INSERTED.IsUsing, INSERTED.ModifiedDate
  INTO tEnrGrantUserEnrollTypeLog;

 

4.update資料並 output

 

SET AuthorizedBy = ?,
    IsUsing = ?,
    ModifiedDate = GETDATE()
OUTPUT INSERTED.EnrGrantUserEnrollTypeID, 'UPDATE', DELETED.IsUsing, DELETED.ModifiedDate,
    INSERTED.AuthorizedBy, INSERTED.AuthorizedTo, INSERTED.EnrollTypeID, INSERTED.IsUsing, INSERTED.ModifiedDate
INTO tEnrGrantUserEnrollTypeLog
WHERE EnrGrantUserEnrollTypeID = ?;

 

5.簡單的create + PK + constraint default

 

  ID int IDENTITY (1, 1) NOT NULL CONSTRAINT PK_資料表 PRIMARY KEY,
  money decimal(9, 2) NOT NULL CONSTRAINT DF_資料表 DEFAULT 0
);

 

6.欄位A=''，則欄位B, 欄位B='' 則欄位C

 

  COALESCE(
    CASE
      WHEN 欄位A = '' THEN NULL
      ELSE 欄位A
    END,
    CASE
      WHEN 欄位B = '' THEN NULL
      ELSE 欄位B
    END,
    欄位C) AS IDNO
FROM 資料表;

 

7.欄位A null，則欄位B, 欄位B null， 則欄位C

 

  COALESCE(欄位A, 欄位B, 欄位C) AS IDNO
FROM 資料表;

 

8.case when + in + like

 

  CASE
    WHEN col IN ('護理', '藥學') THEN '醫學類'
    WHEN col LIKE '%技術%' THEN '科技類'
    WHEN col = 'MBA' THEN '管理類'
    ELSE col
  END AS IDNO
FROM 資料表;

 

9.快速建立一張與資料表同結構的表資料表2(不含資料)

 

 * INTO 資料表2 FROM 資料表 WHERE 1 = 0;

 

10.欄位多列合併一欄 (以 ',' 串聯)

 

 (
  SELECT col + ',' FROM 資料表 FOR xml PATH ('')
) AS IDNO

 

11.數字表 (0~2048)

 

 DISTINCT NUMBER 
FROM master.dbo.spt_values
WHERE NUMBER BETWEEN 0 AND 2048

 

12.自訂交易，出錯時整批回滾 (ROLLBACK)

 

	BEGIN TRAN
		--DO SOMETHING
	COMMIT
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0
		ROLLBACK

	DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
 
    SELECT
        @ErrorMessage=ERROR_MESSAGE(),
        @ErrorSeverity=ERROR_SEVERITY(),
        @ErrorState=ERROR_STATE();
 
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;

 

13.不存在資料則 INSERT

 

BEGIN
	--DO SOMETHING
	INSERT 資料表(ID) VALUES(1);
END;

 

14.同時計算出男性,女性,總人數的方法

 

 
	SUM(CASE SEX WHEN 'M' THEN 1 ELSE 0 END) AS 'MaleCount',
	SUM(CASE SEX WHEN 'F' THEN 1 ELSE 0 END) AS 'FemaleCount',
	SUM(CASE WHEN SEX IN ('F', 'M') THEN 1 ELSE 0 END) AS 'AllCount'
FROM 資料表;

 

15.指定索引

 

 a.id, b.id
FROM a WITH (INDEX (IX_a_id))
JOIN b ON a.id = b.id;

 

16.如果有兩個條件去查區間，但是第二個條件是空值的時候 要怎樣讓他也查的到

 

where code >=ISNULL('A1040330-010','') and code <=ISNULL(null,'')

--第一次給的答案
(CASE WHEN code IS NULL THEN 1
  WHEN code  >=ISNULL('A1040330-010','')  THEN 1 
ELSE 0 END) = 1

--第二次 (可行但效能差)
where (@param1 IS NULL OR [code] >= @param1)
  AND (@param2 IS NULL OR [code] <= @param2)

--第三次 (確認需求:英文+XXXX+-+XXXX，改善為 SARG)
WHERE ([code] >= ISNULL(@param1, 'A0000000-000'))
  AND ([code] <= ISNULL(@param2, 'Z9999999-999'))

 

本著作係採用創用 CC 姓名標示-相同方式分享 4.0 國際 授權條款授權，文章歡迎轉載，請註明出處，謝謝~~~