2008/02/27

【SQL】最近便利だと思ったSQL

--数値を文字列に変換
SELECT
STR(1)
--結果:' 1'
--指定文字を指定回数繰り返す
SELECT
REPLICATE('0', 10)
--結果:'0000000000'
--指定文字列を指定した文字列に置き換える
SELECT
REPLACE( '00010000010000100', '0', '')
--結果:'111'
 --右端から文字列を切り取る
SELECT
RIGHT('0000000123', 3)
--結果:'123'
--変数を宣言する
DECLARE @Number INT
SET @Number = 123
--0埋めを行う(例、10桁)
SELECT
RIGHT(REPLICATE('0', 10) + REPLACE(STR(@Number), ' ', ''), 10)
--結果: '0000000123'
--DateTime型をInt型へ変換する('2008/01/01 00:00:00' → 20080101)
DECLARE @Date DATETIME
SET @Date = '2008/01/01 00:00:00'
SELECT
CAST(CONVERT(VARCHAR, @Date, 112) AS INT)
--結果: 20080101
--'2008/01/01 00:00:00'から月末の日付を取得
SELECT
DATEADD(DAYOFYEAR, -1, DATEADD(MONTH, 1, @Date) )
--結果:'2008-01-31 00:00:00.000'
--現在の時刻を取得
SELECT
GETDATE ()
--結果:'2008-02-25 13:01:24.903'
--任意の日付から月末日を取得する
SELECT
DATEADD(DAYOFYEAR, -1, DATEADD(MONTH, 1, CONVERT(DATETIME, CONVERT(VARCHAR(6), GETDATE(), 112) + '01' + ' 00:00:00')))
--結果:'2008-02-29 00:00:00.000'
 --int型の任意の日付から月末を取得する
SELECT
CAST(CONVERT(VARCHAR, DATEADD(DAYOFYEAR, -1, DATEADD(MONTH, 1, CONVERT(DATETIME, CONVERT(VARCHAR(6), REPLACE(STR(20080215), ' ', '')) + '01 00:00:00', 112))), 112) AS INT)
--結果:20080229
--int型の日付をDatetime型に変換する
SELECT
CONVERT(DATETIME, CONVERT(VARCHAR(8), 20090220))
--2009-02-20 00:00:00.000
--年齢計算
SELECT
FLOOR((CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT) - 19840110)/10000) AS AGE
--25
--条件分岐
--詳しくは「CASE式のススメ」
--
http://www.geocities.jp/mickindex/database/db_case.html
SELECT
Tbl.ID
,CASE COALESCE(Tbl.DELFLG, 0)
  WHEN 1 THEN '●'
  ELSE NULL
END AS [削除済]
FROM
(
  SELECT 1 AS ID, NULL AS DELFLG
  UNION
  SELECT 2 AS ID, 1 AS DELFLG
  UNION
  SELECT 3 AS ID, 0 AS DELFLG
) AS Tbl
ORDER BY
Tbl.ID
--ID    削除済
--1    NULL
--2    ●
--3    NULL
追記:
少し見やすくしました。
ちなみに環境はMSSQLです。MySQL等では動かないものが多い。

SQLパズル 第2版 プログラミングが変わる書き方/考え方