SQLとデータ設計 Archive

表題の件を満たすSQLを使ったのでここに記載しておきます。

参考にしたサイトは以下です。とにかくこのサイトのコンテンツ量はハンパじゃない。管理人さん脱帽します。
そしてすいません、ちょこっと引用させて頂きます。
10-111 最新日で最大金額の行を取得

売上テーブル
コード 日付 金額 商品名
—— ——— —- ——
100 2006/4/19 100 商品あ
100 2006/4/19 50 商品い
100 2006/4/20 200 商品う ←出力対象
200 2006/4/19 200 商品え ←出力対象
300 2006/4/18 100 商品お
300 2006/4/19 300 商品か
300 2006/4/19 600 商品き ←出力対象
400 2006/4/19 800 商品く
400 2006/4/20 100 商品け
400 2006/4/20 200 商品こ ←出力対象
500 2006/5/19 100 商品さ
500 2006/5/20 900 商品し ←出力対象
500 2006/5/20 900 商品す ←出力対象
600 2006/5/20 900 商品せ ←出力対象
600 2006/5/20 900 商品そ ←出力対象

　
コードごとの、日付が最新で、金額が最大のレコードを出力する。
　
出力結果
コード 日付 金額 商品名
—— ——— —- ——
100 2006/4/20 200 商品う
200 2006/4/19 200 商品え
300 2006/4/19 600 商品き
400 2006/4/20 200 商品こ
500 2006/5/20 900 商品し
500 2006/5/20 900 商品す
600 2006/5/21 900 商品せ
600 2006/5/21 900 商品そ

この結果を出力するクエリが欲しいわけです。
で、参考サイトにないやり方で私はやりました。
以下がそれ。

SELECT
  *
FROM
  売上 A
  INNER
  (SELECT
    コード,
    ROW_NUMBER() OVER(PARTITION BY コード ORDER BY
      金額 DESC  NULLS LAST
     ,to_char(日付, 'yyyy/mm/dd hh24:mi:ss') DESC  NULLS LAST) RANKING
  FROM
  売上
  ) B
  ON  A.コード = B.コード  AND B.RANKING = 1

row_number()とpartition byに関しては、こちらのサイトが詳しく説明されています。

もっとがんばなきゃなと思ったエントリでした。（突然なんだ？）

ではでは

和

SELECT * FROM 高校生
  UNION
SELECT * FROM 予備校生;

差

SELECT * FROM 購入者名簿
  EXCEPT
SELECT * FROM 問い合わせ履歴;

共通

SELECT * FROM 高校生
  INTERSECT
SELECT * FROM 予備校生;

#内部結合の例
SELECT 予約者名簿.氏名 FROM 予約者名簿, 購入者名簿
  WHERE 予約者名簿.氏名 = 購入者名簿.氏名;

SELECT 予約者名簿.氏名 FROM 予約者名簿 JOIN 購入者名簿 USING(氏名)

#副問い合わせの例
SELECT 氏名 FROM 予約者氏名
  WHERE EXISTS (SELECT * FROM 購入者名簿.氏名 WHERE  = 購入者名簿.氏名)

商
商（deivision）は”一方の表が他方の表を完全に含む要素だけを取り出す”という演算
たとえば、お店とそこで買える商品を登録した”品揃えリスト表”と欲しい商品を登録した”欲しい物リスト表”があったとします。品揃え表÷欲しい物リストで、欲しい物すべてが購入できるお店の表を得ることができます。
A店・・・野菜
B店・・・洋服、くつ、加工品、クスリ
S店・・・野菜、鮮魚、加工食品
P店・・・洋服、野菜、クスリ、加工食費、画材
欲しい物リスト・・・野菜、加工品

SELECT DESTINCT 店名 FROM 品揃えリスト AS S1
WHERE NOT EXISTS
  (SELECT * FROM 欲しい物リスト
    WHERE NOT EXITSTS
      (SELECT * FROM 品揃えリスト AS S2
        WHERE S1.店名 = S2.店名
          AND S2.商品 = 欲しい物リスト.商品 ));

すべて以下の図書から抜粋：
はじめてでもわかるSQLとデータ設計

多対一

エンティティを時系列に並べたときに多対一となる場合、一対多と同じ要領で多の側に参照キーを入れると、その項目はNULLになってしまいます。

そこで多対一の場合も、多対多と同じように、新しいエンティティを追加します。

多対一の参照キーはNULL項目になる

多対一のリレーションシップでもエンティティを追加する

すべて以下の図書から抜粋：
はじめてでもわかるSQLとデータ設計

ドメインとはデータの集合のことをいい、定義域ともいう。

例、学生テーブルの作成
性別はsex_type型。MまたはFのみ入力可能
成績はgrade_type型。初期値は０で、０～１０の数値が入力可能

CREATE DOMAIN sex_type AS CHAR CHECK(VALUE IN ('M', 'F'));
CREATE DOMEIN grade_type AS INTEGER DEFAULT 0 CHECK(VALUE BETWEEN 0 AND 10);

CREATE TABLE 学生名簿
  (  名前 VARCHAR PRIMARY KEY,
     性別 sex_type,
     成績 grade_type
)

参照トリガーアクション（キー）

• CASCADE:親キーの変更内容を、参照している側のテーブルにも反映する。たとえば、親キーを含む行が削除されたら、参照側も削除されることになる
• NO ACTION:親キーが変更されても、参照側は変更しない。これによて参照制約に違反するデータができてしまう場合、エラーとなり、親キーの変更はできない
• ON DELETE:親キーを削除したときの動作
• ON UPDATE:親キーが変更したときの動作
• SET NULL
• SET DEFAULT

例、仕入伝票テーブルの作成

仕入伝票テーブルの商品コードは、商品マスターテーブルの商品コードを参照する。仕入伝票テーブルに登録されている商品コードのデータは商品マスターから削除できない(ON DELETE NO ACTION)。商品マスターの商品コードを変更したら仕入伝票の商品コードも変更する（ON UPDATE CASCADE）

CREATE TABLE 仕入伝票（
  伝票番号  CHAR(8) PRIMARY KEY,
  注文日     DATE,
  商品コード CHAR(6) REFERENCES 商品マスター（商品コード）
                          ON DELETE NO ACTION
                          ON UPDATE CASCADE,
  数量       INTEGE
）;

制約
ドメイン制約

CREATE TABLE テーブル名
  列名１ CHAR CHECK (VALUE IN ['M', 'F']),
  列名２ INTEGER CHECK (VALUE BETWEEN 0 AND 100),
 .....
);

すべて抜粋：
はじめてでもわかるSQLとデータ設計