SQLServerでよく利用するSQL文

日本時間を表示する

hoge.sql

#日本標準時は、UTC に 9時間を加算したものなので、次のように DATEADD 関数を利用して取得することができます。
DATEADD(hour, 9, SYSUTCDATETIME())

現在の時刻から年月

hoge.sql

#202209
CONVERT (VARCHAR(6), GETDATE(), 112)

#2022/09/09
FORMAT(GETDATE(), 'yyyy/MM/dd')
FORMAT(dbo.PC_START_END.DATE, 'yyyy/MM/dd')

#12:15:11
FORMAT(dbo.PC_START_END.DATE, 'HH:mm:ss')

#当月の月初-月末のWHERE構文
BETWEEN FORMAT(GETDATE(), 'yyyy/MM/01') AND FORMAT(GETDATE(), 'yyyy/MM/31')

四捨五入して整数化する

hoge.sql

#小数点第一を四捨五入して整数化
CEILING(ROUND(ZEIRITSU, 1))

頭にゼロを付ける

hoge.sql

FORMAT(ISNULL(GZSEQ, 0), '0000')

条件文

hoge.sql

--定番のパターン
CASE WHEN K_DATE < '2018-12-31' THEN ISNULL(SKU , 0) ELSE 0 END

CASE WHEN KBN = 1 OR SID_GZKBN = 3 THEN 'A区分' ELSE '""' END


--単純CASE式
CASE gender
    WHEN '男' THEN 1
    WHEN '女' THEN 2
    ELSE 99
END

--検索CASE式
CASE
    WHEN gender = '男' THEN 1
    WHEN gender = '女' THEN 2
    ELSE 99
END


--IN句を使った条件式
CASE
    WHEN prefecture IN ('北海道') THEN '北海道'
    WHEN prefecture IN ('青森県', '岩手県', '宮城県', '秋田県', '山形県', '福島県') THEN '東北'
    WHEN prefecture IN ('沖縄県') THEN '沖縄'
    ELSE NULL
END

参考：https://qiita.com/sfp_waterwalker/items/acc7f95f6ab5aa5412f3

数値から文字列に変換

hoge.sql

CONVERT (varchar, ISNULL(suti, 0))

CAST(Suchi AS varchar) 

文字を抽出

hoge.sql

/*第一引数(文字列) 第二引数(開始地点) 第三引数(文字数) */
/* 345 */

SUBSTRING('12345678', 3, 3);

文字列の結合

hoge.sql

Name1+Name2

DateTimeを変換する

hoge.sql

--年月にする       202007
FORMAT(SDATE,'yyyyMM')

--年月日にする     20200707
FORMAT(SDATE,'yyyyMMdd') 

--年/月/日にする 2020/07/07
FORMAT(SDATE,'yyyy/MM/dd')

--年/月/日にする 2020年07月07日
FORMAT(SDATE,'yyyy年MM月dd日')

今日からnヶ月前

hoge.sql

DATEADD(month, -2, GETDATE())
DATEADD(year, -2, GETDATE())
DATEADD(day, -2, GETDATE())

DELETE と JOIN を使ってデータを削除するSQL文

JOINを使って結合して条件指定したレコードを削除したい場合は以下のコード

「テーブル名 AS　代替テーブル名」を使うようだ。

hoge.sql

DELETE HJNH FROM TP_HJN_H AS HJNH 
LEFT OUTER JOIN  V_HDB_DRN_1year AS VHDB O
N HJNH.HJN_H_NDEN_DRN = VHDB.HDB_NDEN_DRN 
WHERE(VHDB.HDB_N_S_DATE < DATEADD(month, -2, GETDATE()))

サブクエリ

Fromに引用するSQL文を入れることでビューのように利用できる。

逆クロス集計

UNPIVOT.sql

SELECT
 PAC_SKEY_JYA, PAC_SKEY_SCD, PAC_SKEY_KIK, LEFT(PAC_NO, 9) AS PACCL, PAC_SU
FROM
 M_PAC_01 AS p 
UNPIVOT (PAC_SU FOR PAC_NO IN (PAC_NAI00_KOS, PAC_NAI01_KOS, 
 PAC_NAI02_KOS, PAC_NAI03_KOS, PAC_NAI04_KOS,
 PAC_NAI05_KOS, PAC_NAI06_KOS, PAC_NAI07_KOS, PAC_NAI08_KOS, 
 PAC_NAI09_KOS)) AS unpvt
WHERE                       
 PAC_SU > 0;