SQLについて質問です。


データベースの中で、指定した項目の文字個数をカウントしてを保存したいと考えています。

★テーブル名 TEST

★項目名 KOUMOKU1 , KOUMOKU2

例えば テーブル TEST にはデータが20個位入っていて

KOUMOKU1 に りんご という文字が入っているとします。

テーブル TEST には 

KOUMOKU1 にりんごと書かれているデータが5個あります。

SQL命令を実行すると

KOUMOKU2 に 5 という数字が入ります。

これを1~20全てのデータに適応させて KOUMOKU2 に全て数字が入る形を作りたいと考えています。

SQL命令がわかるかたおりましたらよろしくお願いします。

※データベースはPhpMyAdminを使用しています。 


回答の条件
  • 1人2回まで
  • 登録:2009/09/04 02:03:03
  • 終了:2009/09/04 16:16:25

ベストアンサー

id:kn1967 No.1

kn1967回答回数2915ベストアンサー獲得回数3012009/09/04 08:06:16

ポイント100pt

【1】更新における制限事項

UPDATE TEST T1
SET KOUMOKU2 = (SELECT COUNT(*) FROM TEST T2 WHERE T2.KOUMOKU1 = T1.KOUMOKU1)

上記のように書けば「いけそうな気がする~~~♪」などと思うのですが、エラーになります。

UPDATEでは更新されるテーブル(TEST)と同じテーブルをサブクエリ内では指定できないため、

自身(今回であればTESTテーブル)の集計結果を、

自身(同じくTESTテーブル)に書き戻すという動作は一発ではそもそも無理なのです。


【2】対応策その1

以下のように処理を分ければ一応は望みの形になります。

(ステップ1)集計用テーブル(仮にTEST2とする)を空っぽにしておく。

DELETE * FROM TEST2

(ステップ2)集計する。

INSERT INTO TEST2
SELECT KOUMOKU1, COUNT(*) AS KOUMOKU2
FROM TEST
GROUP BY KOUMOKU1

(ステップ3)集計結果を TEST に書き込む。

UPDATE TEST T1
SET KOUMOKU2 = (SELECT KOUMOKU2 FROM TEST2 T2 WHERE T2.KOUMOKU1 = T1.KOUMOKU1 LIMIT 1)

【3a】対応策その1の問題点:時差による誤差の発生

以下のような事が起こりえます。

(ステップ2)集計する。

     ↓

  ステップ2に進むまでの僅かな時間に、

  TESTテーブルから「りんご」と入ったレコードが1件消された。あるいは増えた。

  (1件だけとは限らない。もっともっと多いかもしれない。)

     ↓

(ステップ3)集計結果を TEST に書き込む。


ステップ1で集計した時と、ステップ2で書き込むまでの僅かな時差により、

本来得たい結果とは違うものが書き込まれるという事態になってしまう訳ですが、

作っておられるシステムはオークション出品商品の管理が目標であり目的ですから、

この問題をはらんでいる事は大きな問題になりえると思われます。

(「一応は望みの形」と書いたのは、このような問題点を抱えているからです。)


【3b】対応策その1の問題点:複数アクセスによる誤差の発生

間違って集計ボタンを2回クリックしてしまった場合などは、

同じプログラムが2つ動くことになり、しかも、連携は行なっていませんから、

むちゃくちゃなデータが書き込まれたりする可能性があります。


対応としては、

テーブルにロックをかけるなどがありますが、そもそも「対応策その1」自体が、

今件への解決策とは言えず、ロックの話などしだすと話が長くなるだけなので説明は割愛。


【4】対応策その2

対応策というほどのものでもありませんが、

ただ単に、同種のものが何点あるかという事を知りたいだけであれば、

SELECT T1.KOUMOKU1, (SELECT COUNT(*) FROM TEST T2 WHERE T2.KOUMOKU1 = T1.KOUMOKU1) AS KOUMOKU2
FROM TEST T1

といったような具合に、照会をかける度に計算させて、

「照会時点での個数を得る」という形にするほうがよろしいかと思います。

id:aiomock

ご回答ありがとうございます。

問題解決しました。ありがとうございます。

2009/09/04 16:16:13

