SELECTでgroup byした時の挙動
- 質問文章からSELECTでgroup byした時の挙動についての説明と、titleカラムが取得される条件について質問があります。
- 質問の内容は、TBL1というテーブルからaddressとtitle、countの情報を取得して、addressごとにcountを合計して並び替えるSQL文についてです。
- 具体的なSQL文の例を挙げながら、titleカラムがどのように取得されるか、そしてtitleが取得されないケースがある理由について質問しています。
- ベストアンサー
SELECTでgroup byした時の挙動
環境は PHP5.3.3 MySQL5.0.95 です。 現在TBL1にURL(address)と、そのURLのタイトルタグの中身(title)と、独自に数えてる数字(count)を入れています。 その1つのアドレスに対してcountの数は色々あるので、 そのcountをアドレスと対にして合計したもの(SUM)で並び替えています。 (分かりづらかったらすいません。addressは主キーではないということです。) タイトルに関しては最近取得し始めたところで入ってるところと入っていないところがあります。 その状況下で SELECT address, title, sum(count) as SUM FROM TBL1 where address LIKE "%hoge.com%" group by address order by sum DESC LIMIT 10; こういうSQL文を使ってSELECTした際に、 hoge.com以下の例えばhoge.com/1.html, hoge.com/2.html, ... といった感じのURLごとにSUMの数で並び変わるのですが この場合titleはどのカラムを参照していることになるのですか？ (address) (title) (SUM) hoge.com/1.html | test1 | 500 | hoge.com/2.html | test2 | 400 | hoge.com/3.html | | 300 | hoge.com/4.html | test4 | 200 | hoge.com/5.html | test5 | 100 | hoge.com/6.html | | 40 | hoge.com/7.html | test7 | 20 | hoge.com/8.html | | 10 | hoge.com/9.html | | 6 | hoge.com/10.html | | 5 | こんな結果だったとして、titleが入っているものと入っていないものの区別がつきません。 入っていないURL(例えばhoge.com/3.html)のaddressカラムも全て空というわけではありません。 逆に入っているものに関しては、全ての行にtitleが入っているわけではありません。 主キーは今回のSELECTとは関係してない"ID"です。 ID | address | count | title 1 | hoge.com/1.html | 3 | test1 2 | hoge.com/1.html | 10 | 3 | hoge.com/1.html | 43 | test1 ID | address | count | title 1 | hoge.com/3.html | 4 | test3 2 | hoge.com/3.html | 3 | 3 | hoge.com/3.html | 42 | それぞれは主キーに応じてcountが個別にある状態です。 上記は正確ではありませんが、こんな感じです。 こうして共に入ったり入ってなかったりするのにSELECTした結果に 入ってるものと入ってないものが出てくるのが分かりません。 ちなみに、検証すると一番countが多い行にtitleが入っていたらSELECT文にも出てくるというわけではないようです。 なぜtitleが出るものと出ないものがあるのでしょう？ ご教授宜しく御願い致します。
- man_ahoaho
- お礼率16% (2/12)
- MySQL
- 回答数2
- ありがとう数0
- みんなの回答 （2）
- 専門家の回答
質問者が選んだベストアンサー
MySQL の仕様では、GROUP BY した際に一意にならないカラムをSELECT句に指定した場合は、そのグループに属する値のどれかを返します。その中のどれを返すかについては特に基準は有りませんので、不確定です。 詳しくは下記のページを参照して下さい。 http://dev.mysql.com/doc/refman/5.1/ja/group-by-hidden-fields.html なお、 null 以外は同じ値になるのでしたら、MAX 関数などを使えばその値が取得できるかと。 例) SELECT address, MAX(title), SUM(count) as SUM FROM ・・・
その他の回答 (1)
（素人目からすると）「挙動は未定義」な気がします。 とりあえずtitleが潰れてしまうのを回避するために group by address, title とすることは出来るとは思いますが・・・
関連するQ&A
- SQLのcount()とgroup by
以下のようなテーブルがあったとき、 tbl1 id | score 1 | 100 1 | 200 1 | 300 2 | 200 2 | 100 3 | 300 3 | 400 3 | 500 3 | 600 idでまとめて個数を出すとすると、 SELECT count(*) FROM tbl1 group by id とすれば count(*) 3 2 4 と結果が返ってきますが、この結果にidを足して id | count(*) 1 | 3 2 | 2 3 | 4 というようなSQLはどうやって書けばいいのでしょうか？ MySQLでは、 SELECT id, count(*) FROM tbl1 group by id と書けるのですが、これは標準的なSQLではＮＧなそうなので、気になって質問しました。 よろしくお願いします。
- ベストアンサー
- その他(データベース)
- 別テーブルのカラムを利用してソートしたい
別テーブルのカラムを利用してソートしたい MySQLバージョン4.1.16を使用しています。 テーブル「tbl1」をテーブル「tbl2」のcountというカラムを利用して ソートしたいのですが、どのようなSQL文になるのでしょうか？ 「tbl2」のidというカラムは外部キーで「tbl1」のidと関係しています。 テーブル「tbl1」 +------+---------+ | id | userid | +------+---------+ | 1 | tanaka | | 2 | sato | +------+---------+ テーブル「tbl2」 +------+---------+ | id | count | +------+---------+ | 1 | 10 | | 2 | 3 | +------+---------+ 次のような文かなと思ったのですが、エラーが返ってきます・・・ SELECT * FROM tbl1 ORDER BY (SELECT id FROM tbl2 ORDER BY count)
- ベストアンサー
- MySQL
- 結合で重複のエラーが出る
mysql5.1.33を使用しています。 次のような構成で、 テーブル「tbl1s」 id int(11) テーブル「tbl2s」 id int(11) テーブル「tbl1s_tbl2s」 id int(11) tbl1_id int(11) tbl1sの外部キー tbl2_id int(11) tbl2sの外部キー テーブル「tbl2sbelongs」 id int(11) tbl2_id int(11) tbl2sの外部キー count int(11) テーブル「tbl1s」 +------+ | id | +------+ | 1 | +------+ | 2 | +------+ | 3 | +------+ テーブル「tbl2s」 +------+ | id | +------+ | 1 | +------+ | 2 | +------+ | 3 | +------+ テーブル「tbl1s_tbl2s」 +------+-------+-------+ | id | tbl1_id | tbl2_id | +------+-------+-------+ | 1 | 1 | 1 | +------+-------+------+ | 2 | 1 | 2 | +------+-------+------+ | 3 | 3 | 3 | +------+-------+------+ テーブル「tbl2sbelongs」 +------+-------+-------+ | id | tbl2_id | count | +------+-------+-------+ | 1 | 1 | 3 | +------+-------+------+ | 2 | 2 | 10 | +------+-------+------+ | 3 | 3 | 15 | +------+-------+------+ SELECT * FROM `tbl1s_tbl2s` AS `Tbl1sTbl2` INNER JOIN (SELECT *, sum(Tbl2sbelong.`count`) as sum FROM `tbl2s` AS `Tbl2` INNER JOIN `tbl2sbelongs` AS `Tbl2sbelong` ON (`Tbl2`.`id`=`Tbl2sbelong`.`tbl2_id`) WHERE 1 = 1 GROUP BY `Tbl2`.`id` ) AS `SUB` ON (`Tbl1sTbl2`.`tbl2_id`=`SUB`.`id`) WHERE 1 = 1 GROUP BY `tbl1_id` を実行すると、 「SQL Error: 1060: Duplicate column name 'id' 」 というエラーが出てしまいます・・・ おそらくカラムのidが結合の中で重複しているという内容だと思うのですが、 ３つもテーブルが絡んでくるとどれのidがというのが分からないのですが、 これは何が原因なのでしょうか？ ちなみにサブクエリの SELECT *, sum(Tbl2sbelong.`count`) as sum FROM `tbl2s` AS `Tbl2` INNER JOIN `tbl2sbelongs` AS `Tbl2sbelong` ON (`Tbl2`.`id`=`Tbl2sbelong`.`tbl2_id`) WHERE 1 = 1 GROUP BY `Tbl2`.`id` この段階までは実行できます。 最終的には、「tbl2sbelongs」テーブルの「count」の値によって、「tbl1s」を降順に並べ替えたいと思っています。 この例だと テーブル「tbl1s」 +------+ | id | +------+ | 3 |count ＝ tbl2_id「3」の15 ＝ 15 +------+ | 1 |count ＝ tbl2_id「1」の3 ＋ tbl2_id「2」の10 ＝ 13 +------+ | 2 |count ＝ 0 +------+
- ベストアンサー
- MySQL
- group byを使う時に条件をつけたいです。
どう頑張っても思いどうりにDBからデータがとれず困ってます... 例えばこんなテーブルがあったら（テーブル名が"test"、カラムが"name"と"id"） table test name | id ---------- aa | 1 aa | 1 aa | 1 bb | 1 bb | 1 aa | 0 aa | 0 cc | 0 カラム"id"が0の時は、カラム"name"のgroup byせず カラム"id"が1の時だけ、カラム"name"のgroup byするには、どうすればいいのでしょう？ このような感じにデータを取り出したいのです。 name | id | COUNT ---------- aa | 1 | 3 bb | 1 | 2 aa | 0 | 1 aa | 0 | 1 cc | 0 | 1 よろしくお願いします。
- ベストアンサー
- MySQL
- group by したものをsumしたのですが方法がわかりません。
ある情報をgroup byしその数をcountし、そのcountした数をsumしたのですがどのようにすればよろしいでしょうか？ 例えば ＤＢ名 test a | ------ ABA | A | AA | ABA | select a,count(*) count from test group by a; a |count| ------------- ABA | 2 | A | 1 | AA | 1 | このカウントの数字を一回でsumすることは可能でしょうか？ 宜しくお願いします。
- 締切済み
- MySQL
- 主キーが文字型での結合
主キーが文字列型のカラムhoge_idがあります。 (hoge_idは英数字8文字とします) hoge_idで、他のテーブルに結合します。 また、Webからアクセスする必要があり、URLのパラメータで、 ?hoge_id=A1B2C3D4 の様に、主キーを渡す必要もあります。 文字列を主キーにする方法に慣れていないせいか、気持ち悪く感じます。 主キーを別途serial型のカラムidを作成し、 hoge_idには、ユニークキーで設定をし、 結合や、URLのパラメータは主キーのidを使う方法はあまり意味がないでしょうか。 アドバイスを頂けませんでしょうか。 よろしくお願い致します。
- ベストアンサー
- PostgreSQL
- MYSQLでgroup by を教えてください。
まだ初心者なんです。よろしくお願いいたします。 テーブル$tbl_nameに日付date コラムに (2009-11-3 14:25:06).(2009-11-3 11:25:06). (2009-11-5 12:25:06).(... ....と年齢 ageコラムに (18)(19)(20)... 性別コラムage に (danshi)(jyoshi) が入っています。 これを日付別 に内容が存在するだけ出力したいのです。 出力内容は（18）→25、（19）→13、（20）→7、の数だけ、 同様に（danshi）→20、（jyoshi）→25、というように 〇月〇日18才は25人、19才は13人、20才は7人、 男子は20人、女子は25人と出したいのです。 $sql = "SELECT 'age',count(age) FROM $tbl_name group by 'date'"; $result = mysql_query($sql, $db) or die("クエリの送信に失敗しました。<br />SQL:".$sql); while ($row = mysql_fetch_assoc($result)){ $age .=$row;} 等記入しているのですが、何とも動きませんのです。 どうしたらよろしいものでしょう。よろしくお願いいたします。
- 締切済み
- MySQL
- GROUP BYで集約されるときのソートを変えたい
GROUP BYで集約されるときのソートを変えたい MySQLの5.1を使用しています。 テーブル"tbl"には、"no","id","score"の3つのフィールドがあり、 "no"は主キーです。 +---+----+-------+ | no | id | score | +---+----+-------+ | 1 | 10 | 10002 | | 2 | 10 | 10000 | | 3 | 10 | 10008 | | 4 | 11 | 10004 | | 5 | 12 | 10006 | +---+----+-------+ 上記の表から、 scoreの値が高い順にグループ化してソートしたいのですが、 以下のSQL文ではscoreが最初に登録されたものに集約されてからグループ化されてソートしてしまうため、 思い通りの結果が得られずに困っています。 ↓具体例 SELECT no, id, score FROM tbl ORDER BY score DESC GROUP BY id; +---+----+-------+ | no | id | score | +---+----+-------+ | 5 | 12 | 10006 | | 4 | 11 | 10004 | | 1 | 10 | 10002 | +---+----+-------+ ↓欲しいソート順 +---+----+-------+ | no | id | score | +---+----+-------+ | 3 | 10 | 10008 | | 5 | 12 | 10006 | | 4 | 11 | 10004 | +---+----+-------+ テンポラリテーブルはなるべく使用せずソートしたいのですが、 このような事は可能なのでしょうか？ ご存知の方がいらっしゃいましたら教えていただけると助かります。 よろしくお願いします。
- ベストアンサー
- MySQL
- SQLで部分的にGROUP BYしたいとき(その２
こんばんわ。 先日こちら(http://okwave.jp/qa/q7666702.html) で質問させていただきましたが、まだ問題があったため、 再度質問させていただきます。 以下のような20カラムある「出納帳」テーブルがあって、 同一日付の金額を集約してしまいたいです。 出納帳 ID 日付 金額 伝票 ・・・(全20カラム) 1 2012/8/20 1000 A01 ・・・ 2 2012/8/20 2000 A02 ・・・ 3 2012/8/21 -1000 B01 ・・・ 4 2012/8/21 -2000 B02 ・・・ 5 2012/8/22 3000 A03 ・・・ 6 2012/8/23 4000 A04 ・・・ 7 2012/8/24 -3000 B03 ・・・ 8 2012/8/24 -4000 B04 ・・・ 9 2012/8/27 5000 A05 ・・・ ↓「日付」で金額集約 ID 日付 金額 伝票 ・・・(全20カラム) 1 2012/8/20 1000 A01 ・・・ 2 2012/8/20 2000 A02 ・・・ 2012/8/21 -3000 ・・・(-1000と-2000を集約) 5 2012/8/22 3000 A03 ・・・ 6 2012/8/23 4000 A04 ・・・ 2012/8/24 -7000 ・・・(-3000と-4000を集約) 9 2012/8/27 5000 A05 ・・・ と集約キー以外はNULLにしたいのですが、 またまた、 ( SELECT ID, 日付, 金額, 伝票, ・・・ FROM 出納帳 WHERE 金額>=0 UNION ALL SELECT NULL, 日付, SUM(金額), NULL, ・・・ FROM 出納帳 WHERE 金額<0 GROUP BY 日付 ) ORDER BY 日付 とUNIONをする方法しか思いつかなかったのですが、 UNIONせずに端的に表現できるSQLはありますでしょうか？
- 締切済み
- Oracle
- MySQLでWHEN句のサブクエリ中にて
MySQL ver.5.0.95です。 PHPからSQL文を作ってDBへインサートしているのですが 重複した値が合った場合はupdateするように書いています。 TBL1には |id |string |count 1 aaa 0 2 bbb 3 3 ccc 1 TBL2には |id2 |string2 |count2 1 aaa 0 2 bbb 2 3 ccc 1 実際はもう少し複雑ですが、こういう感じでデータが入ってるとします。 このとき、TBL1のそれぞれをインサート若しくはアップデートするのに TBL2の同一IDのものの、count2の状態によって場合分けしたく 同一IDのcount2が1以上なら変更しない、という風にするため以下のように書いたのですが insert into TBL1 values (1, "aaa", 3), (2, "bbb", 3), (3, "ccc", 3), (4, "ddd", 3) on duplicate key update count = CASE WHEN (select count2 from TBL2 where id2 = values(id)) >=1 THEN count ELSE count + values(count) END; #1064 - You have an error in your SQL syntax;というエラーになって出来ません。 WHENの中のサブクエリで、where id2 = values(id) 恐らくこの文がダメなようです。 例えば決め撃ちで1つ1つ書くと通ります。 insert into TBL1 values(1, "aaa", 3) on duplicate key update count = CASE WHEN (select count2 from TBL2 where id2 = 1) >=1 THEN count ELSE count + values(count) END; insert into TBL1 values(2, "bbb", 3) on duplicate key update count = CASE WHEN (select count2 from TBL2 where id2 = 2) >=1 THEN count ELSE count + values(count) END; insert into TBL1 values(3, "ccc", 3) on duplicate key update count = CASE WHEN (select count2 from TBL2 where id2 = 3) >=1 THEN count ELSE count + values(count) END; insert into TBL1 values(4, "ddd", 3) on duplicate key update count = CASE WHEN (select count2 from TBL2 where id2 = 4) >=1 THEN count ELSE count + values(count) END; これをエラーになったような文に変えて1文にまとめたいのですが、無理でしょうか？ あとそもそもこれをまとめたほうが速いと思ってそうしたいのですが、 変わらないのなら1文1文;でつなげて書くやり方でいこうと思っています。 まとめると速くなることはあるでしょうか？
- ベストアンサー
- MySQL
