SQLでの抽出方法 【IN】【ROW_NUMBER】【副問い合わせ】
①IN
SELECT * FROM test_table WHERE aa IN (SELECT aa FROM (SELECT aa FROM test_table GROUP BY aa,bb,cc,dd HAVING COUNT(*) >= 2) AS X) --6.28 これでもできた。 SELECT * FROM test_table WHERE aa IN (SELECT aa FROM test_table GROUP BY aa,bb,cc,dd HAVING COUNT(*) >= 2)
IN句には複数の条件を記述することができます。
このsql文では、副問い合わせを行っている・・・のですが、副問い合わせの中で同じテーブルを使用するとエラーが出てしまうので、ASで別名をつけています。
書いていて思い出しました。
そのエラーが実際出るのかを試していません。
明日会社でやってみよう。
②ROW_NUMBER
SELECT aa,bb,cc,dd ,ROW_NUMBER() OVER(PARTITION BY aa,bb,cc,dd ORDER BY aa) AS 連番 FROM test_table
ROW_NUMBERは検索結果に順番をつけることができます。
ROW_NUMBER関数内にある、PARTITION BY でグループ化ができます。
③副問い合わせ
DELETE test_table FROM test_table AS C INNER JOIN ( SELECT aa,bb,cc,dd ,ROW_NUMBER() OVER(PARTITION BY aa,bb,cc,dd ORDER BY aa) AS 連番 FROM test_table ) AS A ON C.aa = A.aa AND C.bb = A.bb AND C.cc = A.cc WHERE 連番 <> 1
副問い合わせは、SELECT文による問い合わせを入れ子にすることです。
③はINNER JOINの中で副問い合わせを行っています。
同じテーブルで、JOINさせているのはROW_NUMBER（連番）をつけるためなのですが、これはまだあやふやなので試す必要があります。
6.28
なんとなくわかってきたような・・・
上司が先輩におっしゃっていたことなのですが、自分も覚えておかなければならないと思ったので書き残しておきます。
自分の行ったこと（書いたプログラムとか）が、今後他のところでどんな影響を受けるのか考えなさい。
自分が作業した後のことまで考えるのは難しいです。
これから少しずつできるようになればいいな・・・。
SqlServerで、ストアドプロシージャを作成 その２
明日記事を書くと言っていたのに、遅れてしまいました。
反省。
前回の続きからです。
IF @header = 1 BEGIN -- BCPコマンド出力設定をする -- 半角スペースある/なしで正常に実行されないので注意する SET @BCP_str = '' SET @BCP_str = 'bcp "' + @SQL_heder_edit +'"' SET @BCP_str = @BCP_str + ' queryout "' + @out_folder + ' Header.csv" ' SET @BCP_str = @BCP_str + '-c -t"," ' SET @BCP_str = @BCP_str + ' -S' + @S_name + ' ' SET @BCP_str = @BCP_str + '-d ' + ' test' + ' ' SET @BCP_str = @BCP_str + '-U ' + @U_id + ' ' SET @BCP_str = @BCP_str + '-P ' + @pass + ' ;' -- CSVファイル実行 -- xp_cmdshell は、OS のコマンドを実行して出力をテキストの行として返す EXECUTE @result = master.dbo.xp_cmdshell @BCP_str IF @result = 1 BEGIN SET @R_Msg = 'ヘッダー作成失敗' RETURN @R_value END END -- BCPの詳細ファイル出力 SET @BCP_str = '' SET @BCP_str = 'bcp "' + @SQL +'"' SET @BCP_str = @BCP_str + ' queryout "' + @out_folder + ' Detail.csv" ' SET @BCP_str = @BCP_str + '-c -t"," ' SET @BCP_str = @BCP_str + ' -S' + @S_name + ' ' SET @BCP_str = @BCP_str + '-d ' + ' test' + ' ' SET @BCP_str = @BCP_str + '-U ' + @U_id + ' ' SET @BCP_str = @BCP_str + '-P ' + @pass + ' ;' EXECUTE @result = master.dbo.xp_cmdshell @BCP_str IF @result = 1 BEGIN SET @R_Msg = '詳細ファイル作成失敗' RETURN @R_value END IF @header = 1 BEGIN -- ヘッダーと詳細のファイル結合 SET @File_copy = '' SET @File_copy = @File_copy + 'copy /B' SET @File_copy = @File_copy + @out_folder + ' Header.csv' SET @File_copy = @File_copy + ' +' SET @File_copy = @File_copy + @out_folder + ' Detail.csv' SET @File_copy = @File_copy + ' ' SET @File_copy = @File_copy + @out_folder + file_name -- CSVファイル結合実行 EXECUTE @result = master.dbo.xp_cmdshell @File_copy END IF @header = 0 BEGIN SET @File_copy = '' SET @File_copy = @File_copy + 'copy /B' SET @File_copy = @File_copy + @out_folder + ' Detail.csv' SET @File_copy = @File_copy + ' ' SET @File_copy = @File_copy + @out_folder + file_name EXECUTE @result = master.dbo.xp_cmdshell @File_copy END IF @header = 1 BEGIN -- ヘッダー消去 SET @File_Del = '' SET @File_Del + ' del /Q' SET @File_Del + @out_folder + ' Header.csv' EXECUTE master.dbo.xp_cmdshell @File_Del END -- 詳細ファイル消去 SET @File_Del = '' SET @File_Del + ' del /Q' SET @File_Del + @out_folder + ' Detail.csv' EXECUTE master.dbo.xp_cmdshell @File_Del END -- 例外エラー END TRY BEGIN CATCH RETURN @R_value -- -1(失敗の値)が代入されている END CATCH SET @R_Msg = CAST(@Cnt AS VARCHAR) + '件を書き込みました。' SET @R_value = 0 --成功の値(戻り値) RETURN @R_value END GO
変数にどんどん代入していって、実行させるための文字列を作成するのが、難しかったです。
仕事を始めて数ヶ月、少しずつ慣れてきました。
まだ沢山学ばねばならないのですが、そろそろキーボードだけでパソコンを動かす方法（ショートカットキーだったかな）も覚えていこうかな。
SqlServerで、ストアドプロシージャを作成 その１
再びストアドを作成したので、２回に渡って復習します。
今回は、カーソル作成までです。
CREATE PROCEDURE SP_ABC ( @file_name VARCHAR(50) ,@table_name VARCHAR(50) ,@out_folder VARCHAR(20) -- 出力先フォルダ（フルパス） ,@S_name VARCHAR(30) ,@U_id VARCHAR(20) ,@pass VARCHAR(20) ,@header CHAR(1) ,@R_Msg VARCHAR(1000) OUTPUT -- 返却メッセージ ) AS DECLARE @hed_str NVARCHAR(3000) -- ↑カーソルからfetchしてきた情報をカンマ区切りで格納 DECLARE @hed_get VARCHAR(50) -- ↑列のヘッダーを格納 DECLARE @SQL NVARCHAR(1000) -- SQL...は、SQL文を格納 DECLARE @SQL_header NVARCHAR(1000) DECLARE @SQL_cursor NVARCHAR(1000) DECLARE @SQL_heder_edit NVARCHAR(1000) DECLARE @BCP_str VARCHAR(3000) DECLARE @File_copy VARCHAR(3000) DECLARE @File_Del VARCHAR(3000) DECLARE @R_value int --戻り値 DECLARE @D_Msg VARCHAR(100) DECLARE @result int DECLARE @Cnt INTEGER BEGIN SET @D_Msg = 'ストアドエラー発生' SET @R_value = -1 BEGIN TRY -- テーブルデータ存在確認 SET @SQL = N' SELECT * FROM ' + @table_name EXECUTE sp_executesql @SQL --SQL実行 SET @Cnt = @@ROWCOUNT IF @Cnt = 0 BEGIN SET @R_Msg = 'データなし' + @table_name RETURN @R_value END -- データ抽出SQL作成 IF @header = 1 BEGIN SET @SQL_header = N' SELECT d.name FROM Sys.tables AS t LEFT OUTER JOIN sys.columns AS c ON t.id = c.id WHRER t.name = ''' + @table_name + '''' + N' ORDER BY c.column_nm ' -- ↑ここがわかりづらい -- テーブルの名前を条件にして、カラムとテーブルを結合 -- Sys.tablesというのはカタログビューと言う。 -- カタログビューは、SqlServerデータベースエンジンによって使用される情報を返す。 EXECUTE sp_executesql @SQL_header SET @SQL_cursor = N' DECLARE aa CURSOR FOR ' + @SQL_header -- ↑先ほど外部結合させるSQL文をカーソルの条件にしている。 EXECUTE sp_executesql @SQL_cursor OPEN aa FETCH NEXT FROM aa INTO @hed_get IF @@FETCH_STATUS = 0 BEGIN SET @hed_str += @hed_get END WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM aa INTO @hed_get SET @hed_str += ',' + @hed_get END CLOSE aa DECLARE aa SET @SQL_heder_edit = N'SELECT ' + '''' + @hed_str + '''' END
以上で、カンマ区切りのSELECT文を作成することができました。
これを、csvに出力させるためにbcpユーティリティを使っていきます。
続きはまた明日。
ストアドプロシージャからストアドプロシージャを呼び出す【SqlServer】
昨日は念願の（！）カーソルのあるストアドプロシージャの作成を行いました。
まだ未完成ですが、復習でコードを載せます。
USE testdb GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE testpro( @aaa VARCHAR(20) ) AS DECLARE @returnval int --戻り値 DECLARE @SQL NVARCHAR(4000) DECLARE @lMsg(4000) --カーソルデータ格納変数 DECLARE @kakunou VARCHAR(1000) BEGIN BEGIN TRY SET @returnval = 1 SET @lMsg = @aaa + 'のデータ移行を開始しました。' EXECUTE testbl '案内','',@aaa,0,@lMsg IF (@aaa IS NULL OR @aaa = '') BEGIN SET @lMsg = '引数が未設定' EXECUTE testbl 'エラー','',@aaa,1,@lMsg RETURN @returnval END --カーソル作成 SET @SQL = 'DECLARE abc CUSOR FOR SELECT tosi From' +'(SELECT name From' + @aaa + ' .table) AS T' EXECUTE sp_executesql @SQL --カーソルオープン OPEN abc --abcから変数@kakunouに１件ずつデータを格納する FETCH NEXT FROM abc INTO @kakunou IF @@FETCH_STATUS <> 0 BEGIN SET @lMsg ='テーブルが存在しません' EXECUTE testbl 'エラー','',@aaa,1,@lMsg RETURN @returnval END While @@FETCH_STATUS = 0 BEGIN EXECUTE abcInsertpro @aaa,@kakunou #処理の後、FETCHで再び１行データを取得する FETCH NEXT From abc INTO @kakunou END CLOSE abc DEALLOCATE abc SET @lMsg = @aaa + 'のデータ移行を完了しました。' EXECUTE testbl '案内','',@aaa,0,@lMsg END TRY --例外エラー BEGIN CATCH SET @lMsg = '[ERROR_NUMBER]' + @SQL EXECUTE testbl 'エラー','',@aaa,1,@lMsg RETURN @returnval END CATCH SET returnval = 0 END GO
@@FETCH_STATUSの設定がおかしいかな？
自分でもわかるように、土日で説明を書くつもりです。
...6月4日追加
データ以降が完了したことを判断する処理は、書かなくても良い(正常に処理されれば、下に流れていくため)
ここから雑記
金曜日に、映画「美しい星」という三島由紀夫原作のSF映画を見ました。
人間は人間自身を自然の一部だと思っていない...だったかな？
個人的にとても面白かったです。
MySqlでストアドプロシージャを作成する
MySqlではSqlServerで使えていたRETURNが使えないので、HANDLERを使用する...みたいです。
未完成なのですが、忘れないようにメモ。
.....6月1日
HANDLERでは、希望の処理ができませんでした。
# workbenchで作成しているので、DELIMITERは省略して良い CREATE PROCEDURE SP_MGR_SetLog ( IN aa VARCHAR(3) IN bb VARCHAR(10) OUT returnmsg VARCHAR(2000) OUT returnval int; ) BEGIN DECLARE ssql VARCHAR(4000); #このハンドラがおかしい？ #6月1日...ハンドラは今回使用しません。 /*DECLARE con1 CONDITION for 1; DECLARE EXIT HANDLER for con1 BEGIN SET returnmsg = '引数エラー'; END; #例外エラー（インサート失敗した時） DECLARE EXIT HANDLER for SQLEXCEPTION BEGIN SET returnmsg = 'インサート失敗'; END;*/ IF (aa IS NULL) OR (aa = '') THEN #エラーを検知するのはここで行なっている #が、条件のIF文が以下に続くので、エラーは書き換えられてしまう SET returnmsg = '必須項目が未設定'; SET returnval = 0; END IF; IF NOT bb ='おはよう' OR bb='こんにちは' THEN SET returnmsg = '項目の値が間違ってます。'; SET returnval = 0; END IF; ＃その後、処理が多々...省略 #INSERTはこのように準備してから実行させる。 PREPARE ssql From 'INSERT INTO aa VALUES(?,?);' SET @a = a; SET @b = b; EXECUTE ssql USING @a,@b; END;
INSERT文でコーテーションを考えなくてもよかったので、とても楽になりました。
このprepareは、インジェクションの考え方と同じ（これはよくわからないので、調べる）だそうです。
また、MySqlでは文字列と認識されると緑色になります。
・・SqlServerでは、文字列が赤色で、変数が緑色になっていたような・・これも確認します。
少しずつわかってきました。
今度はカーソルのあるストアドを作りたいなー。
楽しみです。
SqlServerでストアドプロシージャを作成する
ストアドプロシージャを作成したので、復習です。
このストアドプロシージャはインサート処理と、エラー表示を行います（多分・・・）。
USE [AA_DB] GO CREATE PROCEDURE [aa] (@log VARCHAR(5) --パラメータを宣言 ,@name VARCHAR(30) ,@suuzi int ,@MsgReturn VARCHAR(2000) OUTPUT ) AS DECLARE @SQL NVARCHAR(4000) --変数を宣言 DECLARE @DefMsg VARCHAR(1000) DECLARE @ReturnVal int DECLARE @Err INTEGER BEGIN SET @DefMsg='ストアドエラー' SET @ReturnVal = 2 --失敗したら2がかえってくる /** 引数チェック **/ IF(@log IS NULL OR @log = '' OR @name IS NULL OR @name = '' OR ) BEGIN SET @MsgReturn = @DefMsg + '引数未設定' RETURN @ReturnVal END BEGIN TRY SET @SQL = N'INSERT INTO [AA] VALUES ('''+ @log + '''' --引数を文字列として認識するための、コーテーション +N',''' + @name +'''' --NはUnicodeとして処理させるためにつける +N',' + CAST(@suuizi AS VARCHAR)+N''')' --数値を文字列と認識させるために、キャストする。 EXECUTE @Err = sp_executeaql @SQL --@Errに、インサートが失敗したのか成功したのかわかる値が入っている IF @Err <> 0 BEGIN SET @MsgReturn = @DefMsg + 'インサート失敗' RETURN @ReturnVal END SET @ReturnVal = 0 END TRY BEGIN CAHTCH SET @MsgReturn = @DefMsg + '[ERROR_NUMBER]'+@SQL RETURN @ReturnVal END CAHTCH END GO
変数とパラメータが、少しだけ混乱する時があるので気をつけます。
キャストについて【C#】
昨日は、キャストが思いつかず悔しい思いをしました。
忘れないように、コードを残します。
問題のキャスト処理を書く前に、条件分岐の処理も復習します。
ublic void kakunin_Click(C1.WPF.DataGrid.DataGridEventArgs e) { this.IsRegistEnable = true; //登録ボタンにバインドさせている。 bool isError = false; string name = e.Column.Name; DataRowView dv = (DataRowView)e.Row.DataItem; dv.Row.ClearErrors(); e.Row.Errors.Clear(); DataGridRowError dgreroor = new DataGridRowError(); //必須入力チェック //sentakuメソッドで、DataGridの項目の選択か必須かを判断している。 if(!this.sentaku(name)) { //DtoCheckメソッドにて、エラーの出ているメソッドを判別。 isError = this.DtoCheck(e.Column,dv.Row) if(isError) { dgreroor.ColumnNames.Add(name); dgreroor.Message="必須入力エラー"; e.Row.Errors.Add(dgreroor); dv.Row.SetColumnError(name,"[項目:"+ name + "]を入力してください"); } } //switchで、入力した値が項目に合う値なのか（数字なのか、半角英数なのかなど）判別。 switch(name) { case"aa": case"bb": if(!InputAuxiliaryUtil.IsNumeric(dv[name].Tostring())) { dgreroor.ColumnNames.Add(name); dgreroor.Message = "入力エラー" e.Row.Errors.Add(dgreroor); dgreroor.Row.SetColumnError(name,"数字を入力してください"); isError = true; } break; case"cc": if(....) { //略... } break; } //エラーがあった場合、登録ボタンを使えないようにする。 if(isError) { e.Cansel = true; this.IsRegistEnable = false; } }
SetColumnErrorというのが、DataGridの問題セルに出現するエラーです。（見た目はフキダシみたいなものです）
InputAuxiliaryUtil.IsNumericというメソッドで、判定させます。
#region 文字チェック public static bool IsNumeric(string value) { if(null == value) { return true; } if(string.IsNullOrEmpty(value.Trim())) { return true; } if(!Regex.IsMach(value.Trim(),REGEX_Numeric)) { return false; } return true; } #endregion
また、この文字チェック内で使われているREGEX_Numericは、辿っていくと正規表現にたどり着きます。
//こんなの //これは例なので、正規表現は半角英数字の判別設定です。 private static string REGEX_Numeric = "^[-_/0-9a-zA-Z]+$";
DataGridは表示されるだけなので、入力文字についてはこのように自分で書いていかなければならないのです。
次はキャストです。
else if(e.Column.Name == "ff") { dv[name] = dv[name].Tostring().PadLeft(2,'0') }
これです。
間違って書いてコードは以下になります。
//このコードは間違っています。 else if(e.Column.Name == "ff") { var i = (DataRowView)e.Row.DataItem; i.PadLeft(7,'0') string col = e.Column.Tostring(); col.PadLeft(7,'0') }
iやcolには値が入っていますが、オブジェクトを知りません。
ただ値を入れているという処理をしているだけなので、DataGridには何も変化がない、です。
値が入っているプロパティを見つけ、キャストして使用できるようにするまでが難しいな・・・。