その他の回答(1件)

id:kn1967 No.1

kn1967回答回数2915ベストアンサー獲得回数3012009/09/04 08:06:16ここでベストアンサー

ポイント100pt

【1】更新における制限事項

UPDATE TEST T1
SET KOUMOKU2 = (SELECT COUNT(*) FROM TEST T2 WHERE T2.KOUMOKU1 = T1.KOUMOKU1)

上記のように書けば「いけそうな気がする~~~♪」などと思うのですが、エラーになります。

UPDATEでは更新されるテーブル(TEST)と同じテーブルをサブクエリ内では指定できないため、

自身(今回であればTESTテーブル)の集計結果を、

自身(同じくTESTテーブル)に書き戻すという動作は一発ではそもそも無理なのです。


【2】対応策その1

以下のように処理を分ければ一応は望みの形になります。

(ステップ1)集計用テーブル(仮にTEST2とする)を空っぽにしておく。

DELETE * FROM TEST2

(ステップ2)集計する。

INSERT INTO TEST2
SELECT KOUMOKU1, COUNT(*) AS KOUMOKU2
FROM TEST
GROUP BY KOUMOKU1

(ステップ3)集計結果を TEST に書き込む。

UPDATE TEST T1
SET KOUMOKU2 = (SELECT KOUMOKU2 FROM TEST2 T2 WHERE T2.KOUMOKU1 = T1.KOUMOKU1 LIMIT 1)

【3a】対応策その1の問題点:時差による誤差の発生

以下のような事が起こりえます。

(ステップ2)集計する。

     ↓

  ステップ2に進むまでの僅かな時間に、

  TESTテーブルから「りんご」と入ったレコードが1件消された。あるいは増えた。

  (1件だけとは限らない。もっともっと多いかもしれない。)

     ↓

(ステップ3)集計結果を TEST に書き込む。


ステップ1で集計した時と、ステップ2で書き込むまでの僅かな時差により、

本来得たい結果とは違うものが書き込まれるという事態になってしまう訳ですが、

作っておられるシステムはオークション出品商品の管理が目標であり目的ですから、

この問題をはらんでいる事は大きな問題になりえると思われます。

(「一応は望みの形」と書いたのは、このような問題点を抱えているからです。)


【3b】対応策その1の問題点:複数アクセスによる誤差の発生

間違って集計ボタンを2回クリックしてしまった場合などは、

同じプログラムが2つ動くことになり、しかも、連携は行なっていませんから、

むちゃくちゃなデータが書き込まれたりする可能性があります。


対応としては、

テーブルにロックをかけるなどがありますが、そもそも「対応策その1」自体が、

今件への解決策とは言えず、ロックの話などしだすと話が長くなるだけなので説明は割愛。


【4】対応策その2

対応策というほどのものでもありませんが、

ただ単に、同種のものが何点あるかという事を知りたいだけであれば、

SELECT T1.KOUMOKU1, (SELECT COUNT(*) FROM TEST T2 WHERE T2.KOUMOKU1 = T1.KOUMOKU1) AS KOUMOKU2
FROM TEST T1

といったような具合に、照会をかける度に計算させて、

「照会時点での個数を得る」という形にするほうがよろしいかと思います。

id:aiomock

ご回答ありがとうございます。

問題解決しました。ありがとうございます。

2009/09/04 16:16:13
id:HALSPECIAL No.2

HALSPECIAL回答回数407ベストアンサー獲得回数862009/09/04 11:17:26

ポイント35pt
UPDATE TEST AS TEST1 SET KOUMOKU2 = (
    SELECT COUNT(1) FROM TEST AS TEST2
    WHERE  TEST2.KOUMOKU1 = TEST1.KOUMOKU1
)

こんな感じでSQL1発を期待していますか?

MySQLはUpdate, Deleteで自己相関サブクエリが使えない のでSQL1発では無理です。


PHPでグルグル回しながらセットしていくか、

MySQLだけで完結したいのであれば、

ストアドプロシージャを使うといいです。


あとはテンポラリテーブルを使ってSQL2発で実装するとかです。

id:aiomock

ご回答ありがとうございます。

2009/09/04 14:25:44

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません