備忘録

かなりウカツだったんですが、いつの間にやらSQLServerでは使えるようになっていたのですな！

[TSQL]再帰クエリ
再帰クエリその2
再帰SQL — Let's Postgres

Oracleでは、共通表式のWITH句は使えますが、WITH RECURSIVEによる再帰SQLはできませんでしたね。
かろうじてCONNECT BYによる階層問い合わせが出来ましたが、件数多いと全然帰ってこなかったんで、今までほとんど遣ったことないです。

SQLServerをインストール後、正常にインストールできたのになぜかパフォーマンスカウンタが登録されていないことがあった。
その際の手動登録の仕方が以下↓
SQL Server Desktop Engine 2000 のインストールがパフォーマンス モニタ カウンタの登録時に失敗する

pmlのメーリングリストと以下のブログからまとめたメモ。

数値は SUM() 関数で集計、文字列を集計的に結合するには( Transact-SQL で aggregate concatenation )

文字列を集計的に結合する(ユーザー定義関数経由で GROUP BY 対応)

【テーブル】

SQLServerだとNothwindデータベースにあるCategoriesテーブル、Oracleでは同じデータ・設定でCategoriesテーブルを作成してお試し

【Categoriesテーブルのデータ】

【SQLServerで実行】

実行したSQLは↓

DECLARE @STR NVARCHAR(3000)
SET @STR=''
SELECT @STR=@STR+CategoryName FROM Categories
SELECT @STR 

かえってきた結果は↓

BeveragesCondimentsConfectionsDairy ProductsGrains/CerealsMeat/PoultryProduceSeafood

【ORACLEで実行】

実行したSQLは↓

SELECT REPLACE(SYS_CONNECT_BY_PATH(CATEGORYNAME,':'),':','') FROM CATEGORIES
WHERE CATEGORYID=8
START WITH CATEGORYID = 1
CONNECT BY PRIOR CATEGORYID = CATEGORYID - 1
/

　※SYS_CONNECT_BY_PATHは階層問合わせでのみ使用可能な関数で、CONNECT BY条件でもどってきた値を区切り文字ありで連結した文字列として返す関数です。空文字('')とかは指定できないのでダミーでとりあえずコロンを指定してReplaceで空文字に置き換えてます。

かえってきた結果は↓

CATEGORYID

------------------------------------------

BeveragesCondimentsConfectionsDairy ProductsGrains/CerealsMeat/PoultryProduceSeafood

1行が選択されました。

【状況】

あるテーブル(T_TEST)の列(RNO int)は１から999999までの連続番号で書き込まれている。
連続番号にするために空き番号が無いかチェックをし、空き番号があれば、それを優先的に使用したいとした場合。

【対処】

ROW_NUMBER関数で先頭から空いてる番号を見つける（SQLServer2005以降）

select
min([仮名].[連番]) as [空き番号]
from
(select [番号], ROW_NUMBER() over(order by [番号]) as [連番] from [テーブル名]
) [仮名]
where [番号]<>[連番]

【補足】

SQL Server2005 Beta 2 Transact-SQL の機能強化

ROW_NUMBER

ROW_NUMBER 関数を使用して、クエリの結果行に整数の一連番号を割り当てることができます。 たとえば、すべての講演者の speaker、track、score を返し、スコアの降順に 1 から始まる連続値を結果行に割り当てるとします。 ROW_NUMBER 関数で OVER (ORDER BY score DESC) を指定した次のクエリを実行すると、希望する結果が出力されます。

SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum, 
  speaker, track, score
FROM SpeakerStats
ORDER BY score DESC

結果セットは以下のようになります。

rownum speaker    track      score
------ ---------- ---------- -----------
1      Jessica    Dev        9
2      Ron        Dev        9
3      Suzanne    DB         9
4      Kathy      Sys        8
5      Michele    Sys        8
6      Mike       DB         8
7      Kevin      DB         7
8      Brian      Sys        7
9      Joe        Dev        6
10     Robert     Dev        6
11     Dan        Sys        3

スコアが最も高い講演者には行番号 1 が割り当てられ、スコアが最も低い講演者は行番号 11 が割り当てられます。ROW_NUMBER 関数は、要求した順序に従ってすべての行に異なる行番号を返します。 OVER() オプションの中で指定した ORDER BY リストが一意ではない場合、結果は 1 とおりに決まりません。 つまり、正しいクエリの結果が 2 つ以上存在することになります。クエリを呼び出すごとに異なる結果が得られることもあります。 たとえば、上記の例では 3 人の講演者 Jessica、Ron、Suzanne に同一の最高スコア (9) が与えられています。 異なる講演者には異なる行番号が割り当たることになっているので、Jessica、Ron、Suzanne にそれぞれ割り当てられた値 1、2、3 は、この 3 人に順不同で割り当てられたと考えてください。 値 1、2、3 がそれぞれ Ron、Suzanne、Jessica に割り当てられたとしても、結果が正しいことに変わりありません。