- PR -

SELECT INTO でソートした値にIDENTITYをふるには

1|2 次のページへ»
投稿者投稿内容
うい
会議室デビュー日: 2007/05/29
投稿数: 11
投稿日時: 2007-06-21 01:45
初めて登校させていただく、ういというものです。
よろしくお願いします。

環境 SQLServer2000
TESTDATA列でソートした後に連番を振りたいと思っています。
下のSQLでソートの後に連番がなったのですが、
サブクエリ内にTOPを使用しないとエラーが出るのでTOPをいれてますが固定値なのが気になります。

ソートした後に連番をつけて全件取得する方法はないのでしょうか。
よろしくお願いします。

DROP TABLE ##SUBTABLE;
SELECT IDENTITY(int,1,1) AS RANK, * INTO ##SUBTABLE FROM
(
SELECT TOP 100 TESTDATA
FROM TESTTABLE
ORDER BY TESTDATA
) AS SUBTABLE ;

SELECT * FROM ##SUBTABLE;
かめたろ
ぬし
会議室デビュー日: 2003/03/20
投稿数: 255
投稿日時: 2007-06-21 10:06
引用:

SELECT IDENTITY(int,1,1) AS RANK, * INTO ##SUBTABLE FROM
(
SELECT TOP 100 TESTDATA
FROM TESTTABLE
ORDER BY TESTDATA
) AS SUBTABLE ;

なぜインラインビューにしているのかはわかりませんが、ビューのクエリは実際はもっと複雑なんだろうなと思いつつ。

SELECT IDENTITY(int,1,1) AS RANK, *
INTO ##SUBTABLE
FROM
(
SELECT TESTDATA
FROM TESTTABLE
) AS SUBTABLE
ORDER BY TESTDATA

複雑じゃないのならこんなんでも
SELECT IDENTITY(int,1,1) AS RANK, TESTDATA
INTO ##SUBTABLE
FROM TESTTABLE
ORDER BY TESTDATA

ビューの中でソートしなきゃ・・・って気持ちは分からなくは無いですが、その必要はないですよね。
うい
会議室デビュー日: 2007/05/29
投稿数: 11
投稿日時: 2007-06-21 23:17
>ビューの中でソートしなきゃ・・・って気持ちは分からなくは無いですが、その必要は>ないですよね。

ビューの中でソートするのとSELECT INTOのあとにソートするのではRANK１になる行が変わるようです。
かめたろ
ぬし
会議室デビュー日: 2003/03/20
投稿数: 255
投稿日時: 2007-06-22 10:39
引用:

ビューの中でソートするのとSELECT INTOのあとにソートするのではRANK１になる行が変わるようです。

え、まじ？
とちとびっくりしたので試さずにはいられませんでした。

SQL Server 2000 SP4
-------------------------------------------
create table #TESTTABLE (TESTDATA INT)
insert into #TESTTABLE values(10)
insert into #TESTTABLE values(1)
insert into #TESTTABLE values(5)
insert into #TESTTABLE values(3)

--うい さんの
SELECT IDENTITY(int,1,1) AS RANK, * INTO #SUBTABLE1 FROM
(
SELECT TOP 3 TESTDATA --結果が異なることを示すため100を3に
FROM #TESTTABLE
ORDER BY TESTDATA
) AS SUBTABLE

--おれの
SELECT IDENTITY(int,1,1) AS RANK, TESTDATA
INTO #SUBTABLE2
FROM #TESTTABLE
ORDER BY TESTDATA

select * from #SUBTABLE1
select * from #SUBTABLE2
----------------------------------------------

結果
RANK TESTDATA
1 1
2 3
3 5

(3 件処理されました)

RANK TESTDATA
1 1
2 3
3 5
4 10

(4 件処理されました)

うぅ〜ん、「RANK１になる行」は同じです。
その現象が起きるミニマムのデータとクエリを提示することは可能でしょうか。
うい
会議室デビュー日: 2007/05/29
投稿数: 11
投稿日時: 2007-06-24 22:43
返信遅れてすいません。
環境はSQLServer2000 体験版です。
drop table ##subtable1;

SELECT IDENTITY(int,1,1) AS RANK, * INTO ##SUBTABLE1 FROM
(
SELECT TOP 3 TESTDATA
FROM TESTTABLE

) AS SUBTABLE
ORDER BY TESTDATA ;
select * from ##subtable1;

結果(ORDER BYが外)
RANK TESTDATA
3 10
2 11
1 12


drop table ##subtable1;

SELECT IDENTITY(int,1,1) AS RANK, * INTO ##SUBTABLE1 FROM
(
SELECT TOP 3 TESTDATA
FROM TESTTABLE
ORDER BY TESTDATA
) AS SUBTABLE;

select * from ##subtable1;

結果(ORDER BYが内)
1 10
2 11
3 12


ORDER BYを外に持ってくるとTESTDATA10に対してRANKが３になってます。
中に入れると10のRANKが1になります。
うい
会議室デビュー日: 2007/05/29
投稿数: 11
投稿日時: 2007-06-24 22:47
テストデータを入れると大きい数字から入れていくとこういった現象がおきました。
テストツールにはクエリアナライザを使っています。
かめたろ
ぬし
会議室デビュー日: 2003/03/20
投稿数: 255
投稿日時: 2007-06-25 11:45
引用:

ういさんの書き込み (2007-06-24 22:47) より:
テストデータを入れると大きい数字から入れていくとこういった現象がおきました。

はい。その辺も確認できるようにと思って、以前のテストにおいてテストデータは以下のようにランダムに入力していました。
引用:

かめたろさんの書き込み (2007-06-22 10:39) より:
create table #TESTTABLE (TESTDATA INT)
insert into #TESTTABLE values(10)
insert into #TESTTABLE values(1)
insert into #TESTTABLE values(5)
insert into #TESTTABLE values(3)


こちらの環境は、SQL Server 2000 Developer Edition (SP4) です。
念のため、今回のういさんのバッチを少々カスタマイズしてもう一回テストしてみました。

------------------------------------------------
create table ##TESTTABLE (TESTDATA INT)
insert into ##TESTTABLE values(12) --大きい順で
insert into ##TESTTABLE values(11)
insert into ##TESTTABLE values(10)

SELECT IDENTITY(int,1,1) AS RANK, * INTO ##SUBTABLE1 FROM
(
SELECT TOP 3 TESTDATA
FROM ##TESTTABLE
) AS SUBTABLE
ORDER BY TESTDATA ;

select * from ##SUBTABLE1;
------------------------------------------------

結果
RANK TESTDATA
1 10
2 11
3 12

(3 件処理されました)


うちの環境では問題ないんですよねぇ。実行プランを見ると、Sort後にComputeScalarになってますし。たまたまなのだろうか・・・。

一応、実行プランを確認してみてはどうでしょうか。
うい
会議室デビュー日: 2007/05/29
投稿数: 11
投稿日時: 2007-07-06 00:35
返信遅れてすいません。

かめたろさんに載せていただいたSQLをそのまま実行した結果を
下に貼り付けます。
RAMK TESTDATA
3 10
2 11
1 12

SELECT INTO部分の実行計画を載せます。
TableScan57%→Top0%→ComputeScalar0%→Sort17%→ComputeScalar0%→Top0%
→TableInsert/Insert25%→SELECT INTO0%

実行計画の意味自体を勉強中なのでよくわかりませんでした。

1|2 次のページへ»

スキルアップ／キャリアアップ（JOB@IT）