2019-03-28

ExcelでSQLを使う-016: SQL 複数の表からの検索

　狂犬病予防接種をして歩く”旅の獣医”にあこがれ、町の人の信頼のもとに成り立つ開業医を夢見る博士課程のハムテルと二階堂。理想の獣医師像を求めて修行に出た二人を待ち受けていた現実とは？一方、清原たちの開業のしらせを聞いて、あせるおばあさんがめぐらせた画策とは？さまざまな動物たちと個性的な人々が織りなしてきた北の大地の物語もいよいよ終幕。かって予告されたとおり、獣医師の道をつき進むハムテルの胸に去来するものは何であろうか？（同書の帯より）

◇◇◇◇◇

複数の表からの検索

　同じデータベースに属していれば、SELECT文を使って複数の表からデータを検索することができます。

　SELECT ＜列名の並び＞ FROM ＜表1＞,＜表2＞ WHERE [＜表1＞].＜列＞ = [＜表2＞].＜列＞

　例えば、次のような表＜商品一覧＞と表＜商品枝番情報＞がありるとします。この２つの表は、[商品一覧].[ID]と[商品枝番情報].[商品一覧_ID]と結合するための列を持っています。この場合、SELECT文で２つの表からデータを検索することができます。

    --------------------------------------------------
        表商品一覧    表商品枝番情報
    --------------------------------------------------
         ID             ID
         品名           商品一覧_ID
         綴り           区分
         科目           枝番
         品種           仕入単価
                        販売単価
    --------------------------------------------------

　次は、紳士靴に関する共通の情報が表＜商品一覧＞に、23cm、24cm、25cmというサイズごとの仕入単価や販売単価が表＜商品枝番情報＞に登録されている場合に、サイズ23cmの紳士靴の＜品名＞＜仕入単価＞＜販売単価＞を検索する例です。

　SELECT 商品一覧.品名,商品枝番情報.仕入単価, 商品枝番情報.販売単価 FROM 商品一覧, 商品枝番情報WHERE 商品一覧.id=商品枝番情報.商品一覧_ID AND 商品枝番情報.枝番='23cm'

　このように長いSQL文は、マクロに書く前に一度〔イミディエイトウインドウ〕でテストすることをお勧めします。私は、メモ帳で次のようにSELECT節、FROM節、WHERE節毎に行を変えて書いてからテストしました。

