SQL Tips

目次

SQL Server で Rownumっぽいカラムを追加するには

SQL ServerでSELECTの 20行目から40行目を取得するとか，そういうのは TOP 句を使った例としてネット上にたくさんありますが，本当にROWNUMを取得したいときのコードの例が結構少なかったのでここにメモしておきます。

ユーザ定義関数内でROWNUMを含んだテーブルを作り，それに対してSELECT文を実行する，という形で実装するようです。ユーザ定義関数が使えないとか，それをSELECTするSQL文が使えないとかいう状況はたくさんあると思いますが，そういう場合は，そもそも DataSet や カーソルを使って取得していて，ループで回せばわざわざROWNUMをSQL文で作らなくてもいい状況なんだと思います。私が今まで必要を感じてなかったのは，そういう方法で取得していたからでした。

http://diego18.at.webry.info/を参考にしました。（2004/09/06）

/* ユーザ定義関数 */
CREATE FUNCTION Hase_Udf_GetHogeReport (
@STARTDATETIME DATETIME,
@ENDDATETIME DATETIME
)
RETURNS @DUMMY_TABLE
TABLE(
ROWNUM int IDENTITY(1,1),
ID NVARCHAR(256),
TimeStamp DATETIME,
Message NVARCHAR(256)
)
AS
BEGIN 
	INSERT INTO @DUMMY_TABLE(ID, TimeStamp, Message)
	SELECT ID,TimeStamp,Message
	FROM HogeReport
	AND StartTime BETWEEN @STARTDATETIME AND @ENDDATETIME
	AND EndTime BETWEEN @STARTDATETIME AND @ENDDATETIME
	ORDER BY StartTime
	RETURN
END

/* SQLからの呼び出し方 */
SELECT * FROM Hase_Udf_GetHogeReport(@STARTDATETIME, @ENDDATETIME)

/* ストアドプロシージャから呼び出す */
CREATE PROCEDURE Hase_GetHogeReport 
@STARTDATETIME DATETIME,
@ENDDATETIME DATETIME
AS
BEGIN
	IF( @STARTDATETIME <= @ENDDATETIME
	)
	BEGIN
		SELECT * FROM Hase_Udf_GetHogeReport(@STARTDATETIME, @ENDDATETIME)
	END
END
GO

HogeReportテーブルの構造が分からないともしかすると読みにくいですか？

ID NVARCHAR(256),
TimeStamp DATETIME,
Message NVARCHAR(256),
StartTime DATETIME,
EndTime DATETIME
というカラムのあるテーブルです。