台湾製のパソコンのせいか、ときどき文字化けしますが、極力なおしているつもりです。(名の字が吊に化けたりする。）


なお、一通りやったので、SQLはプライベートパソコンからは、削除するつもり。(2024/09)

かなりこみいった文章になったのでマトメを列挙しておこう。
ODBC記述は�A武井DBのなかの記述がまとまっている感じがする。（特に(2)が重要かな）
SQL認証(Windows認証より武井氏は推奨している)は私はうまくいかなかった時期があったが、最近可能になった。
(ゆうさんのSQL データベース作成--URL記述--の説明どおりやったらうまくいったので、私としてはこれを推奨)
ただし、武井データベースのみSQL認証も可能というだけで、武井氏推奨とはことなりWindows認証が楽かな。？
Inner JoinやLeft Outer Joinについては重要だが記述があまりに薄い感じがするのでほかのURLを参考にされたい。
JapanDBではWikipediaの情報をもとに47レコードを記載。ただ最近Wikipedia更新があったやもしれない。
数値の表記について詳述しているが、(12345ではなく12,345)どうそろえてもSQL Serverは左詰めになってしまう。
雑多なブログはPivot TableをSQLまたAccess（クロス集計)での実現など、ほかにも学ぶことが多い。
CSVの取り込みはBULK INSERTの方法もあるが、124,537件もある郵便番号などはMatumotoYoshio（ホチキス先生）のほうを推奨する。
Microsoft Ace.OLEDB.12.0の登録がないなどの問題はあるがホチキス先生の利用価値は結構ありそうである。
NorthWindJという日本語のNorthWindがダウンロードできるサイトやSQLとAccessの構文の違いなどTsWareのサイトは非常に参考となる。





SQLは特にMicrosoftは無償で公開しており、企業でなければお試しで導入して試すことができる。DataBaseの上限とか企業版より
反応が若干遅いなどはあるようだが、あまり制限と感じることもない。
昔はSQL Server6.5(Windows95の頃だったと思う)などで、それこそ企業で使いこなした(こなすほど使ったわけでもないが)
わけだが、今ではSQL Server Management Studioを経由してSQL2022を使いこなすということになる。
20-30件のデータとかでSQLはもったいないだとかあるやもしれないがいわゆるお試し体験記として、以下を読んでいただければ
さいわいです。いくつかの失敗も交えた体験記録です。
--特殊文字が含まれていたため、普通では文字化けしてしまったため、かきなおしとなった。(2022/5/11)

SQL Serverに関して、Select云々といったビューをSQL内部でなく、外部にxxx.sqlとして保存することがある。そのほうがコメントなど
書き込めて便利ともいえるためでもあるが。その際にfrom で指名するデータテーブルはフルにデータベース+dbo+テーブルで
記述したほうが良いだろうと思われる。クエリ(ビュー)の画面の上のほうにデータベース名が出ていたりする(masterやxxxDBなど)
がこれとSQLを実施するデータベースが異なるとエラーになることがあるため。(Select XXX from XXXdb.dbo.AAAtable)

　　　 �@SQL導入　Microsoftのページからダウンロードできる。
https://www.microsoft.com/ja-jp/sql-server/sql-server-downloads　
大概の場合は、そのページの中のExpress(DeveloperかExpressか)でダウンロードすればいいと思う。
Windows版であるが。Windows、Linux、Docker コンテナに導入可能とうたっている。Mac版は無さそうである。
そういえばAccessもMac版はなかったかな。
いづれにせよここでSQL Serverをダウンロード & 導入し、そのまま続けてSQL Server Management Studioの導入を促されるので
導入することとなる。また、同じページのリンクにSQL Server Management Studioの導入サイトがあるので、そこから
落とすほうが良いともいえる。というのは間違いないと思うが、日本語Versionをおとすべきなので。
https://learn.microsoft.com/ja-JP/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16　
いままで、何回もダウンロードしているがSQL Serverは容量が大きく落とすのに時間がかかりその後の導入にも時間がかかる。
例えば、SQL ServerはSQL2022-SSEI-Eval.exeもSQL2022-SSEI-Expr.exeも4191kbだがこのモジュールはダウンロードを促す
だけである。(Evalは Evaluation ExprはExpressの意味と思う）SSMS(SQL Server Management Studio)はSSMS-Setup-JPN.exeで
660,891kbである。

�A 武井誠---Login　SampleDB ODBC SQL
https://www.youtube.com/playlist?list=PLK0z_eKZxdJs53V8Tw_svmfVI1RyhNw33
まずはSSMS(SQL ServerManagement Studio)を立ち上げてみる。Windows認証でパスワードなしでログインできる。
サーバーの種類(T)はデータベースエンジン サーバー名(S)はコンピュータの名前\SQL EXPRESS 認証(A)はWindows認証
ユーザー名とパスワードはWindows認証のためグレイアウトしている。かすかにユーザー名は読むことができるが。
個別のSQL認証のユーザーでの注意点はオブジェクトエクスプローラのセキュリティのログインで規定するのだが、パスワード
ポリシー以下の3つのレ点をはずすことかな。これは武井誠の講義内9回目にSQL Server導入とともに詳しいが後ほどデータベースも
名前変更があったりする点いただけない。また、複数回Server導入するうちにSQL認証でエラーが発生してしまったので、
現在はWindows認証のみになっている。(注釈あり--2023/6/1)

注釈　　すでにいくつかのDB（武井DB/JapanDB/雑多なブログ/..NorthWindJ)登録ののちに
https://www.youtube.com/watch?v=jjoF9vYmsDI　このブログに従い、SQL　Login Userのxxxx（本当は武井DB提案のadmin)
の登録ができた。武井DBのところだけだがSQL Loginが可能となった。一応ほかのＤＢには登録していないせいだろうが
武井DBのMS_ShainListの中身も200件でも1000件でも見れるが、ほかのDB例えばJAPAN DBにはAccessできないと
当然ながら拒否される。一応、SQL Loginも可能という事にして、Window認証でほかの（今後もDBがさらに増える可能性
があるだろう）DBや新規の作成については対応しようと思っている。（武井DBだけSQL認証)

