SQL文の質問。使用した日時順で商品を抽出。

Question

MySQLで、JAVAを使っています。

データの抽出SQL文が分からないので、教えてください。

下記の様に、会社名と商品名、商品を使用日時のテーブルがあります。
・company_id=0は、商品の全一覧の基本のレコードです。
・company_id=1000などは、実際に会社が使用した商品とその日時です。

goods_listテーブル

id company_id goods last_use_datetime
1 0 商品１ 2015/01/01 01:01:01
2 0 商品２ 2015/09/12 18:33:43
3 0 商品３ 2015/02/01 01:01:01
4 0 商品４ 2015/01/01 01:01:01
5 0 商品５ 2015/08/25 17:21:43
6 0 商品６ 2015/01/01 01:01:01

7 1000 商品２ 2015/10/13 13:55:12
8 1000 商品６ 2015/09/12 18:33:43
9 1000 商品５ 2015/08/11 11:22:33

10 2000 商品１ 2015/10/11 12:12:12

11 2000 商品４ 2015/08/25 17:21:43
12 3000 商品１ 2015/09/30 19:43:56

・id プライマリーキー
・company_id 複数存在
・goods 商品名
・last_use_datetime 最終使用日時

上記から、特定のcompany_idを指定し、
下記の様に、
・商品でユニークになり、
・使用時間の最近順
で抽出するには、どのようなSQL文を書けばよいでしょうか？

※抽出結果例） company_idに1000を指定した場合の抽出結果。

id company_id goods last_use_datetime
7 1000 商品２ 2015/10/13 13:55:12
8 1000 商品６ 2015/09/12 18:33:43
9 1000 商品５ 2015/08/11 11:22:33
1 0 商品１ 2015/01/01 01:01:01 ・company_id=1000で未使用の基本商品も抽出
4 0 商品４ 2015/01/01 01:01:01 ・company_id=1000で未使用の基本商品も抽出
3 0 商品３ 2015/02/01 01:01:01 ・company_id=1000で未使用の基本商品も抽出

Answer 1

回答する前にお尋ねします。
このテーブル設計は、今からでも変更可能ですか？

このテーブル設計のままでも一応このご質問内容だけの実現は可能ですが、非常にまずい設計であり早晩破綻することは明らかです。

Comments

[退会済みユーザー]

はい、可能です。

[yuba]

では、2つのテーブルに分けましょう。
・商品マスタ
・最終利用履歴
の2つです。
また、商品を商品名だけで表現するのはやめてID番号を振りましょう。これは業務の中では出てこない、システムの中だけの番号としてかまいません。なぜなら、商品名の入力ミスひとつで誤作動が起こることになりますし、文字列同士の一致判定は単純にパフォーマンスを下げますし。
利用履歴は、カラム名から判断するにこれまでのすべての利用履歴ではなく各社・各商品の最終利用だけを記録しているのですよね？

カラム構成は
●商品マスタ "products"
id (int/AUTO_INCREMENT), name(VARCHAR)
●利用履歴 "last_usage"
product_id(int), company_id(int), datetime(datetime)
となります。

last_usageのproduct_idはproductsのidを外部キー参照します。
last_usageの主キーはproduct_id,company_idの複合です。

するとお求めの結果は
```sql
SELECT p.id, p.name, u.product_id, u.datetime FROM products
LEFT JOIN usage AS u ON p.id = u.product_id AND u.company_id = 【与えられた会社ID】
```
で得られます。

[退会済みユーザー]

ご丁寧にありがとうございます。先を見越したテーブルへのご指摘もありがとうございます。早速テーブルを分割して、上記SQLをJAVAで作って、抽出してみます。

[yuba]

last_usageテーブルにdatetimeというカラムを作りましたが、予約語をカラムに使うのはよろしくありませんでしたね。last_usage_datetimeに戻した方がいいです。

[退会済みユーザー]

ご丁寧にありがとうございます。招致しました。ようやく、他の作業や質問の嵐などの介入をかいくぐりながら、テーブルの作成とデータ移行、書きこみ部分が終わったので、このプログラムの開発を上記SQLまでのところにたどり着いたので、今から、SQL文で読み込みを作ります。それで完成になります。

[退会済みユーザー]

抽出までできましたが、普通に１番から順番に抽出されました。
「使用時間の最近順 」じゃないみたいなので、SQLを工夫してみます。できるかな。。。

[退会済みユーザー]

oder byをいれたら、正確に抽出できました。
テーブルの作成やSQLのご提供ありがとうございます。
無事に完成しました。
本当に助かりました。
お手数をお掛けしました。