複数条件のSQLについて

タグの編集
投稿者 sl  (社会人) 投稿日時 2017/8/10 14:02:23
環境
・vb.net
・Mysql

DBと接続してデータグリッドビューに表示しています。
今は日付だけをfrom~toで検索してデータを絞っています。
この先、注文番号や小計の検索条件も追加したいのですがSQL文の書き方がわかりません。
注文番号と小計を個別にSQLを書けばちゃんとデータが絞れています。
WHERE句で個々のSQLをANDで繋げていけば複数条件の検索もできますでしょうか??
投稿者 sl  (社会人) 投稿日時 2017/8/10 14:27:09
ANDで繋げて複数条件の検索ができました。
ですが、全部入力しないと表示されなくなってしまいました。
if文でこの項目が空欄だったら、、、、と書けばできますでしょうか??
投稿者 YuO  (社会人) 投稿日時 2017/8/10 15:19:26
項目数が少ないのであれば,組み合わせの数だけSQLを網羅しておくのがセキュリティ的には安全とされます。
ただ,これは大変なので,通常はSQLを文字列を使って組み立てます。

使う条件式を配列なりList(Of String)なりに入れて,String.JoinすればWHERE句の中を作成することができます。
あとは,Listに1件以上あればWHERE句を追加するようなコードを作れば良いです。

ただし,パラメータの取り扱いは若干手間です。
さっさとMySqlCommandを作って,SQLは後からCommandTextに代入する形であれば,Parametersが使えるため,
条件式を使うとなったタイミングでParameters.Addすればよいでしょう。
投稿者 (削除されました)  () 投稿日時 2017/8/10 16:36:44
(削除されました)
投稿者 (削除されました)  () 投稿日時 2017/8/10 16:40:21
(削除されました)
投稿者 魔界の仮面弁士  (社会人) 投稿日時 2017/8/10 16:45:38
SQL 文を動的に生成すると、そのたびに構文解析されてオプティマイザーの効率が落ちるので、
よく使われる画面なのであれば、問い合わせが大文字小文字も含めて常に同一となるようにし、
条件値は MySqlParameter オブジェクト経由で渡すようにしてみては如何でしょう。


【案1】
WHERE ( ORDER_DATE    BETWEEN @ORDER_DATE_FROM AND @ORDER_DATE_TO )
  AND ( ORDER_NUMBER  BETWEEN @ORDER_NUM_FROM  AND @ORDER_NUM_TO  )
  AND ( SUB_TOTAL     BETWEEN @SUB_TOTAL_FROM  AND @SUB_TOTAL_TO  )

という SQL 条件にしておき、

cmd.Parameter("ORDER_NUM_FROM").Value = If(txtOrderNumFrom.TextLength = 0, Integer.MinValue, CInt(txtOrderNumFrom.Text))
cmd.Parameter("ORDER_NUM_TO"  ).Value = If(txtOrderNumTo.TextLength   = 0, Integer.MinValue, CInt(txtOrderNumTo.Text  ))

のように、各フィールドの最大・最小値を指定する。



【案2】
WHERE ( @USE_ORDER_DATE_FROM = 0  OR  ORDER_DATE >= @ORDER_DATE_FROM)
  AND ( @USE_ORDER_DATE_TO   = 0  OR  ORDER_DATE <= @ORDER_DATE_TO  )
  AND ( @USE_ORDER_NUM_FROM  = 0  OR  ORDER_NUM  >= @ORDER_NUM_FROM )
  AND ( @USE_ORDER_NUM_TO    = 0  OR  ORDER_NUM  <= @ORDER_NUM_TO   )
  AND ( @USE_OSUB_TOTAL_FROM = 0  OR  SUB_TOTAL  >= @SUB_TOTAL_FROM )
  AND ( @USE_SUB_TOTAL_TO    = 0  OR  SUB_TOTAL  <= @SUB_TOTAL_TO   )

という SQL 条件にしておき、

If txtOrderNumFrom.TextLength = 0 Then
    cmd.Parameter("USE_ORDER_NUM_FROM").Value = 0
    cmd.Parameter(    "ORDER_NUM_FROM").Value = DBNull.Value
Else
    cmd.Parameter("USE_ORDER_NUM_FROM").Value = 1
    cmd.Parameter(    "ORDER_NUM_FROM").Value = CInt(txtOrderNumFrom.Text)
End If
If txtOrderNumTo.TextLength = 0 Then
    cmd.Parameter("USE_ORDER_NUM_TO").Value = 0
    cmd.Parameter(    "ORDER_NUM_TO").Value = DBNull.Value
Else
    cmd.Parameter("USE_ORDER_NUM_TO").Value = 1
    cmd.Parameter(    "ORDER_NUM_TO").Value = CInt(txtOrderNumTO.Text)
End If

のように指定する。

※上記では、注文番号(ORDER_NUM)列が INT 型のフィールドであると想定しています。
※TextBox に非数値が入力されていた場合のチェックは省略しています。
※注文日、小計などの条件も同様に記述します。