? DSelect("SELECT
[商品一覧$A1:E100].品名,
[商品枝番情報$A1:F100].仕入単価,
[商品枝番情報$A1:F100].販売単価
FROM
[商品一覧$A1:E100],
[商品枝番情報$A1:F100]
WHERE
[商品一覧$A1:E100].ID=[商品枝番情報$A1:F100].商品一覧_ID
AND
[商品枝番情報$A1:F100].枝番='23cm'",,chr(13))
1;紳士靴A;5000;6750
2;紳士靴B;5500;7430

◇◇◇◇◇

DSelect関数の結果を表に出力する

　８回に渡るハスキー流「SQLリファレンス」も本稿で終わります。最後に、上記のDSelect関数を実行して新しい表に出力するマクロを紹介しておきます。

表示がズレています。

この不具合は、2019/03/22に解決しました。

記事№017を参照してください。

　マクロは、１行でも書けます。しかし、それでは、他のユーザーがアレンジして再利用うことは不可能です。多少、面倒でも《誰でも、目で見たらわかるマクロ》を書くことを勧めておきます。

Option Explicit

'
' 《例》
'
' SELECT
'         [商品一覧$A1:E100].品名,
'         [商品枝番情報$A1:F100].仕入単価,
'         [商品枝番情報$A1:F100].販売単価
'     FROM
'         [商品一覧$A1:E100],
'         [商品枝番情報$A1:F100]
'     WHERE
' [商品一覧$A1:E100].ID = [商品枝番情報$A1:F100].商品一覧_ID
'         AND
' [商品枝番情報$A1:F100].枝番='23cm'",,chr(13))
'
Sub 商品情報検索して表に出力する()
    Dim strSQL    As String
    Dim strSelect As String
    Dim strFrom    As String
    Dim strWhere As String

    strSelect = "SELECT " & _
                     "[商品一覧$A1:E100].品名," & _
                     "[商品枝番情報$A1:F100].仕入単価, " & _
                     "[商品枝番情報$A1:F100].販売単価"
    strFrom = " FROM " & _
   "[商品一覧$A1:E100]," & _
   "[商品枝番情報$A1:F100]"
    strWhere = " WHERE " & _
   "[商品一覧$A1:E100].ID = [商品枝番情報$A1:F100].商品一覧_ID" & _
   " AND [商品枝番情報$A1:F100].枝番='23cm'"
    strSQL = strSelect & strFrom & strWhere
    ' ※※※※※※※※
    ' 表に出力する
    '※※※※※※※※
    Call SQLWriter(strSQL, "New")
End Sub

2019-03-28

ExcelでSQLを使う-015: SQL ORDER BY 節多重ソート

動物のお医者さん　第７章

　鷹匠になることを夢見た小学生のハムテルは、ある日、道端で60円のひよこを買った。それはやがて、主人にまで蹴りをいれる恐るべきニワトリに成長した。いくたびかの苦難をを乗り越えて西根家にたどりついた気の強い仔猫は、そのままおばあさんにもらわれ、今では近所のパトロールに余念のない毎日である。アフリカでウイッチ・ドクターとして崇められていた篠原教授の威厳は、日本でも不動のままである。強いものはいくつになっても強いのだった。（同書の帯より）

◇◇◇◇◇

ORDER BY 節

　前項までは、検索した行の並びには関心を払いませんでした。しかし、検索結果を並び替えることも SQL文の重要な役割りです。ORDER BY節を使うことで、SELECT節に指定した列のいずれかを基準として検索した行を特定の順序で並び替えるができます。

　例えば、得意先台帳から＜名前＞と＜読み＞を抽出し＜読み＞の五十音順に並べることもできます。

　SELECT 氏名,読み FROM [得意先台帳$A1:F11] ORDER BY 読み

　本稿では、得意先台帳から＜読み＞昇順で抽出した＜名前＞を同じバイト数の文字列に左詰めして、かつ＜読み＞を付加してプリントするマクロを紹介します。

Sub Test()
Dim I    As Integer
Dim N As Integer
Dim strSQL    As String
      Dim Customers() As String
      Dim strName As String
Dim strYomi    As String

strSQL = "SELECT 氏名,読み FROM [得意先台帳$A1:F11] ORDER BY 読み"
Customers() = Split(DSelect(strSQL), "|")
N = UBound(Customers) - 1
For I = 0 To N
   strName = CutStr(Customers(I), ";", 2)
   strYomi = CutStr(Customers(I), ";", 3)
   Debug.Print SetL(strName, String(20, "-")) & strYomi
Next I
End Sub

' ==================

' 文字を左詰めする

' ==================

Public Function SetL(ByVal Text1 As String, ByVal Text2 As String) As String
Dim I As Integer
Dim J As Integer
Dim L As Integer
Dim M As Integer
Dim N As Integer

J = Len(Text1)
L = LenH(Text2)
For I = 1 To J
M = LenB(StrConv(Left(Text1, I), vbFromUnicode))
If M > L Then
   SetL = Left(Text1, I - 1) & Left(Text2, L - N)
   Exit Function
Else
   N = M
End If
Next I
SetL = Text1 & Left(Text2, L - N)

End Function

' ==================

' 文字を右詰めする

' ==================

Public Function SetR(ByVal Text1 As String, ByVal Text2 As String) As String

Dim I As Integer
Dim J As Integer
Dim L As Integer
Dim M As Integer
Dim N As Integer

J = Len(Text1)

L = LenH(Text2)
For I = 1 To J
M = LenH(Mid$(Text1, 1, I))
If M > L Then
SetR = Left$(Text2, L - N) & Left$(Text1, I - 1)
Exit Function
Else
N = M
End If
Next I
SetR = Left$(Text2, L - N) & Text1

End Function

' =============================

' 文字列の長さをバイト数で返す

' =============================

Public Function LenH(ByVal Text As String) As Integer

LenH = LenB(StrConv(Text, vbFromUnicode))
End Function

多重ソート

　ORDER BY 節に複数の列をカンマで区切って指定して、”多重ソート”することができます。次は、顧客情報を最初に＜住所1＞で並び替え、次に＜読み＞を基準に地区ごとのグループを並び替えます。

　SELECT 名前, 読み, 住所1 [得意先台帳$A1:F11]
ORDER BY 住所1, 読み

2019-03-28

ExcelでSQLを使う-014: SQL WHERE節の活用－３ INとLIKE

動物のお医者さん第６章

　チョビがポスターのモデルにスカウトされた！再び犬ぞりレース出場の日も近い！さて、会社員になったのに、毎日大学に出没する菱沼を尻目に、ハムテルは卒論を仕上げ、獣医師国家試験の勉強に励む毎日である。二階堂も、ネズミと闘い、猫に導かれながら、ハムテルと共に博士課程への進学を決めるのであった。そして、とうとう卒業式を迎える。愛犬・平九郎と離れる清原君を筆頭に、皆、晴れ晴れとした寂しさを感じないではいられなかったー。（同書帯より）

WHERE 節の高度な活用－３

集合: IN

　WHERE節のキーワードINに値を列挙することで、その値を持つ列を抽出することができます。

　＜列名＞ [NOT] IN (＜値1＞,＜値2＞,...＜値n)

　SELECT * FROM 得意先一覧 WHERE 郵便番号 IN (8010002, 8010003)

　上記の例は、郵便番号が 801-0002 と 801-0003 に該当する得意先を選びだします。

　SQLツールDSelect関数は、次のようにキーワード IN を使って特定の集合の列を抽出することができます。

? DSelect("SELECT * FROM [得意先台帳$A1:E100] WHERE 郵便番号 IN (8010002, 8010003)",,Chr(13))
1;3;山田　太郎;8010002;香川県;丸亀市
2;4;木下　三郎;8010002;香川県;丸亀市
3;5;津田　淳;8010003;香川県;坂出市
4;6;内田　洋行;8010003;香川県;坂出市

パターン照合: LIKE

　WHERE節にキーワード LIKE を使って、特定の文字列を含む行を抽出することができます。

　WHERE ＜文字型列＞ LIKE '＜値＞'

　＜文字型列＞には、CHAR型の列の名前を記述し＜値＞には文字列をシングルクォーテーション（’）で囲んで指定します。＜値＞の文字列には、次のようなワイルドカード文字列を使うことができます。

%	0個以上の文字に対応します。
_	任意の１文字に対応します。
[...]	各カッコ内に指定した文字に対応します。
[!...]	各カッコ内に指定した文字以外に対応します。

　次に、キーワードLIKEを使ったDSelect関数の例文を幾つか紹介します。

? DSelect("SELECT 氏名, 住所1 FROM [得意先台帳$A1:E100] WHERE 氏名 LIKE '山%'",,Chr(13))
1;山田　太郎;香川県
2;山下　馨;香川県

? DSelect("SELECT 氏名, 住所1 FROM [得意先台帳$A1:E100] WHERE 氏名 LIKE '_下%'",,Chr(13))
1;木下　三郎;香川県
2;山下　馨;香川県
3;大下　宏;香川県

? DSelect("SELECT 氏名, 住所1 FROM [得意先台帳$A1:E100] WHERE 氏名 LIKE '[!山,木,内]%'",,Chr(13))
1;鈴木　一郎;香川県
2;中村　主水;香川県
3;津田　淳;香川県
4;佐野　順次;香川県
5;吉田　宏;香川県
6;大下　宏;香川県

🔶🔶🔶🔶🔶

　ハスキー流「SQLリファレンス」も、残すところ2節となりました。

3-3、ORDER BY 節
3-4、複数の表からの検索

　これが終われば、いよいよSQLツール《SQLWriter》の活用ノウハウ編になります。今暫く、お付き合いください。

2019-03-28

ExcelでSQLを使う-013: SQL WHERE節の活用－２ BETWEENとAND

動物のお医者さん　第５章

　病気になったヒヨちゃんは、ガンもとばさないし、蹴りもしない・・・のでちょっと寂しい。チョビの出生の秘密があかされたり、永遠に研究室の主であろうと誰もが疑わなかった菱川聖子がついに就職口を見つけたり、相変わらず次々と事件は続く。そして、北海道名物の（？）犬ぞりレースの季節がやってきた。謎の男・ブッチャー氏にその才能を認められたハムテルとチョビは、「オレはやるぜ、やるぜ」のリーダー犬・シーザーと共に、果敢にレースに挑むのであったー。（同書の帯より）

◇◇◇◇◇

WHERE 節の高度な活用－２

範囲: BETWEEN と AND

　WHERE節では、キーワード BETWEENと ANDを使って特定の範囲の値を検索するように条件を設定することができます。

WHERE <列名> [NOT] BETWEEN <式> AND <式>

　次は、2018年8月度の売上伝票を選択する例です。このように、選択範囲が月初と月末の場合には、選択開始日と選択終了日をGetDate関数を用いてBETWEENとANDに指定します。

　SELECT * FROM 売上日 [売上伝票$A1:H100]

WHERE

BETWEEN

GetDate("2018/05/01"), 1

AND

GetDate("2018/05/01"), 99

初日、末日、締日の取得

　BETWEENとANDを用いるのは、月次締切り処理などで集計処理範囲を指定する場合などです。その際に、当月の初日と末日、前月の締日と当月の締日などの指定は必須です。それには、GetDate関数を用います。

Public Const 初日 = 1
Public Const 末日 = 99

Public Const 締日 = 20

? GetDate("2018/08/10",初日)    ← 2018/08 の初日
2018/08/01
? GetDate("2018/08/10",初日,-1)    ← 前月の初日
2018/07/01
? GetDate("2018/08/10",初日,1)     ← 来月の初日
2018/09/01
? GetDate("2018/08/10",初日,,1)    ← 来年8月の初日
2019/08/01

? GetDate("2018/08/10", 末日)    ← 2018/08 の末日
2018/08/31
? GetDate("2018/08/10",末日,-1)   ← 前月の末日
2018/07/31
? GetDate("2018/08/10",末日,1)     ← 来月の末日
2018/09/30
? GetDate("2018/08/10",末日,,1)    ← 来年8月の末日
2019/08/31

? GetDate("2018/08/10", 締日) ← 2018/08 の締日
2018/08/20
? GetDate("2018/08/10",締日,-1)    ← 前月の締日
2018/07/20
? GetDate("2018/08/10",締日,1)     ← 来月の締日
2018/09/20
? GetDate("2018/08/10",締日,,1)    ← 来年8月の締日
2019/08/20

Public Function GetDate(ByVal N As Date, _
   Optional Hiduke As Integer = 1, _
   Optional MoveM As Integer = 0, _
   Optional MoveY As Integer = 0) As Date
Dim IntAjust As Integer

      If Hiduke = 99 Then
           MoveM = MoveM + 1
           Hiduke = 1
           IntAjust = -1
      End If
      GetDate = DateSerial(DatePart("yyyy", N) + MoveY, _
DatePart("m", N) + MoveM, _
Hiduke) + IntAjust
End Function

　次は、DSelect関数とGetDate関数を使って2018年5月度の売上伝票を選択しています。

? DSelect("SELECT * FROM [売上伝票$A1:E100]

　　　　　　　　　WHERE 売上日

　　　　　　　　　BETWEEN

　　　　　　　　　#" & GetDate(FormatDateTime("2018/05/01"), 1) & "#

　　　　　　　　　AND

　　　　　　　　　#" & GetDate(FormatDateTime("2018/05/01"), 99) & "#",,Chr(13))

1;3;20190003;2018/05/01;3;山田　太郎
2;4;20190004;2018/05/02;4;木下　三郎
3;5;20190005;2018/05/03;5;津田　淳
4;6;20190006;2018/05/04;6;内田　洋行

? DSelect("SELECT * FROM [売上伝票$A1:E100]

　　　　　　　　 WHERE Str(売上日)

　　　　　　　　 BETWEEN

　　　　　　　　 '" & GetDate(FormatDateTime("2018/05/01"), 1) & "'

　　　　　　　　 AND

　　　　　　　　 '" & GetDate(FormatDateTime("2018/05/01"), 99) & "'",,Chr(13))

1;3;20190003;2018/05/01;3;山田　太郎
2;4;20190004;2018/05/02;4;木下　三郎
3;5;20190005;2018/05/03;5;津田　淳
4;6;20190006;2018/05/04;6;内田　洋行

＜売上伝票＞

＜売上伝票検索＞

　SQLツールユーザーは、以上のようなテストを踏まえて表＜売上伝票検索＞を作成することになります。

　オペレーターが入力するのは、＜検索月＞のみです。＜初日＞と＜末日＞は、式で生成します。

　＜売上伝票＞を検索する式は、C07に書きます。検索した値から＜伝票番号＞＜売上日＞＜顧客名＞を取り出して表示するには、CutStr関数をネストします。

C07=DSelect("SELECT * FROM [売上伝票$A1:E100]

WHERE Str(売上日)

BETWEEN

'"& TEXT(E6,"yyyy/mm/dd")&"'

AND

'"& TEXT(F6,"yyyy/mm/dd") & "'")

C09=CutSTr(CutSTr(C7, "|",1), ";",1)
D09=CutSTr(CutSTr(C7, "|",1), ";",3)
E09=CutSTr(CutSTr(C7, "|",1), ";",4)
F09=CutSTr(CutSTr(C7, "|",1), ";",6)

　VBAでのプログラミングなしでの仕組み。それをセットアップするのは、確かに、少々面倒です。しかし、一度、仕組んでしまえば、＜検索月＞を入力するだけで表＜売上伝票検索＞が更新されます。是非、挑戦して貰いたいと思います。

　本稿は、ここまでとします。集合、パターン照合については、次稿で紹介します。

2019-03-28

ExcelでSQLを使う-012: SQL WHERE節の活用－１関係演算子とAND、OR

動物のお医者さん　第４章

　6月でも北海道は寒い。なにしろ、ストーブで、”しるこドリンク”を温めて飲む漆原教授の姿も見られる日もあるのである。研究室に秘書はいないし、想像を絶する教授の奇行に、学生たちも患畜たちも大弱り・・・。向かうところ敵なしの彼に挑むのは、乱暴者ヒヨちゃんか、おしゃべりな畜主か、はたまた昔の恩師なのか？そんな環境のなかで、５年生になっても吹き矢作りにいそしむハムテルに、突然与えられた使命は、漆原教授に代わって獣医学会で研究発表することだったー。（同書帯より）

◇◇◇◇◇

　本稿からは、[イミディエイトウインドウ]でのテストに加えて、シートの式にSQLツール関数を書いた運用例も示していきます。

WHERE 節の高度な活用－１

　WHERE 節では検索条件を指定します。SELECT文のなかでも重要な節です。

関係演算子

　WHERE 節には、次の関係演算子を使って、列と定数との比較条件を記述することができます。

    --------------------------------------------------
      記号        意味
    --------------------------------------------------
       =          等しい
       <>        等しくない
       !=         等しくない
       >          大きい
       >=        大きいか、もしくは等しい
       <          小さい
       <=        小さいか、もしくは等しい
    ---------------------------------------------------

　例えば、関係演算子を使って平均販売単価を算出する商品区分を指定することだできます。

　SELECT AVG(販売単価) FROM [商品台帳明細$A1:H1000] WHERE 区分=1

? DLookup("SELECT AVG(販売単価) FROM [商品台帳明細$A1:H1000] WHERE 区分=1")

1756.6667

複数の検索条件の設定: AND と OR

＜売上伝票＞

　WHERE節に複数の検索条件を設定する時は、検索条件をキーワード AND もしくは OR で結合します。

SELECT * FROM 売上伝票 WHERE 顧客名='佐野　順次' AND 売上日 >= #2019/03/05#

　上記の例は、＜顧客名＞が '佐野　順次'さんで＜売上日＞が 2019年03月05日以後の＜売上伝票＞を選択します。下記の例は、同じ検索を DSelect関数で行っています。

? DSelect("SELECT * FROM [売上伝票$A1:E100]

WHERE

顧客名='佐野　順次'

AND

FormatDateTime(売上日) >= #2019/03/05#")

7;20190007;2019/03/07;7;佐野　順次

《売上伝票検索》

　DSelect関数を利用すれば、左図のような＜売上伝票＞を参照するシートを簡単に制作することができます。

・列C07の式=DSelect(SQL文)

　列C07の式には、イミディエイトウインドウで確認したDSelect(SQL文)を書いています。ただし、'佐野　順次' の部分は、E05と置き換えています。また、#2019/03/05#もE06と置き換えています。

　ちょっと、面倒な置き換え作業ですが、一度置き換えてしまえば、次からはユーザーがE05とE06を入力すれば、＜売上伝票＞の参照情報は自動更新されます。しかし、言うは易く行うは難しの、この置き換えです。まあ、そこを行うのが、エクセルシステム開発者の意地と根性の見せ所。コツは、３つのステップで置き換えることです。

=DSelect("SELECT * FROM [売上伝票$A1:E100]

　　　　WHERE 顧客名='佐野　順次'
　　　　AND
　　　　FormatDateTime(売上日) > #2019/03/05#")
　↓
=DSelect("SELECT * FROM [売上伝票$A1:E100]
　　　　WHERE 顧客名='" & XXXXX & "'
　　　　AND
　　　　FormatDateTime(売上日) > #" & Text(XXXXX ,"yyyy/mm/dd")& "#")
　↓

=DSelect("SELECT * FROM [売上伝票$A1:E100]
　　　　WHERE 顧客名='" & E5 & "'
　　　　AND
　　　　FormatDateTime(売上日) > #" & TEXT(E6,"yyyy/mm/dd") & "#")

　DSelect関数は、列区切り子と行区切り子を引数で指定いないと”；”と”｜”を採用します。ですから１行目のデータは、CutStr関数で容易に取り出せます。

? DSelect("SELECT * FROM [売上伝票$A1:E100] WHERE ID<3")

1;20190001;2019/03/01;1;鈴木　一郎|2;20190002;2019/03/02;2;中村　主水

? CutStr(DSelect("SELECT * FROM [売上伝票$A1:E100] WHERE ID<3"), "|",1)

1;20190001;2019/03/01;1;鈴木　一郎

? CutStr(DSelect("SELECT * FROM [売上伝票$A1:E100] WHERE ID<3"), "|",2)

2;20190002;2019/03/02;2;中村　主水

　それぞれの列データを取り出すには、CutStr関数をネストで書きます。

Public Function CutStr(ByVal Text As String, _

   ByVal Separator As String, _
   ByVal N As Integer) As String
    Dim strDatas() As String

    If N > 0 Then
        strDatas = Split("" & Separator & Text, Separator, , 0)
        CutStr = strDatas(N * Abs(N <= UBound(strDatas)))
    End If
End Function

日付をStr関数で扱う

? DSelect("SELECT * FROM [売上伝票$A1:E100] WHERE 顧客名='佐野　順次' AND Str(売上日)>'2019/03/05'")

7;20190007;2019/03/07;7;佐野　順次

=DSelect("SELECT * FROM [売上伝票$A1:E100]

　　　　WHERE 顧客名='" & E5 & "'

　　　　 AND
　　　　 Str(売上日) > '" & E6 "'")

と、日付をStr関数で扱うこともできます。

　テストは、十分とは言えませんが、（現時点では）日付型と時刻型を文字列として扱う簡便法での検索ミスは起きていません。Str関数の利用は、一つの有力な選択肢かもしれません。

　以上が、上図の《売上伝票検索》の仕組みの全てです。

◇◇◇◇◇

　ハスキー流「SQLリファレンス」の「第３章２項：WHERE節の高度な活用」は、

関係演算子
複数の検索条件の設定: AND と OR
範囲: BETWEEN と AND
集合: IN
パターン照合: LIKE

の5つの小見出しを持ちます。範囲、集合、パターン照合については、稿を改めて紹介します。

　VBAでのプログラミングゼロでSQL言語を使うってことは、SQLツールを開発することも課題ですが、その活用ノウハウを確立することも重要な課題です。今暫く、その確立を目指すハスキー流「エクセルでSQLを活用するためのリファレンス」にお付き合いください。

🔶　🔶　🔶

　それにしても、split関数の登場は、CutStr関数の書き方を一変させました。その昔は、次のように書いたものです。自分で書いたにも関わらず、目で見ただけでそのアルゴリズムを理解することは不可能です。split関数の登場は、一種の革命でした。

Public Function CutStr(ByVal Text As String, _

ByVal Separator As String, _
ByVal N As Integer) As String
     Dim I As Integer
     Dim J As Integer
     Dim K AS Integer
     Dim L AS Integer

     Text = Text & Separator
     L = Len(Separator) - 1
     For I = 1 To N
         J = K + 1
         K = Instr(J. Text, Separator, vbTextCompare) + L
     Next I
     IF K > J Then
         CutStr = Mid$(Text, J, K - J - L)
     End If
End Function

2019-03-28

ExcelでSQLを使う-011: SQL 算術演算子と算術関数 SUM, AVG,MIN,MAX

動物のお医者さん　第３章

　バイトに研修に実習、４年生になったハムテルたちは、獣医師への道まっしぐらーとそう順調にはいかないようで、元競走馬の世話やら牛の出産立ち合いやら動物園での飼育係体験やらで、もう大変。チョビの”あ・そ・ぼ”とミケの蹴りに力づけられつつ（？）、着実に経験を積んでいきます。異常な動物好きの二階堂の弟妹たち、菱沼に絶対服従の犬・源三や見合い相手まで登場して、獣医学部の日々は過ぎていく。試験シーズンはもう目前ー！（同書の帯より）

　明後日には、出産師が我が家のラブラドール・レトリバーを迎えにきます。犬の出産は、とてつもない一大イベントです。複数のベテラン出産師が昼夜交替で仔犬を取り上げます。そして、出産二日目にはママ犬が仔犬を連れて帰ってきます。そうなれば、24時間体制での仔犬の世話に突入。本ブログが一応の完結を迎えるかどうかは、微妙なところとなった。

◇◇◇◇◇

算術演算子

　SELECT 節に列記した数値型の列に対しては、次の算術演算子を使うことができます。

---------------------------

      記号        機能
    ---------------------------
       +          加算
       -          減算
       *          乗算
       /          除算
       %          剰余
    ---------------------------

　算術演算子を使うことにより、表＜商品台帳明細＞の基準販売単価を5％値上げした値を検索することができます。算術演算子を利用すると、検索結果の型が通貨型ではなくなります。そのために DSelect関数は、通貨型のフォーマットを適用しません。通貨型にフォーマットして表示したいのであれば、FormatCurrency関数を使って整形する必要があります。

? DSELECT("SELECT 販売単価 * 1.05 FROM [商品台帳明細$A1:Z10]",,Chr(13))1606.51764
1764
1764
1932

? DSELECT("SELECT FormatCurrency(基準販売単価 * 1.05,2) FROM [商品台帳明細$A1:Z10]",,Chr(13))
¥1,764.00
¥1,764.00
¥1,932.00

　単なる小数点を含む桁揃えをしたいのであれば、FormatNumber関数を使います。

? DSELECT("SELECT FormatNumber(基準販売単価 * 1.05,2) FROM [商品台帳明細$A1:Z10]",,Chr(13))

1,764.00
1,764.00
1,932.00

※FormatDateTime関数

? DLookup("SELECT 日付 FROM [Sheet2$A1:H100] WHERE FormatDateTime(日付)=#2019/02/11#")

2019/02/11

? DLookup("SELECT 時刻 FROM [Sheet2$A1:H100] WHERE FormatDateTime(時刻)=#22:30:00#")

22:30:00

算術関数

　SELECT 節に列記した数値型の列に対しては、次の関数を使って計算することができます。

*COUNT()**	WHERE節の条件を満たす行の総数を算出します。
SUM(＜列名＞)	指定した列の値について合計を算出します。
AVG(＜列名＞)	指定した列の値について平均値を算出します。
MAX(＜列名＞)	指定した列の値の最大値を求めます。
MIN(＜列名＞)	指定した列の値の最小値を求めます。

　集計関数に列を指定する時は、列の名前をカッコで囲みます。一つの SELECT節に複数の関数を記述する時には、関数をカンマで区切ります。

SELECT AVG(販売単価) AS avg_price

FROM 商品台帳明細

SELECT AVG(販売単価), MIN(販売単価), MAX(販売単価)

FROM 商品台帳明細

? DSelect("SELECT FormatCurrency(AVG(販売単価), 2) AS avg_price

　　　　 FROM [商品台帳明細$A1:G100]")

¥1,682.00

? DSelect("SELECT AVG(販売単価), MIN(販売単価), MAX(販売単価)

FROM [商品台帳明細$A1:G100]")

1682;1530;1840

集計関数とGROUP BY 節

＜商品台帳明細＞

　集計関数を GROUP BY 節とともにSELECT文と組み合わせて使うと、強力な処理を行うことができます。次の例では、区分ごとの平均販売単価を求めています。

? DSelect("SELECT 区分, AVG(販売単価) FROM [商品台帳明細$A1:H100] GROUP BY 区分",,Chr(13))

0;1530

1;1680

2;1840

? DBSelect("SELECT 区分, AVG(販売単価) FROM 商品台帳明細 GROUP BY 区分","D:\DB1.mdb",,Chr(13))

0;1530
1;1680
2;1840

不具合発生メモ！

　先頭行にnull行が発生した。原因は目に見えない空行の存在だ。Access相手のDBSelect関数では、あり得ないこと。実に、エクセル固有の現象だ。どうやら、《目に見えない空行が存在します！》という警告を出す必要がありそうだ。

にほんブログ村

2019-03-28

ExcelでSQLを使う-010: SQL 重複行の除去ーキーワード DISTINCT

動物のお医者さん　第２章

　西根家にはいろいろな動物が出入りする。ヒヨちゃんに輪をかけた乱暴者のメンドリコンビ。思いっきりワガママな仔犬スコシ。酒飲みのスナネズミ。緊急治療に飛び込む近所の人々ー。そいて、極めつきは、ドイツに住む音楽家・ハムテルの両親の登場！なにごとにも動じないマイペースなハムテルの性格形成は、この環境のもとでなされたのだった！？３年生も終わりに近づき、漆原教授の講座を選ぶハムテルの未来は明るいのかー？（同書の帯より）

◇◇◇◇◇

　本稿では、SELECT文を高度に活用するための複雑な文について説明します。