1個のSQL文で、OrderByを2パターン作成したいのですが・・・。

[戻る]
Takeshi 2002/05/08(水) 11:01:18
■動 作 環 境 :Windows2000(Professional)
■開 発 環 境 :Visual C++6.0(MFC使用,ODBC接続)
■データベース :Access2000

  お世話になっております。
SQL文を作成して、データベースから必要なレコードを抽出しようとしています。

テーブルにある2つのフィールドの内、どちらかから Where句 で一致するものを
抽出しているのですが、その順番をどちらのフィールドから一致したのかによって、
違うフィールドを Order By で指定したいのですが、うまくいきません。
SQL文 をうまく工夫すればできるのでしょうか?
以下に実例を記述しますね。

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
(実例)

テーブル1 
  名前(文字列)
  趣味1(テーブル2から)
  期間1(数値)
  趣味2(テーブル2から)
  期間2(数値)

テーブル2
  趣味一覧(文字列)
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

  この例では、テーブル1の[趣味1]または[趣味2]が一致するレコードを
Where Or で検索して、[趣味1]で一致したら[期間1]の順番でソート、
[趣味2]で一致したら[期間2]の順番でソートという風にソートの基準も区別したいのです。
[趣味1]、[趣味2]のどちらか、又はその両方での一致までは実現できています。
更にソートを2パターンから実現する方法がわかりません。

このような事が SQL文で実現できるのでしょうか?または他の方法があれば教えて下さい。
宜しくお願いします。

ワルツ 2002/05/08(水) 13:02:20
UNIONとかテーブル結合の考え方ではだめでしょうか?
テーブル1が2つあるとみたてて。

それとも、最初からテーブルを2つにわけるとか。

ただ、いまいちどういうソートの結果になればよいのかイメージしづらいのですが。

クリリン 2002/05/08(水) 13:32:58
参考までに・・・
http://www.mars.dti.ne.jp/~o-shin/
http://www.sqlpowerpage.co.jp/

Takeshi 2002/05/08(水) 14:41:01
  お世話になります。

→ワルツさんへ

イメージしにくいですか?

つまり、趣味を パソコン で検索をかけたとします。

すると、パソコンを[趣味1]に入れていた人と、パソコンを[趣味2]に
入れていた人の両方が検索にひっかかりますよね。

その場合に、[趣味1]、[趣味2]どちらの場合でも[期間]の長い人を先頭にして
表示したいんです。[期間]も1と2の二種類があるので、

Aさん 趣味1=パソコン  期間1=5年  趣味2=ゲーム    期間2=1年
Bさん 趣味1=ゴルフ    期間1=2年  趣味2=パソコン  期間2=8年

データベースのレコードが上のようだったとしたら

Bさん、Aさんの順で表示したいのですよ。

分かって頂けましたか?

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

→クリリンさんへ

参考にさせて頂きます。でも投稿はしません。マルチポストになるので。

ありがとうございました。

sugar 2002/05/08(水) 15:33:50
データベースがmdbだと、UNIONは使えたかなぁ...

2つの異なる整列をひとつのレコードセットとして取得ですか。
あまりスマートな方法ではありませんが、作業用テーブルTを予め用意しておいて、
テーブル1から趣味1=パソコンに一致するレコードをTに退避、
テーブル1から趣味2=パソコンに一致するレコードをTに退避、
こうして出来上がったTを期間順にSELECTする、
なんて感じでしょうか。

第3水準 2002/05/08(水) 15:49:49
標準SQLだけでは厳しい気がします。

解決策としては

1.SQLでは無くプログラム側でソートする
 SELECTで抽出されたデータを、全てプログラム内に取り込んでおいてから、
 自力でソートを掛ける方法です。
 一番スマートな方法かと思います。

2.DB構造を修正する
 テーブル1を正規化して、分割します

 テーブル1
  個人ID(数値、主キー) 
  名前(文字列)

 テーブル2
  趣味ID(数値、主キー)
  趣味一覧(文字列)

 テーブル3
  個人ID(数値、主キー)
  趣味ID(数値、主キー)
  期間(数値)

 のようなDB構造なら、簡単にORDER BYが掛けられます。
 ただ、DB構造を変えると、周辺にかなりの影響が出ます。

3.ACCESS関数を使ってSQL文を作成する
 ACCESSの特殊関数を使うならば、以下のようなSQL文で実現可能だと思います。
 (ADO+JetEngine+ACCESS2000では確認済み)
 また、趣味1と趣味2が両方引っかかった場合は、
 期間1と期間2を比較して大きい方の値を採用しています

SELECT * FROM テーブル1 
WHERE (趣味1 = 'パソコン' OR 趣味2 = 'パソコン') 
ORDER BY 
(iif((趣味1 = 'パソコン' AND 趣味2 = 'パソコン'),(iif((期間1 > 期間2),期間1,期
間2)),
 (iif((趣味1 = 'パソコン'),(期間1),
  (iif((趣味2 = 'パソコン'),(期間2),0))
 ))
)) DESC

iif関数は
iif(評価対象,True時の値,False時の値)のような関数仕様を待ち
第1引数の評価対象がTrueの時は、第2引数を返し、
第1引数の評価対象がFALSEの時は、第3引数を返します。
(参考文献:Access関数ポケットリファレンス/山田健一/技術評論社)

