一定期間更新がないため広告を表示しています
スポンサーサイト
- 2022/06/17
- -
- -
- -
- 記事URL https://plus-sys.jugem.jp/?eid=
Twitterはじめました。プラプラ | Twitter
【SQLServer】超シンプル!再帰CTE(共通テーブル式)で連続データを作成する。
- 2013/06/17
- SQLServer
- comments(1)
- trackbacks(0)
- 記事URL https://plus-sys.jugem.jp/?eid=402
SQLでデータ取得する際、
「抽出データは歯抜けありで出力は20件固定」
といったような、
抽出データ数が可変、出力データ数は固定といった要件がたまにあります。
このような場合、出力用の20件のキーをもったテーブルを作成し、
select 抽出データ.* from 抽出データ left join 出力用の20件のキーデータ ・・・
のようなSQLで対応していました。
ただ、出力用の20件のキーを保持するテーブルを作成するのは、
一時テーブルにせよ、永続性のあるテーブルにせよ、
ちょっとわかりくいので、もっとすっきりした方法はないものかと
ネット上を探していましたところ、
ヒントとなるエントリを見つけました。
(質問)
連続値を取得するクエリ
(回答)
解として、再帰CTE(共通テーブル式)を使う方法が提示されていました。
ちなみにCTEは、SQL99に準拠し、SQLServerではSQLServer2005以降から
実装されています。
(実行例)
SQLServerのCTEでは再帰処理を行うことができます。
再帰CTEで任意のパターンのデータを作成できれば、
別途テーブルを用意する必要はありません。
これはすばらしい!
再帰CTEの書き方は、以下のとおり。
from msdn
共通テーブル式を使用する再帰クエリ
WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition -- Anchor member is defined.
UNION ALL
CTE_query_definition -- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name
CTE内は、アンカーメンバ(最初の反復)と再帰メンバ(後続の反復)で
構成され、アンカーメンバと再帰メンバはUNION ALLで結合します。
また、アンカーメンバは、ベーステーブルへの参照のみを含むことができ、
再帰メンバは、CTE自身への参照が含まれている必要があります。
注意点としては、再帰のコードをミスると無限ループに
なってしまうこと。
このため、SQLServerの既定では、再帰処理が100回を超えると
以下のエラーメッセージを表示し、処理が停止します。
「メッセージ530 レベル16 状態1 行1
ステートメントが終了しました。ステートメントの完了の前に
最大再帰数100に達しました。」
この最大再帰数は、オプションを指定することにより
任意の値に変更可能です。(ゼロを指定すると、無限)
(指定例)
OPTION (MAXRECURSION 9999) --9999回まで
OPTION (MAXRECURSION 0) --無限
1から30までの連番をもったテーブルを作成してみます。
WITH gyo_no AS
(
SELECT 1 AS cur_no -- アンカーメンバ
UNION ALL
SELECT cur_no + 1 -- 再帰メンバ
FROM gyo_no
WHERE gyo_no.cur_no < 30
)
SELECT * FROM gyo_no;
ちゃんとできてますね。
ちなみに、再帰CTEに関しては、
SQL Server 2005 ストアドプロシージャプログラミング のP223〜224
にコードサンプル、説明、制限が記載されています。あわせてどうぞ。
>>SQL Server 2005 ストアドプロシージャプログラミング (SQL Server Books)
あと、ネット上では、コードサンプルとして以下のようなエントリもありました。
ご参考まで。
(再帰で階層データを取得する例)
再帰 CTE を使って、階層構造のデータを取得する
共通テーブル式を使用する再帰クエリ
(再帰でカレンダデータを取得する例)
再帰クエリでカレンダーテーブルを作成する
(おまけ)
へんてこな形ですが、これは指マウスという代物。
プレゼン時に使用すると、パソコンから離れて
自由に動き回れるようになるという優れものです。
安さと「サンワダイレクト」というのがおすすめの理由。2980円。
>>サンワダイレクト リングマウス 指マウス 指輪の形のワイヤレスマウス 【TVで紹介されました! 】 400-MA031 サンワダイレクト
フリック指マウス。マウスを空中で操作。
今、話題になっています。
対応OS Windows 7,Vista,XP / Mac OS。レーザーマウス機能もあり。
在庫あと1コ。(2013/06/17)
>>[高性能ジャイロを搭載]指先で操作できるマウス!フリック指マウス:USAIRMSW
「抽出データは歯抜けありで出力は20件固定」
といったような、
抽出データ数が可変、出力データ数は固定といった要件がたまにあります。
このような場合、出力用の20件のキーをもったテーブルを作成し、
select 抽出データ.* from 抽出データ left join 出力用の20件のキーデータ ・・・
のようなSQLで対応していました。
ただ、出力用の20件のキーを保持するテーブルを作成するのは、
一時テーブルにせよ、永続性のあるテーブルにせよ、
ちょっとわかりくいので、もっとすっきりした方法はないものかと
ネット上を探していましたところ、
ヒントとなるエントリを見つけました。
・再帰CTEを使うと連続データが簡単に作成できる?
(質問)
連続値を取得するクエリ
T-SQLで、テーブルを参照せず、1から30までの雨に連続する値のをもつ30行を取得するには、どのようなクエリになりますでしょうか?
やりたいことは、今日から過去30日間の集計データを表にするということで、そのために、今日から過去30日の日付の行をもつ日付列を用意して、Left Joinしようとしています。
(回答)
WITH DAYS AS
(SELECT CURRENT_TIMESTAMP AS YMD
UNION ALL
SELECT DATEADD(Y, -1, YMD)
FROM DAYS
WHERE DAYS.YMD >= DATEADD(y, -30, CURRENT_TIMESTAMP)
)
SELECT * FROM Table1 LEFT JOIN DAYS ON ...;
解として、再帰CTE(共通テーブル式)を使う方法が提示されていました。
ちなみにCTEは、SQL99に準拠し、SQLServerではSQLServer2005以降から
実装されています。
(実行例)
SQLServerのCTEでは再帰処理を行うことができます。
再帰CTEで任意のパターンのデータを作成できれば、
別途テーブルを用意する必要はありません。
これはすばらしい!
・再帰CTEの書き方
再帰CTEの書き方は、以下のとおり。
from msdn
共通テーブル式を使用する再帰クエリ
WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition -- Anchor member is defined.
UNION ALL
CTE_query_definition -- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name
CTE内は、アンカーメンバ(最初の反復)と再帰メンバ(後続の反復)で
構成され、アンカーメンバと再帰メンバはUNION ALLで結合します。
また、アンカーメンバは、ベーステーブルへの参照のみを含むことができ、
再帰メンバは、CTE自身への参照が含まれている必要があります。
注意点としては、再帰のコードをミスると無限ループに
なってしまうこと。
このため、SQLServerの既定では、再帰処理が100回を超えると
以下のエラーメッセージを表示し、処理が停止します。
「メッセージ530 レベル16 状態1 行1
ステートメントが終了しました。ステートメントの完了の前に
最大再帰数100に達しました。」
この最大再帰数は、オプションを指定することにより
任意の値に変更可能です。(ゼロを指定すると、無限)
(指定例)
OPTION (MAXRECURSION 9999) --9999回まで
OPTION (MAXRECURSION 0) --無限
・実際に再帰CTEを使ってみる
1から30までの連番をもったテーブルを作成してみます。
WITH gyo_no AS
(
SELECT 1 AS cur_no -- アンカーメンバ
UNION ALL
SELECT cur_no + 1 -- 再帰メンバ
FROM gyo_no
WHERE gyo_no.cur_no < 30
)
SELECT * FROM gyo_no;
ちゃんとできてますね。
ちなみに、再帰CTEに関しては、
SQL Server 2005 ストアドプロシージャプログラミング のP223〜224
にコードサンプル、説明、制限が記載されています。あわせてどうぞ。
>>SQL Server 2005 ストアドプロシージャプログラミング (SQL Server Books)
あと、ネット上では、コードサンプルとして以下のようなエントリもありました。
ご参考まで。
(再帰で階層データを取得する例)
再帰 CTE を使って、階層構造のデータを取得する
共通テーブル式を使用する再帰クエリ
(再帰でカレンダデータを取得する例)
再帰クエリでカレンダーテーブルを作成する
(おまけ)
へんてこな形ですが、これは指マウスという代物。
プレゼン時に使用すると、パソコンから離れて
自由に動き回れるようになるという優れものです。
安さと「サンワダイレクト」というのがおすすめの理由。2980円。
>>サンワダイレクト リングマウス 指マウス 指輪の形のワイヤレスマウス 【TVで紹介されました! 】 400-MA031 サンワダイレクト
フリック指マウス。マウスを空中で操作。
今、話題になっています。
対応OS Windows 7,Vista,XP / Mac OS。レーザーマウス機能もあり。
在庫あと1コ。(2013/06/17)
>>[高性能ジャイロを搭載]指先で操作できるマウス!フリック指マウス:USAIRMSW
Twitterはじめました。プラプラ | Twitter
スポンサーサイト
- 2022/06/17
- -
- -
- -
- 記事URL https://plus-sys.jugem.jp/?eid=
Twitterはじめました。プラプラ | Twitter
[関連エントリ 最新5]-
[Google PR]
[最新エントリ 5]-
- コメント
- これは使えますね。増分の間隔も再帰メンバで調整可能ですし。
-
- sazi
- 2017/09/08 2:01 PM
- コメントする(お気軽にどうぞ)
- この記事のトラックバックURL
- トラックバック
- カテゴリー
-
- Vista (15)
- AccessTips (74)
- 雑記 (68)
- セキュリティー (23)
- おもしろいサービス (5)
- tool (12)
- あまっちゃお (4)
- コンピュータ (66)
- CakePHP (25)
- VAIOTypeP[バイオタイプピー] (12)
- SQLServer (25)
- iPad (36)
- Apple Watch (5)
- AWS認定 (1)
- Azure (4)
- e-mobile (8)
- iOSアプリ (18)
- iPhone (2)
- iPhoneX (2)
- ipod touch (3)
- kindle (18)
- Monaca (1)
- Nexus7 (11)
- VAIO PRO (1)
- VAIO X[バイオ エックス] (1)
- VAIO Z (5)
- Windows10 (1)
- Windows8 (7)
- Windows9 (1)
- XPERIA Z1 (1)
- XperiaZ (6)
- 【全記事一覧】 (1)
- おすすめ本まとめ (2)
- クラウド (1)
- セール (3)
- デジモノ (24)
- 本(BOOK) (9)
- 腕時計 (1)
- プロフィール
- links
- recent comment
-
- 【Access】コンボボックス対応版。超簡単な帳票フォームでの矢印上下キー行移動。
⇒ 山田 裕樹 (04/28) - 【Access】vbaでhmacが正しく計算できた!!
⇒ まさ (06/01) - 【Access】AccessReportMailメール送信時のポートを25から587へ
⇒ わら (05/31) - 【SQLServer】超シンプル!再帰CTE(共通テーブル式)で連続データを作成する。
⇒ sazi (09/08) - 【あまっちゃお】Amazon商品検索「あまっちゃお」プロジェクト始動。Ver00-06-00 β
⇒ カメちゃん (07/31) - 【Access】あれ?DoCmd.RunCommand acCmdSaveRecordの内部仕様が変わった?
⇒ 花姉 (03/17) - 【Access】実行中のmdb(自分自身)をバックアップコピーする方法。
⇒ 亀 (02/06) - 【雑記】Amazonでの注文時、TMGさん発送にご注意を。納期を守れない可能性あり!
⇒ 管理人 (01/31) - 【雑記】Amazonでの注文時、TMGさん発送にご注意を。納期を守れない可能性あり!
⇒ あ (01/31) - 【雑記】Amazonでの注文時、TMGさん発送にご注意を。納期を守れない可能性あり!
⇒ K (11/13)
- 【Access】コンボボックス対応版。超簡単な帳票フォームでの矢印上下キー行移動。
- recent trackback
-
- 【Access】2003安全でない式がブロックされていませんとセキュリティーレベル。
⇒ awgs Foundry (10/29) - 【VAIOtypeP】液晶保護シートは、新車のカバーではない。レイアウト社液晶/天板保護フィルムセットRT-VP1FS1
⇒ XMLがキライ。 (04/09) - Vistaから、LinkStation(NAS)にアクセスできません。
⇒ そのほかいろいろ (09/06)
- 【Access】2003安全でない式がブロックされていませんとセキュリティーレベル。
- sponsored links