- 締切済み
ビュー定義に集約関数を入れる方法を教えてください
mysql5.0.27です。 ビューを定義するところで、集約関数（例えば、count())の結果をビューの属性のひとつにするには、どのように書けばよいのか、教えてください。 例えば、table_Aとtable_Bから、hoge_viewというビューを作る場合。hoge_viewには属性が2つあって、1つはtable_Aのitemという属性をそのまま定義し、もう1つはtable_Bのdataという属性（数字とします）を全レコードにわたって足し合わせたものとしたい時、どのように書けばいいのでしょうか。 下のようなものを試してみたんですが、当然ですが、動きません。 CREATE OR REPLACE VIEW hoge_view ( item, sum_data ) AS SELECT table_A.item, (count(table_B.data)) <---- ここがよくわかりません！ FROM table_A, table_B;
- aneja
- お礼率93% (379/405)
- MySQL
- 回答数1
- ありがとう数1
- みんなの回答 （1）
- 専門家の回答
みんなの回答
・・・AS SELECT table_A.item, count(table_B.data) FROM table_A, table_B where table_Aとtable_Bの結合条件 ←結合条件がないとcross joinになりますよ？例題だから、はしょっただけなのかな？ group by table_A.item; グルーピングすればいいです。
関連するQ&A
- AUTO_INCREMENTのあるテーブルにinsertできません（長文）
mysql5.0.27(RedhatLinux 9)です。 属性のひとつがAUTO_INCREMENTになっているテーブルhogeに、データをinsertしたいのですが、「コラムの数が合致しない」というエラーで、insertできません。 Webをいろいろ漁って、AUTO_INCREMENTの属性は明示的に指定しないようだと理解しました。冗長で申し訳ないのですが、エラーが出た状態のものを、そのまま掲載させていただきます（テーブル名や属性名のみ変更しました）。 状態をまとめると、以下のようになります。 ・下記のテーブル定義で、テーブルは普通にcreateできた。 ・descで確認しても、ちゃんとテーブルはできているよう。 ・下記のinsert文で、その下のエラーメッセージが出て、データを登録できない。 ・どうみても、コラム数は合っている（と思う）。 ・下記のテーブル定義の属性数を適当に減らす（id, item1, item4, item6くらいにする）と、エラーが出ずデータがちゃんとinsertできる。 私はなにか、とんてもない勘違いをしているのでしょうか？かなりの時間試行錯誤しているのですが、一向にわかりません。。。 ===== テーブル定義 ===== CREATE TABLE hoge ( id MEDIUMINT UNSIGNED AUTO_INCREMENT NOT NULL, item1 VARCHAR (64) NOT NULL, item2 VARCHAR (64) NOT NULL, item3 VARCHAR (32) NOT NULL, item4 VARCHAR (64), item5 VARCHAR (64), item6 VARCHAR (16) NOT NULL, CONSTRAINT PK_HOGE PRIMARY KEY (id) ) type=innodb; CREATE INDEX IDX_HOGE_1 ON hoge(item1); CREATE INDEX IDX_HOGE_2 ON hoge(item2); CREATE INDEX IDX_HOGE_3 ON hoge(item3); CREATE INDEX IDX_HOGE_4 ON hoge(item4); CREATE INDEX IDX_HOGE_5 ON hoge(item6); ===== descの出力 ===== +---------------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+-----------------------+------+-----+---------+----------------+ | id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment | | item1 | varchar(64) | NO | MUL | | | | item2 | varchar(64) | NO | MUL | | | | item3 | varchar(32) | NO | MUL | | | | item4 | varchar(64) | YES | MUL | NULL | | | item5 | varchar(64) | YES | | NULL | | | item6 | varchar(16) | NO | MUL | | | +---------------------+-----------------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec) ===== insert文 ===== insert into hoge (item1, item2, item3, item4, item5, item6) values ( 'data1','data2', 'data3', 'data4', 'data5', 'data6'); ===== エラーメッセージ ===== ERROR 1136 (21S01) at line 1: Column count doesn't match value count at row 1 長文大変申し訳ありません。よろしくお願いします。
- 締切済み
- MySQL
- Mysql サブクエリの使い方
table_A ---+------ id | count ---+------ 1 | 5 ---+------ 2 | 6 ---+------ 3 | 7 ---+------ table_B ---+----- id | sub_count ---+----- 1 | 2 ---+------ 2 | 2 ---+------ 3 | 5 ---+------ 1 | 3 ---+------ 2 | 4 ---+------ このようなテーブルで table_Aのcountと、table_Bのidでまとめたsub_countの合計が 一致しないidだけを抽出するために、 SELECT table_A.id FROM (SELECT sum(table_B.sub_count) FROM table_B GROUP BY table_B.id) AS B, table_A, table_B WHERE table_A.id = table_B.id AND table_A.count != sum(table_B.sub_cout) と書いてみましたが、うまくいきません。 ERROR 1111 (HY000): Invalid use of group function 何が悪いのでしょうか？ mysqlも投稿も初心者です。 よろしくお願いします。
- ベストアンサー
- MySQL
- PostgresSQLでテーブル定義の抽出方法
create tableでテーブルを作成し、 comment on table でテーブルに名前を comment on column でアイテムに名前を つけてみました。 これらの作業の成果物を抽出したいのですが、どうやれば良いのでしょうか？ \d テーブル名で Column、Type、Modifiersは確認できますが、 そうでなくて、別のデータベースにテーブルの枠だけをそのまま移植できるような形式で抽出できないでしょうか？ create table tb1 ( item1 char(1) null ); comment on table is 'テーブル１'; comment on column is 'テーブル１のアイテム１'; みたいな状態で抜く事は可能でしょうか？
- ベストアンサー
- PostgreSQL
- ビュー（インラインビュー）で集約した結果と結合するSQLについて
テーブルAを複数のキー項目で集約した結果と、テーブルBを結合しようとしています。 インラインビュー(もしくはビュー)で予め集約を行う、以下のようなSQLを考えました。 候補<1> MAX関数でTableA.列1を絞る select ... from (select max(列1),列2,列3,列4 from TableA group by 列2,列3,列4) TableA2,Table_B where TableA.列2=TableB.列2 and ...; 候補<2> ROW_NUMBER関数で列1の順位を取得し、Where句で順位=1とすることでTableA.列1を絞る select ... from (select 列2,列3,列4 from TableA group by 列2,列3,列4 row_number() over (partition by 列2,列3,列4 order by 列1) RN) TableA2,Table_B where TableA.列2=TableB.列2 and ... and RN=1; ※候補<1><2>で結果が異なることがありますが、ともかく 「インラインビュー(もしくはビュー)で予め集約を行う」がやりたいことです。 しかしこのようなクエリだと、インラインビューでもビューでも、 性能がでません。実行計画を確認したところ、TableA,TableBの結合で生成される レコードごとに毎回集約を行っているようで、膨大なクエリ数が発生していました。 ビューやインラインビューで上記のように集約を行うと危険、とはよく聞きますが... ビューやインラインビューで集約した結果をひとつのテーブルとみなして結合するなど、 レコードごとに集約を行わないようなノウハウがありましたら、お教え願います。
- ベストアンサー
- Oracle
- Access2002 Insert関数が未定義関数となり、エラー
XP Access2002です。 ランキング形式のテーブルから、クエリを作成しています。 クエリでの表示は A1 となっているのですが、それにハイフン「-」を入れて、A-1としたいです。 「A1」は、テーブル名：「ランク」内のフィールド名：「順位」 SQLビューで insert(ランク!順位,2,0,'-') AS ランキング としたのですが、結果は、未定義関数と出てしまいます。 InsertをReplaceに変更したら、今度はアラーとは出ませんが、表示画面では、エラー表示になってしまいます。 構文が違うのでしょうか？ SQLを使っていて、『未定義』とアラートが出たのは初めてですので、戸惑っています。
- ベストアンサー
- オフィス系ソフト
- 数値をNUMBER型にするかCHAR型にするか。
テーブルのカラムで、2005という文字を格納したい場合、 属性として、NUMBER型、CHAR型の選択ができると思います。 ●テーブル名 TABLE1 カラム名 A B 属性 ??? char データ 2005 XXXX ●テーブル名 TABLE2 カラム名 A C 属性 ??? char データ 2005 XXXX このカラムAが、TABLE2のAの2005という値と結合させる （結合させるためのキーとなっている場合） ということがある場合、NUMBER型での定義とchar型での定義に 速度的な観点やその他の観点で、なにか差異みたいなものはありますでしょうか？ 下記のように、値を指定するところでは、シングルコーテーションが必要か不必要かというのは、あるかと思いますが、 結合自体で、差異はありますでしょうか？ ■CHAR型にした場合。 select * form TABLE1,TABLE2 where A='2005' and TABLE1.A=TABLE2.A ■NUMBER型 select * form TABLE1,TABLE2 where A=2005 and TABLE1.A=TABLE2.A
- ベストアンサー
- Oracle
- ACCESS Viewでユーザー定義関数を使いたい
ACCESS2000＋MSDEで開発をしていますが、Viewの中でユーザ定義関数を使用したいのですが、できません。 1> create function dbo.count1(@linkNo int) 2> returns int 3> as 4> 5> begin 6> declare @cnt int 7> 8> select @cnt = count(*) from dbo.TBL_OA where [link] = @linkNO 9> 10> return @cnt 11> end 12> go 1> select dbo.count1(100) 2> go ----------- 1 (1 件処理されました) ここまでは上手く行くのですが、 ACCESS側でViewの列へ count1(100)と記述すると 「ADOエラー：'count1'は関数名として認識されません。」 とエラーになってしまいます。 方法は問いませんが、Viewの中でユーザー定義関数が使用できる方法、ご存知の方いらっしゃいましたら、よろしくお願いします。
- ベストアンサー
- SQL Server
- テーブル定義書作成時のIndex付加について
基礎的なことで申し訳ございませんが、 お力添えよろしくお願いいたします。 CentOs 5 & PostgreSQL 8.4.9にてDB構築しております。 DB構築から、テーブル作成・項目追加・キー設定まで行いまして、 検索する際に、抽出件数が多く負荷のかかりそうなものに Indexを作成しようと考えております。 ■本題 １．Indexを作成する対象となる項目の、目安としてどのようなものが考えられるでしょうか。 ・キー対象の項目になっているもの ・検索条件の対象となる項目なども当たるでしょうか ２．項目をまとめて一つのIndexにするメリット・デメリットはどのようなものでしょうか ３．テーブル結合したときのIndexの動作 例) 下記のようなテーブルがあった場合 tablea(データ50000件程度) hoge_id pkey1 hoge_no hoge_nm ... tableb(データ150000件程度) test_id pkey1 hoge_id pkey2 hoge_betu_kb ... select t1.hoge_id, t1.hoge_nm, t2.hoge_betu_kb rom tablea as t1 leftjoin tableb as t2 on t1.hoge_id = t2.hoge_id where t1.hoge_id > '100' and t1.hoge_nm like 'あいう%' このような場合のIndex作成は、 A. Create index idx_tablea_hoge_id ON tablea (hoge_id); B. Create index idx_tablea_hoge_id ON tablea (hoge_nm); C. Create index idx_tableb_hoge_id ON tableb (hoge_id); ←tableaで指定しているのでこれは不要？ ４．A.B.C.のようなIndex作成した際のテーブル定義の書き方 テーブル定義書のフォーマットは定められており、お門違いな質問かとは存じてはおりますが、 テーブル定義にIndex定義を書く場合に、添付した画像のようなフォーマットの書き方が よく理解できずお気づきの点などございましたら、ご指摘願えたら助かります。 （以前は、CSEツールなどで自動出力される定義書のような書き方をしていたもので・・・） 例）tablea IDX1 IDX2 IDX3 hoge_id 1 hoge_nm 2 このような書き方になるのかどうか？ IDX2, IDX3の項目とは？どのようなIndex定義をした際に書き込むことになるのかが判らず。。。 基礎から勉強しなおすべきとは存じておりますが。。。 現状の期間あまり時間がないことと、 DB構築を一人で行っており、このような内容を相談できるものがおりませんゆえ、 改めて、Index付加することを考えてみると、いまさら聞けないような内容かもしれませんが ちゃんと把握したうえで、テーブル構築したいと思いますので 今までの他の皆様の経験からのノウハウなども踏まえて、 ご教授いただけると大変助かります。
- ベストアンサー
- PostgreSQL
- viewの性能
あるテーブルがあって、Viewがそのままテーブルを参照してます。 そのViewをそのまま参照しているViewが階層的にあるとします。 テーブルA ← View１ ← View2 ← View3 [SQL的イメージは] ・creat table テーブルA (・・・） ・create view1 as select * from テーブルA ・create view2 as select * from view1 ・create view3 as select * from view2 質問したい事として、 View1 を参照するのとView3を参照するのは、View1を参照するのが 階層化が無い分、速いのでしょうか？ それともそのまま参照している場合、あまり速さは変わらないのでしょうか？
- ベストアンサー
- Oracle
- 集約関数のDUPLICATEKEY UPDATE
テーブルmoney、テーブルmoney2にある金額の合計をテーブルmoney3に1日に1回、EVENTでインサートします。money、money2の金額は随時変わるため、最終結果をmoney3に毎日更新していきます。 CREATE table money(g_usr_id INT,g_money INT); CREATE table money2(n_usr_id INT,n_money INT); CREATE table money_all(o_usr_id INT PRIMARY KEY NOT NULL,g_money_all INT,n_money_all INT); 構文 DELIMITER // CREATE EVENT money ON SCHEDULE EVERY 1 DAY STARTS '2012-09-05 00:30:00' ENDS '2013-03-31 00:30:00' DO BEGIN INSERT INTO money_all(o_usr_id,g_money_all,n_money_all) SELECT o_usr_id,SUM(g_money),SUM(n_money) FROM (SELECT g_usr_id AS o_usr_id,g_money,0 AS n_money FROM money UNION ALL SELECT n_usr_id,0,n_money FROM money2) AS T GROUP BY o_usr_id; END; // DELIMITER ; これで初日目の分は無事インサート出来たのですが、翌日以降EVENTがアップデートしようとするとDUPLICATE KEYエラーが出て更新できていません。(当たり前ですが) そこで下記のようにON DUPLICATE KEY UPDATE を付けてみたのですが、エラーになってしまいました。 質問です。 集約関数のON DUPLICATE KEY UPDATE以降はどのように書けばいいか、下記の構文を修正いただけませんでしょうか DELIMITER // CREATE EVENT money ON SCHEDULE EVERY 1 DAY STARTS '2012-09-05 00:30:00' ENDS '2013-03-31 23:30:00' DO BEGIN INSERT INTO money_all(o_usr_id,g_money_all,n_money_all) SELECT o_usr_id,SUM(g_money),SUM(n_money) FROM (SELECT g_usr_id AS o_usr_id,g_money,0 AS n_money FROM money UNION ALL SELECT n_usr_id,0,n_money FROM money2) AS T GROUP BY o_usr_id ON DUPLICATE KEY UPDATE g_money_all=NEW.SUM(g_money),n_money_all=NEW.SUM(n_money) FROM (SELECT g_usr_id AS o_usr_id,g_money,0 AS n_money FROM money UNION ALL SELECT n_usr_id,0,n_money FROM money2) AS T GROUP BY o_usr_id; END; // DELIMITER ; よろしくお願いいたします。(MySQL 5.1です)
- ベストアンサー
- MySQL
お礼
早速のご回答、ありがとうございました。 いろいろやってみたのですが、group byをcountの後につけたりしていたので、ダメだったようです。 where句の後につけるのですね。勉強になりました。 元々のソースはもっと複雑で、簡略化した時に結合条件も一緒に削除してしまっていました。重ね重ね、ご指摘ありがとうございました。