この方法の場合、SQL文一発でソートしたデータを取って来れますが
プログラムがACCESSに依存した物になってしまい
将来的に、他のDBシステムに移植する際等に妨げとなります。
また、SQL文が非常に見難くなります(汗
Switch関数を使えば、もうちょっと見やすくなるはずなのですが
なぜか使えませんでした。

クリリン 2002/05/08(水) 16:18:36
>将来的に、他のDBシステムに移植する際等に妨げとなります。
プログラムコードをDBによっても変更しない方法として
Accessはクエリー、SQL Server や Oracle であれば、ストアドやビューを使用して、プログ
ラム内のSQL文をWhere文だけにする等…という手があります。
しかし、移植の際はやはり、DB内のSQL文を変更しないといけませんけど・・・
例えば、iif関数をcase関数(SQL Server),decode関数(oracle)
といった具合に・・・

Takeshi 2002/05/08(水) 16:49:55
  お世話になっております。
sugerさん、第三水準さん、ありがとうございます。

いつもながら 第3水準さんの見識には驚かされます。
まずは、3. の Access関数でやってみました。
残念ながら未だにこのSQL文を解析する事ができないのですが、
先に動作確認だけでもと実行してみました。

結果は、例外で AccessViolation となりました。
また続いてファイル選択ダイアログが出てきて OUTPUT.C のパスを聞いてきます。

ちなみに、"Access関数を使う" というのはどういう事でしょうか?
VC++でSQL文を作成して、そのSQL文字列を渡してOpenするとマズイのでしょうか?

方法が違っていたらすいません。

クリリン 2002/05/08(水) 16:57:11
>ちなみに、"Access関数を使う" というのはどういう事でしょうか?
>VC++でSQL文を作成して、そのSQL文字列を渡してOpenするとマズイのでしょうか?
iif関数はAccessのオリジナル関数です。
よって、VC++でSQL文を作成した場合、別のDBでは、エラーになるという事です。

Takeshi 2002/05/08(水) 17:06:04
  クリリンさん、どうもです。

環境的に Access から変更が無ければ問題ないという事ですね。
この SQL文で例外になると言う事は、どこかにタイプミスがあるのか、または
ODBCでの環境下では使用できない可能性があるという事ですかね。

使用できないのでれば、Accessのクエリーによる抽出を試してみます。
ありがとうございました。

クリリン 2002/05/08(水) 17:42:25
まずは、SQL文にミスがないかどうかを、クエリのSQLビューのところに、VCが作成したSQL文を
コピペし、実行してみてはいかがでしょうか?
私がよくやっているのは、エディットボックスにSQL文を出力して、コピーですけど・・・
他の方法もあると思いますけど、手っ取り早いかな?と思ってやってます。
>Accessのクエリーによる抽出
私が提案しておきながら、よく考えたら、クエリに引数が取れるのか疑問です。m(__)m
ただの、「select * from Q_作成クエリ where … order …」
なんてのは出来るのですが・・・。

第3水準 2002/05/08(水) 18:04:15
3.のSQL文については、
CDatabaseクラス+CRecordsetクラスでも動作を確認しました。

あと、ORDER BY 〜 DESCの間のiif()の山は
C言語風に書くとこんな感じになります。

if((趣味1 == 'パソコン') && (趣味2 == 'パソコン')){
    if(期間1 > 期間2){
        return(期間1);
    } else {
        return(期間2);
    }
} else {
    if(趣味1 == 'パソコン'){
        return(期間1);
    } else {
        if(趣味2 == 'パソコン'){
            return(期間2);
        } else {
            return(0);
        }
    }
}

このreturnされた値を使ってソートしています。

Takeshi 2002/05/08(水) 19:29:08
  お世話になっております。

→第三水準先生へ

以前、別スレッド「SQL文による抽出を使ってRecordsetをOpenしたいのですが・・・」で、
SQL文による抽出方法を教えて頂きましたよね。

Select * From MyTable Where Field1 = '%s' Or Field2 = '%s'",(LPCTSTR)CString,
(LPCTSTR)CString

この SQL文に対して更に OrderBy を使って順番を設定したいのです。
すると、ご指摘のようにC言語風で書くよりは Access の iif関数を使った方がやり易いような
気がします。あのやり方だと、フィールドの値を参照する為に[趣味]の一致条件だけで一度
レコードセットをオープンして、それぞれの[期間]の値をVC++の文字列に取り込んでから、
If , Elseによる分岐で処理をして、Return値を得るんですよね。
そして、それを元にもう一度レコードセットをソート条件付きで再度Openすると。

・・・このロジックをあれこれ考えていると、既に頭が混乱して来たんです。
できれば一発の SQL文で抽出したいので、やはり前述のAccess関数のiifでいこうと思います。
移植性の問題は今回は重要ではないのでこれで問題ないと思います。

ありがとうございました。

Takeshi 2002/05/08(水) 20:19:08
[[解決]]
  いつもお世話になっております。

(かっこ)の数確認、文字列引数の確認を入念に行った結果、
文字列引数が5個必要な事が判明したので正常に抽出動作しました。
最初の引数2個のままでSQL文を構築していました。

よく考えたら、5個所で'%s'を指定したら、後続に付ける引数も5個ですもんね。
お蔭様で解決致しました。なんだかおんぶにだっこで情けないですが頑張ります。
お世話になりました。

第3水準 2002/05/08(水) 23:03:09
無事解決されたようですが気になったので。
前に私が書いた「C言語風〜」は
SQL文のiif()の部分のロジックをC言語風に書いたらああなると言うだけの物です。
SQL文のiif()の部分が複雑怪奇に入り組んでいるので
解析するのにC言語風のロジックと見比べながらやれば楽になるかな〜と思ったのですが
逆に混乱させてしまったようですね。すみませんでした。

Takeshi 2002/05/09(木) 09:11:15
  おはようございます。

いえいえ考え方は十分参考になりましたよ。
"C" のソースは見慣れておりますし。

Access関数もご存知であるし、かなりの経験をお持ちなんですね。
また何かありましたら、宜しくお願いします。

                                       Takeshi

古いスレッドには追加発言できません。