サンプルDBはdbo.MS_BushoListとdbo.MS_ShainListである。(SQLについての後で詳述する。)
12回目のテーブル定義書およびデータがダウンロードできるのでデータは出来上がったテーブルに張り付ければよい。
(Control + CでコピーしControl + Vで貼り付け。)
後述するやもしれないが、SQL ServerにはExcelやCSVからの取り込みなど手段があるが、なぜかExcelを取り込むには
ひとつのモジュールを必要とするのだが、これを実施するとAccessなどの閲覧が出来なくなったりと問題が大きいので
あきらめた経緯がある。(Microsoft Ace.OLEDB.12.0の登録がない）
https://you-1.tokyo/microsoft-ace-oledb-16-0/
環境がことなるが、(Win11Pro(64Bit)<-->Win10Pro(64Bit)、SQLserver2022<-->SQLserver2017、Office2013Pro Premium<-->Ofiiceなし
accessruntime_4288-1001_x86_ja-jp.exeをいれたらとんでもないことになったので削除（コントロールパネルより）した。

　 ODBC設定はMicrosoft Accessで設定するので、順番を追って説明する。映画では015番である。
(1) Accessで外部データを選ぶ。その中で、データベースからSQL Serverからを選ぶ。
(2) 外部データの取り込み-ODBCデータソースというタブが現れるので、2つあるうちの下のほうのリンクテーブルを作成して
　　ソースデータにリンクするを選択する。
(3) データソースの選択画面が出るのでコンピュータデータソースを選ぶ。そこで新規作成ボタンを押し、(ここで警告が出ても無視）
　　データソースの新規作成でユーザーデータソース(このコンピュータにのみ適用がえらばれているはず）の次へを押す。
(4) ここでSQLServerを選び、次へ。そしてさらに次へ。そしてSQL Serverに接続するための新規データソースの画面になる。
(5) 新しいデータソース名はわかりやすい名前(〇〇DBなど)に。接続するSQL Server名はコンピュータの名前\SQL EXPRESSに。
　　説明（2段目)はとくに入力する必要はない。そして次へ
(6) ログイン確認方法となるが上のWindowsログインでパスワードなしにするか下のSQL認証にするかだが個人的なものなので上にしている
(7) 次に、SQL Serverに接続するための新規データソースを作成する画面で既定のデータベースを作成画面で〇〇DBを選ぶ。
(8) さらに次の画面でSQL Serverの言語をJapaneseにチェックを入れて完了である。
(9) 接続テストでうまく接続できればOKである。続けて何をリンクするか(大概はテーブルである）選ぶ画面が出るので選択する。
　　Accessを一旦終了してしまったが、追加でdbo_Viewなども取り込みたいときには
　　上記の(1)(2)(3)まで操作を繰り返し、コンピュータデータソースで〇〇DBを選ぶとテーブルのリンクが表示される。

SQL言語の基本的な流れ(Select * From TakeiDB.dbo.MS_ShainList where 部署コード = 'B0005')は第14回目の講義で学ぶことができる。
https://www.youtube.com/watch?v=wnDfig0J29k&list=PLK0z_eKZxdJs53V8Tw_svmfVI1RyhNw33&index=14&t=450s　
　 WHERE 年齢 >= 38 やWHERE 社員名 LIKE '*司*'/WHERE 年齢 BETWEEN 20 AND 40などやOrder Byそして少しだけ
Left Outer JoinやInner Joinの練習がある。
基本的にAccessはODBC経由(ODBC.accdb)と独自にテーブルを作成したもの(Access.accdb)との２つを作成している。
またSQL文によるクエリを含めたものはSQL Serverで実行するものはフォルダー名　SQL　に格納し編集で内容が見れるように
している。SQL Server本体への格納でもよいのであるが、1000件表示などでSQL命令文を見ると細かい指定が表示上省略されて
しまうこともあっていまひとつである。Access.accdbには場合によってだがSQL Serverで実施した命令文と同様のクエリを
格納している。なぜかというとServerとAccessで微妙に違いがあるからである。(例えばDateDiff("yyyy",[BirthDay],GetDate()) AS Age　
とDateDiff("yyyy",[BirthDay],Date()) AS Ageの違い　つまり現在の日付のSQLとAccessの違い）

(SQL Server内のクエリ--Viewの中身が上位1000件の表示でこれではわからない上都合をきたしている)
よく考えるとデザインで開くとSQL構文がわかるのではあったが。
　　　　SELECT TOP (1000) [ShainCD]
　　　　　　　　,[Jusho] FROM [TakeiDB].[dbo].[View_ワイルドカード]
上記のとおり、上位1000行の選択で表示されるクエリ以外にその上のデザインでは細かい表示(Accessのクエリのデザイン)もありうる。
この、[View_ワイルドカード]は、 dbo.MS_ShainList WHERE (Jusho LIKE '_京%')というものであった。
　 　　　　　　　　(外部保存のSQL)
　　　　SELECT TOP (1000) [ShainCD]
　　　　　　　　,[Jusho] FROM [TakeiDB].[dbo].[MS_ShainList] where Jusho like '東京%'
　　　　　　　　(AccessのODBC接続のクエリ　若干中身を変えている）
　　　　SELECT dbo_MS_ShainList.ShainCD, dbo_MS_ShainList.Jusho FROM dbo_MS_ShainList
　　　　　　　　WHERE (((dbo_MS_ShainList.Jusho) Not Like "*都*"));

武井誠DBとしては上記のように３つのクエリを設けてみた。
ワイルドクエリと生年月日から現年齢を求めるクエリ(年齢計算は本当は簡便な方式ではいけないのだが一応仮の姿で)
プラス　ワイルドカードと年齢の両者の組み合わせなどである。
実際にはDATEDIFF(yyyy, 誕生日, GETDATE()) AS 年齢というものだが、誕生月日が現在日付の月日に到来しているかいないかで
年齢に１歳の差が出ることを考慮してない、また閏年生まれの人が現在日付が2000年と1900年の2月末では29日か28日の
違いがあることも考慮されていない点はあるが簡便方式を採っている。

さらには、社員リスト(dbo.MS_ShainList)と部署リスト(dbo.MS_BushoList)は部署コードをキイにしてつながっているのだが
部署リストにない部署コードを持つ社員をつくってみて、いわゆるInner Join(両方での不一致除外)やLeft Outer Join(不一致は空白で表示
つまり、Inner Joinより１行増える）の違いを確かめたりしている。


�B JapanDB
日本に関するDBはWikiPediaの都道府県のなかに47都道府県に関しての記述がある。項目的にはISO/JIS番号、都道府県、よみ、
都道府県庁所在地、最大都市、旗、地方、人口(人)、面積(km2)、人口密度、市町村数、国会定数(衆参)である。
Excelに張り付けてデータ定義した後、画面貼り付けするだけである。SQL ServerにExcelをタイトル付きではりつけたいところ
であるが、Microsoft Ace.OLEDB.12.0の登録がないということで実際これを取り込んだところ、Accessが開かないなどにっちもさっちも
いかない形となり、あきらめた次第。
SQL SerVerにSSMS(SQL Server Management Studio)経由でJapanDBの都道府県DBを作成。さすがに旗は画像を取り込む手段が(調べれば
わかるだろうが)不明でもあり、また衆参両院の人数は以前あったか不明だが取り込んでいない。いまWikipediaをみると各都道府県の
人数も以前と異なっているようで、なんらかの変更があったのかもしれない。項目に変動が起きた時に、自動的に変更するような機構を
どうやって設けるべきかも研究が必要かもしれない。
なお、ISO/JIS番号はSQL Serverのテーブルの主キイ、である。北海道が1で沖縄県が47である。
SQL Serverのシステムビュー、AccessのODBC、AccessのDBの三種類の(後者2者はクエリ）は3つ作成している。 AccessのODBCについては、
データベース(dbo_都道府県 実施dbo(データベースオーナー)の文字ははずしている）のみODBC経由でありクエリはAccessの中で作成し、
そうはいってもODBC経由のデータベースを相手にしているわけだが、Access内で作成していることに注目すべき。
　　　　(a)AccessのODBC県庁と最大都市（人口）が異なる都道府県
　　　　　　　　WHERE (NOT (都道府県県庁所在地 = 最大都市))
　　　　(b)都道府県が県(府)で終わらない都道府県
　　　　　　　　WHERE (都道府県 NOT LIKE N'%県')　　WHERE (((都道府県.都道府県) Not Like '*府'))
　 　　　　　　　　WHERE (((都道府県.都道府県) Not Like "*県"))
　 　　　N'%県' または　N'*県' のNは省略可能（一応、SQL　ODBC　Accessの順であるが、Accessは無駄に()が多い。）
　　　　(c)人口密度の計算上と統計上の違い(これも三者ならべてみる。長いので折り返しになってしまうかも。SQL Serverだけ色を変えてみる。)
　　　　　　　　SELECT TOP (100) PERCENT 都道府県, Format(人口, '#,##0') AS 人口,ForMat(面積, '#,##0.#0') AS 面積,
　　　　　　　　　　　　Format(人口 / 面積, '#,##0.0#') AS 計算上,Format(人口密度, '#,##0.#0') AS 人口密度
　　　　　　　　　　　　FROM dbo.都道府県　ORDER BY [ISO-JIS番号] 　　SQL Server
テーブルをみると、千単位の区切りはない(Excelの表示上でごまかされるが実態は千単位の区切りはない)北海道を例にとると
5383579(人口) 83424.22(面積) 68.60(人口密度) 千単位は東京の人口密度が6168.10である。
それをSQL Serverのクエリ(ビュー)表示上83,424,22(面積)のように表示させるためこのようなFormatが必要である。
また数字であることに変わりないのだから、右詰めすべきかとは思うが、方法が今は不明である。
Formatを用いず、Str(面積,10,0) つまり、最大値10桁、小数点以下0桁(小数以下省略)で面積を表すと右詰めしているようだが、
岐阜県(10621)と静岡県(7779)の右端に微妙な差があってそろっていない。10621の1と7779の9の位置がそろっていない。

ODBCのAccessは以下のとおり。
　　　　　　　　SELECT 都道府県.都道府県, Format(人口, '#,##0') AS 人口DB, Format([面積],'#,##0.#0') AS 面積DB,
　　　　　　　　　　　　(Format([人口密度],"#,###.0")) AS 人口密度DB, (Format([人口]/[面積],"#,###.0")) AS 計算上,
　　　　　　　　　　　　[人口密度]-[計算上] AS 差異 FROM 都道府県 ORDER BY 都道府県.[ISO-JIS番号];
Access(ODBC)とSQL Serverはそっくりである。人口密度と計算上が順番が異なっているだけでFormatの内容は同じである。
たとえばFormat(人口, '#,##0') AS 人口DBとAS以下にテーブルと同じ名前だとErrorになってしまうのでDBとつけている。
SQL Serverと同じく北海道は5,383,579(人口) 83,424.22(面積) 68.60(人口密度) 千単位は東京の人口密度が6,168.10である
なお、差異は北海道だけ4.1でほかの都道府県は0である。近時、Wikipediaのデータが差し替えられてしまったので
差異を出す楽しみもなくなってしまったかもしれない。

Access(ODBC経由ではない）では以下のとおり。
　　　　　　　　SELECT 都道府県.ISO番号, 都道府県.都道府県, 都道府県.人口, 都道府県.面積, 都道府県.人口密度,
　　　　　　　　　　　　FROM 都道府県;
Accessそのもののフィールド定義で 人口:フィールドサイズ　 精度浮動小数点型　書式　#,##0　　
　　　　　　　　　　　　　　　　　　　　 面積:フィールドサイズ　 精度浮動小数点型　書式　#,#00.00　
　 　 　　　　　　　　　　　　　　　　　　　人口密度:フィールドサイズ　精度浮動小数点型　書式　#,#00.00　　
と定義してある。したがってテーブルおよびクエリは人口、面積、人口密度については右詰め千単位でカンマがある。
ただし計算上は左詰め、差異は数値項目として計算されているので右詰め。北海道のみ差異がでて4.09999999999999である。


　 �C 雑多なBlog(郵便番号　ほかに魚（Pibot Table) 食物（Max関数で上から1番目をとらず、２番目をとるには）
　 https://you-1.tokyo/category/tech/sqlserver/sqlmaster/　
このサイトはSQL初級から上級にいたるまでさまざまな手法を考えることができる。
まずはその前に郵便番号についてとらえよう。(雑多なBlogではSQLの初級から郵便番号に関する手法を問うている）
郵便番号は郵便局がCSV形式で公開しており著作権はフリーである。まずはSQL Serverにデータを取り込むことから
はじめよう。ダウンロードサイトはここである。　https://www.post.japanpost.jp/zipcode/download.html　
レイアウトは自治体Code(4)/旧郵便番号(3)/郵便番号(7)/都道府県カナ/市区町村カナ/町域カナ/都道府県/市区町村/町域/1桁６項目
例えば郵便番号というDBがあるが１件もデータがない場合、CSVは命令文で取り込める。
　　 　　　　　　　　　　　　BULK INSERT　　　　　　　　　　　　
　　　　　　　　　　　　[ZattaDB].[dbo].[郵便番号]
　　　　　　　　　　　　FROM
　　　　　　　　　　　　'D:\KEN_ALL.csv'
　　　　　　　　　　　　WITH(
　　　　　　　　　　　　FORMAT = 'CSV'）
改廃・更新もあるようだが、124,537件もデータがあるようであるが、更新の手段については別途考える必要があるかもしれない。
これについてもSQL ServerとともにAccessでも郵便局データのKen_All.csvはテーブルとして構えている。特にAccessにおいては
郵便番号を入力することで都道府県/市区町村/町域まで自動で入力できてしまうウイザードが備わっており、いろいろなテストデータを
試すことができる。なお、本当は郵便番号が主キイなのだとは思うが、SQL Serverでは主キイなし、Accessでは別にIdxを設け、　
これを主キイとしている。またAccessはモザイクアートチャンネルのFormを主にしたものと独自に事業所住所フォームをも作成を　
主体としたVBAを構築している。(https://www.youtube.com/watch?v=Mxy-LMQZpXs&t=8s)　
郵便番号が主キイと思っていたが、そうではなかった。どうも、何県の何という村だったか調べればわかると思うが、何とか村の
西区でひとつ、東区で一つ、実は南・北区もあって4つのレコードが同じ郵便番号でしかも以下の郵便番号以外という注釈付きであった。
おそらく、なんとか地区でほかの区域は別の郵便番号をつけていたものの代表郵便番号をつけたなんとか地区が東西南北区にわかれてしまったのだろう。
いわゆる市区町村合併ならぬ市区町村分割が発生したが、郵便番号はそのままとしたため、４つのレコードになってしまったのだろう。

肝心なことを言い忘れたので、まずAccessについて言っておこう。CSVそのものでExcelと同じようなものだが、Excelでみると郵便番号は
7桁（ハイフンなし）なのだが、数字はExcelでは前0はみえない。北海道などを例にとると060-0031（ハイフンなしなので0600031前0は無視される）
つまり600031とみえてしまう。これをAccessに取り込むときには外部データ取り込みでExcelではなくテキストファイルの取り込みとなる。
取り込みでカンマ区切りなどの前後でサンプル表示があるが 0600031は確かに前0がついている。秀丸などでCSVの郵便データをみても
同じく前0がついている。
SQLServerもデータベースを右クリックしてタスクのデータのインポートでFlatDataSouseでのファイルを格納する方法もあるのだろうが
上述のBULK INSERT---'CSV')で対応可能である。件数がめちゃくちゃ多いのであまり再現はできないが、Index付き(主キイ）も試したかった
のでCSVを5件だけに絞り、CSVでは数字は”　”が付かないようなので1,2,3,4,5と頭にIDXをつけ、タスクのスクリプトの作成で
主キイ付きのスクリプトはどうすればいいのかをまねて、レイアウトは郵便番号のテーブルを流用して作成してみた。もともとが
主キイのない郵便番号なので自分でIndexなどを作成せざるを得ないが5件ではなく本当の件数の場合にはどうすべきか不明ではある。
ちなみにAccessで郵便番号における重複クエリを実施したところかなりの数の重複が生じている。
なお、確かにBulk Insertより確実で早いのは、データベースを右クリックしたタスクのデータインポートではある。要点がいくつかあるが
元データはフラットファイルソースとするがファイル名を記入するのに実際にファイルを参照する際はCSVファイルである。また、テキスト
修飾子では”が記入される。先頭データ行は列名として仕様はない。さらに列表示で実際の見え方が表示される。詳細設定で型の推測を押さないと
フィールドが50文字分固定になってしまうことがあるので型の推測を押し、出来れば行数はCSVの124572行としたほうが良い。列5が73文字、
列8が76文字など2か所が郵便局データでは重要。次の変換先はMicrosoft OLE DB Provideerfor SQL Serverとして、サーバー名（ｘｘｘ\SQLEXPRESS)
Windows認証などを選ぶ。変換元変換先が表示されるがあらかじめテーブル(がわだけ）を登録していれば、それに変更する。実行でエラーが
なければあっという間に124572行が格納される。参考　
https://matsumotoyoshio.wordpress.com/2014/09/17/郵便番号データを-sql-server-で利用するには（１）-sql-server-2012-m/　
彼によれば主キイ付きは逆にAccessからSQL Serverを生成する方法があるようだ。
https://matsumotoyoshio.wordpress.com/2014/12/19/郵便番号データを-sql-server-で利用するには（２）-デ/
なお、Accessの追加クエリではあるが、SQL文もあるので、加工しながら実行しました。
実験はTEST１、２、３のテーブルをFacicitiousDBに作成したりして行いました。 ODBCをかなり無駄に作成してしまったりしたので
削除したいのだが、https://culage.hatenablog.com/entry/20140904/1409756400
というレジストリ削除で対応しようと思っている。
　　　　 　　　　 　　　　 　　　　 　　　　
SQL ServerはほとんどのSQL(検索）はSQLのビューに格納せず、独自のフォルダーに初級・中級・上級にわけて雑多なBlgに記載のものを
加工して入れている。初級はあいまい検索(where 郵便番号 like'142%'and 町域 like '_中%')とか
グループ化Count(Select 都道府県, count (都道府県) as 市区町村町域数 from ZattaDB.dbo.郵便番号 group by 都道府県),
Avg/Min/Max/Sumなどをとるために市区町村にいくつ町域があるか(num)をとらえた市区町村テーブルを作成したりしている。

　 　　　SELECT 都道府県,sum([num])As 市区町村の町域over5000 FROM [ZattaDB].[dbo].[市区町村テーブル] group by　都道府県
　　　　 having sum([num]) >='5000' order by 都道府県　　SumとHavingの組み合わせなどの事例。
　 　　　　
中級ではSub Query/Row_numberを教えているが事例で示したほうがわかりやすいか
　　　　　　　　SELECT 都道府県,市区町村,num
　　　　　　　　FROM [ZattaDB].[dbo].[市区町村テーブル]
　　　　　　　　where 都道府県
　　　　　　　　in ( SELECT 都道府県 FROM [ZattaDB].[dbo].[市区町村テーブル] where num >='900' )
　　　　　　　　order by 都道府県,num desc
都道府県単位でnumの合計が900以上の明細を出力 (市区町村がひとつでも900件以上の県は900以下の市区町村も表示)　答えは東京都と富山県で
明細77行の市区町村がでる　
これの肝はwhere 都道府県 in(sub query)なのであってwhere num >='900'とSub Queryでなくすると単純に900件以上の2件(港区と富山市)がでる。

Row_numberはhttps://segakuin.com/oracle/function/row_number.htmlの説明がわかりやすいだろう。
　　　　　　　　SELECT salary,ROW_NUMBER() OVER(ORDER BY salary),RANK() OVER(ORDER BY salary),
　　　　　　　　DENSE_RANK() OVER(ORDER BY salary) from emp
つまり、800/950/1250/1250/1300/1500のならびがあったとき 1250が二人いた時のランク付け(順番付け)である。
ROW_NUMBER() は単純に1,2,3,4,5,6の順番付け、RANK()は1,2,3,3,5,6 そしてDENSUKE_RANK()は1,2,3,3,4,5という順番付けとなる。

魚は、テーブルとしては魚の名前と1から12月の月数それに対する単価　売上個数　トータル売価のテーブルに対して
縦(月数）横(魚の名前）のトータル売価　または縦(魚の名前）横(月数）におけるトータル売価をSQL Server/Access(クロス集計)/Excelで
集計してみるということである。　https://you-1.tokyo/pivot/ Webをコピーして動かしてみたときスペースが余分に入って
いたりしてうまくいかなかったりする場合があるので要注意である。まず、SQL Serverは以下の通り。
　　　　SELECT *　FROM (SELECT 月, 名前, 売上 FROM ZattaDB.dbo.おさかな) AS TEMP PIVOT (SUM(売上) FOR 名前 IN ([鰯], [鰹],
　　　　[鯨], [鯛], [蛸], [鱈], [鯱], [鰒], [鰊], [鱸])) AS PVT　
本当は魚の数は10ではなく12です。(要するに、For以下で横列は自由に配列できる。これがない構文もありうるがそうすると
ASCI順に並ぶので以下の1月から12月のならびが10/11/12が先に来てしまう。今回魚を12ならぬ10にしたのは、あと２つが
むずかしい漢字で書ききれなかったため。）

あるいは以下の通り。
　　　　　　　　SELECT * FROM (SELECT 名前,月,売上 FROM ZattaDB.dbo.おさかな) AS TEMP PIVOT ( SUM(売上)FOR 月 IN ([ 1月], [ 2月],
　　　　　　　　[ 3月], [ 4月], [ 5月], [ 6月], [ 7月], [ 8月], [ 9月], [ 10月], [ 11月], [ 12月])) AS PVT

　 Access(クロス集計)では以下の通り。
　　　　　　　　TRANSFORM Sum(魚.売上) AS 売上の合計
　　　　　　　　SELECT 魚.名前　FROM 魚　GROUP BY 魚.名前 PIVOT 魚.月 In (" 1月"," 2月"," 11月");
この""と1月または11月の前の空白をよく見ないと特に10,11,12月の数値をとってこない。　あるいは以下の通り
　　　　　　　　TRANSFORM Sum(魚.売上) AS 売上の合計
　　　　　　　　SELECT 魚.月 FROM 魚 GROUP BY 魚.月 PIVOT 魚.名前;
ピボットテーブルについては数年前だろうが「わえなび」チャンネルという短い動画とサンプルのExcelがあがっている。
このExcelをAccessで実施してみたりして5編くらい試してみた。https://www.waenavi.com/excel_pivottable　
　 　
Excelは省略するが、Excelは月ごとの魚の合計や魚ごとの月の合計（例えば12か月分の合計）が出ているのだが
AccessとSQL Serverはその横の合計表示はない。また、横軸に月を指定するSQLは１月が左に来るが指定しない場合は11月が来てしまう。
逆に言うと、こういった細かいところ（月別なら1,2,3月の順番であって数字と漢字の順番ではない）の指定もあるのかも知れない。
https://qiita.com/fuk101/items/731379d12cd7f5559fb2 Accessのクロス集計で列を固定する というWEB Pageに列については
指定もできるということが書かれていた。プロパティシートのクエリ列見出しに「,」で区切って順番をつけられるとのこと。
それをSQL表示すると上記の通り。(3か月分だけの表示)微妙に" "と1月などの文字との間の空白など間違うと数字の表示がされない。

ほかにも、いろいろなテクニックがあるが、2番目を指摘する方法という題名の考え方についてである。(食物テーブル）
大項目(果物、魚、肉）のなかに中項目(例えばリンゴ、みかん、スイカ、メロン)があり、値段が100、150、3000、5000とあったときに
スイカをあてるというもの。（ただし、果物、魚、肉のすべて2番目をあてるSelect文を考えるというもの。
　　　　　　　　Select * from 　(SELECT 大項目,中項目,値段　
　　　　　　　　　　　　,row_number() over(partition by 大項目 order by 値段 desc ) as num
　　　　　　　　　　　　FROM [ZattaDB].[dbo].[食物])XX
　　　　　　　　　　　　where XX.num='2'
これもRow_Numberを使ったテクニックでしかもSub Queryを使い、それぞれの大項目単位の中で順番がつき、その順番番号が2のものを選ぶ
というもので、これはこれで理解はできるが、事例をたくさんこなしていきたいと思っている。


�D FictitiousDB--架空のデータベースという意訳
架空のデータベースとは自分独自のデータベースを構築してみようという意味合いから独自に構築を考えたものである。
https://yamagata.int21h.jp/tool/testdata/　　という架空の人物の住所録があり、5000件まで自動生成できるというもの。
これをベースに住所録を登録していろいろなものに役立ててみようという考えである。また4日間の入院（たいしたものではないが)で
暇でもあるので、もう1台の小型PCにもSQL Serverを入れて同一環境（すなわち同一のデータ格納をして）で楽しもうとした。
それゆえデータベースのコピー(データベースを右クリックしてタスクからスクリプトの生成）などを行った。
すなわち特定のデータベースを（テーブルもビューも）スクリプトファイルとして保存。ただしこの時、詳細設定で
スクリプトを作成するデータの種類でスキーマのみではだめでスキーマとデータが必要である。あるいはテーブルはあるが
0件の空白の状態のときはデータのみとする必要がある。データベースの構造があってもデータとスキーマであればエラーは出るが
0件の状態であればデータは登録される。この時はデータベースはそれを指してないと、Masterに保存ということには
ならないと思うものの、ほかのデータベースに間違えて登録してしまうことを心配したほうがいい。たとえばビューで
Selectｘｘｘといった検索をする際に異なるデータベースを指しているとデータがありませんというようなエラーが起きることがある。

Delete from 人名　　などは、人名テーブルの中身のデータを削除するが構造は削除しない。

実は雑多なBlogのどこかでLastName別の男女別人数をAdventureWorksというデータベースをもとに検索していたのだが該当の
テーブルが見つからなかったのでこの架空のデータベースで実施してみようとした。
ただ日本人のデータベースなので名前はあるが苗字だけのフィールドはない。名前は「北野 勤」など確かに姓と名の間に半角の
スペースはあるのであるが。そこでExcelに張り付けたデータ(名前）から=Find(" ",Sheet1!B2)　として事例としてはSheet1のB2に
ある名前の何文字目に半角のスペースがあるかを探りそれを5000件であれば5000件にコピーして調べ、さらに1文字の苗字、
2文字の苗字、3文字の苗字に関してLastNameを作成した　=IF(Sheet2!C2=2,LEFT(B2,1),IF(Sheet2!C2=3,LEFT(B2,2),LEFT(B2,3)))
つまりSheet1のB列に姓名がありSheet2で何桁目に空白があるかをC列に記し、Sheet3のD列にB列の姓名からLastNameを記述して
いる。Sheet3はデータベース登録用でA(no) B(姓名) C(姓名フリガナ）D(LastName) E(男女) 以下生年月日など。
　
さて、雑多なblgに記載されている男女別に同一LastNameの人物がそれぞれ何人いるかというSQL文はCaseを使ってスッキリという
タイトルで紹介されている。ただし、日本人と西洋人との違いはあるが、以下のSQL文となる。
　　　　　　　　SELECT 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
　　　　　　　　　　　　[LastName]
　　　　　　　　　　　　,sum(( case when [Gender]='女' then 1 else 0 end)) '女'
　　　　　　　　　　　　,sum(( case when [Gender]='男' then 1 else 0 end)) '男'
　　　　　　　　　　　　FROM [FictitiousDB].[dbo].[人名]
　　　　　　　　　　　　group by LastName
　　　　　　　　　　　　order by LastName
5000名で499行つまり499行に収まっているので、意外と同姓の者が10人前後いるということである。
Accessでは人名静的という1000人の人物住所データベースに対してクロス集計で同様な結果をえられる。

　　　　　　　　TRANSFORM Count(人名静的.[名前]) AS 名前のカウント
　　　　　　　　SELECT 人名静的.[LastName], Count(人名静的.[名前]) AS [合計 名前]
　　　　　　　　　　　　FROM 人名静的
　　　　　　　　　　　　GROUP BY 人名静的.[LastName]
　　　　　　　　　　　　PIVOT 人名静的.[性別];
　
もしかするとSQLでも工夫してできるかもしれないが、Accessでは合計・女・男の3列でいわゆる男女合計で何名が同一LastNameか
出力されるが、SQLでは男女合計の欄はない。ただしどちらもそれで合計はでていない。例えば5000 4100 900みたいなものはない

入院中は気が付かなかったのだが、1000件では変なゴミは発生しなかったのだが、5000名を一度にSQLに登録すると項目の
Mail Addressに最終文字に？というExcelでは表示されなかったゴミが表示されていた。1000件づつにわけて登録しなおそうと
考えている。
　　　=======解決しました！！＝＝＝
全権削除して1000件だけ登録してもフィールドMAILというところにゴミが生じた。方策はないものかとSQLなどをキイにして
インターネットを検索したところなんとか策をみつけだした。次の構文である。
　　　　　　　　UPDATE [FictitiousDB].[dbo].[人名] SET MAIL=REPLACE(MAIL,'?',' ')
構文としては　Update　DataBase Set フィールド名=Replace(フィールド名,'元の文字','変えたい文字') というもの。
一応、変更後NULLにしてもいいのだが、空白文字を一つ入力した。

現在は、Accessを一からやりなおすべくユースフル / スキルの図書館さんのアクセスの使い方・データベース初心者入門講座
を1000人の人名静的を使用してフォームを作成してみたり、郵便番号補助をしてみたり、クエリで可変の質問にしてみたり
している。　https://www.youtube.com/watch?v=70CTB0U0Thg&list=PLSW6preHKht59-8q4rdlGAv9lXWiUgJQ_
作成しては壊して(削除して)もう一度作成してみたりと何回かAccess習得のために繰り返しているが、SQL Serverでの実施をトライ
してみようとも思ってる。何回かトライしているうちに目新しいこともいくつかあるが、意図したものと異なっているものもあり、
列挙してみようと思う。
(1)テーブルを作成後、フォームを作成するが単票形式では使い勝手が悪いので、上に単票下にレコードを表示はいいと思う。
　　　 　　　 フォームのプロパティシートの既定のビュー(分割フォーム）分割フォームの方向(データシートを下に）
(2)上にも述べた通り。テーブルデータは既存の50件ほどのデータを転用しているが郵便番号から住所を自動入力するには
　　　郵便番号の下1桁を入力し直すと機能する。(オリジナルデータは郵便番号に対応したものがすでに住所で埋まっているので)
(3)コマンドボタンでデータ追加入力する際、コードビルダーを選ぶのかと思ったが、マクロビルダーを選んでできる点が秀逸である。
(4)クエリでは抽出条件で汎用性を持たせるため[何歳か入力してください]などと[]の中に文言を書き込める。
(5)会員のなかで退職したものを退職者テーブルに追加するクエリを作成する方法はよいがおそらく削除クエリも導入しないと
　　　会員テーブルに残ったままである。
ここら辺を自作したいと思うが、いくつか試してみたいと思う。
上記の(5)については退職者テーブルに会員テーブルとレイアウト同じレコードを追加するクエリと会員テーブルから削除する
クエリはバラバラでは実施可能である。ただ一つのクエリに合体させるとエラーになってしまう。フォームで両クエリを実行
することが　かろうじてできたので良しとしている。対象は退会日がヌルでないこと。(抽出条件Is Not Null)
なお、会員テーブルはIDがオートナンバーの主キイになっているが退職者テーブルはnumericの主キイなし。ここから
会員テーブルに張り付けても(再入会)IDは自動附番される。

さて、ユースフル / スキルの図書館さんのフォームの分割フォーム・データシートを下にはいい提案と思っている。
いままで手掛けてきたデータベースを単票ベースから分割フォームに、もともとAccessのものもSQL Serverのものも、
一挙に手掛けたいと思っている。むしろそのほうが表面上項目がでこぼこだったものも(フォームの項目ごとの右端や
高さなどをそろえればいいのだが)きれいにできる練習にもなる。また、JapanDBでのべた47都道府県のデータは加工する
こともないのでプロパティシートで使用可能をいいえに編集ロックをはいにしたり細かいところを操作したい。そうするとマウスが
そこにはいかない。VBAにもう少し詳しくないとどうすべきか研究中だがIdxの何番目をメインフォームに表示(だまってると、
つねに一番目を表示)するなどのマクロボタンも考えたい。(2023/5/18)
　　　
フォームに対して、マクロを書けば基本的に、例えばテキストボックスに数字を入れ、ボタンを押せば、分割フォームの
明細の何番目に焦点が合い、かつメインフォームにその中身が表示される。日本全国の47都道府県をデータベースにしたもの
でも例えば47をいれ、ボタンを押せば沖縄県がメインフォームに表示され、分割フォームであれば明細行の47番目の
沖縄県の行に記しが付く。前提はISOコード順にデータが格納されていればではあるが。いわゆるISOコード順であると、
北海道が一番で沖縄が47番目となるので。ただし、すべてのフィールドに(県名や人口など)編集可能をYesではなく、Noに
設定するとマクロが有効にならない。もう一つのフィールドである編集ロックはすべてのフィールドがYesでもよい。
なおMacroの中身は次の通り
　　　　 　　　　 If txtNo <= 47 Then
　　　　　　　　　　　　DoCmd.GoToRecord acDataForm, "F_dbo_都道府県", acGoTo, txtNo　
　　　　　　　　Else
　　　　　　　　　　　　Msgbox ("数字かつ47以下をテキストに入力してください")　
　　　　　　　　End IF

ちょっと、唐突だがユースフル / スキルの図書館さんのAccessに会員テーブルがあり、その退職日の記入の有る者は
レイアウトが全く同じ退職者テーブルに移行するというもの（会員テーブルから移し、かつ会員テーブルから削除するという
ものがある。Accessなので追加クエリ(Insert..)と削除クエリ(Delete..)は簡単に書ける。(where 会員テーブル.退会日 is not null)
ただ2つのクエリをひとつづつではなく、一括で実施したいVBAを見つけたので知らせておく。https://tsware.jp/study/indexv12.htm
これの#26で、DoCMD.OpenQuery "作成したクエリの題名"　 というのを連続で2個書けばよいということである。なお、最初と
最後にDoCMD.SetWarning False とDoCMD.SetWarning TRUE をセットすることによりわずらわしい警告が出ないこととなる。
マクロで実行するという方法もあるが、マクロはやっていることがよく見えないという事でVBAがよい。
　　　 　　　
�ENorthWindJ
またSQLServerとAccessでの文のちがいなどの良質なサイトをみつけたのでNorthWindJをもとにしたサイトを紹介しよう。
https://tsware.jp/study/indexv12.htm これだけでなくindexが12ということはほかにも参照すべき点がありそうである。
なお、NorthWindの日本語版があるのもよい参考例になるであろう。以下のサイトである。
http://www.sqlquality.com/Self2016/Self2016_SSAS/Scripts/SQL13_SelfLearning04_SSAS_SampleScript.zip
アクションクエリを複数実行する(no25)VBなど（一旦ほかのテーブルにCopyして(insert)元のテーブルから削除(delete)など)
簡単で便利（Accessそのものではクエリを連続できない)である。