愚者の経験

「また今度」はほとんどこない

月別アーカイブ: 8月 2014

[SQL Server][SQL][Tips]cteを使おう!としてみたが難しい。その3

これを使ってSplitできるんじゃないと思って考えました。
開始位置と終了位置を返すselect文をcteで
初期のアンカーメンバーを1と最初の区切り文字の場所として
区切り文字+1と次の区切り文字を区切り文字がなくなるまで再帰します。
そうしてできたクエリでsubstringすれば一発でSplitの出来上がり。
と思ったのですがすでにやってる人いましたのでそれを載せます。
参考URL:http://www.wisesoft.co.uk/scripts/t-sql_cte_split_string_function.aspx

CREATE FUNCTION fnSplitString(@str nvarchar(max),@sep nvarchar(max))
RETURNS TABLE
AS
RETURN
	WITH a AS(
		SELECT CAST(0 AS BIGINT) as idx1,CHARINDEX(@sep,@str) idx2
		UNION ALL
		SELECT idx2+1,CHARINDEX(@sep,@str,idx2+1)
		FROM a
		WHERE idx2>0
	)
	SELECT SUBSTRING(@str,idx1,COALESCE(NULLIF(idx2,0),LEN(@str)+1)-idx1) as value
	FROM a

やはり目のつけどころが皆さん違いますね。
これは作業用のテーブルにinsertとかしませんので個人的には相当スッキリします笑

[SQL Server][SQL][Tips]cteを使おう!としてみたが難しい。その2

cteに機能の再帰ですが実際何に使えるのかというとほとんど
・連続データの作成
になると思います。マスタなどに親コードみたいなみたいなものが
存在し、順番に追って最上位を取得するという場合にも再帰は役に立ちます。
が、あまりそのような状況に巡り合わないのでそれの説明は省きます。

with cte(inc) as (
	select 1
	union all
	select inc+1
	from cte
	where inc<30
)
select *
from cte

これで1~30までの連続データができます。
年間カレンダーを作るときなどにも使えますね。
注意しなければならない点としては
・100を超える再帰をする場合そのままではエラーになるのでoption (maxrecursion N)が必要

with cte(inc) as (
	select 1
	union all
	select inc+1
	from cte
	where inc<200
)
select *
from cte
option (maxrecursion 0)

・パラメータ等使って回転数を制御した場合等で0行を返すことはできない

declare @maxinc int
set @maxinc=0;
with cte(inc) as (
	select 1
	union all
	select inc+1
	from cte
	where inc<@maxinc
)
select *
from cte

こういうのですね…初期アンカーがある(select 1)ので
1行は必ず返ります。最後のselectにwhere句つけてください。