いつもお世話になっております。
MySQLで、group by関数を使用した際の表示カラムをコントロールしたいのですが、やり方が分りません。
具体的には
id | e_mail | date
1 | t1@t.jp| 2005-05-21
2 | t1@t.jp| 2005-05-22
3 | t1@t.jp| 2005-05-23
4 | t1@t.jp| 2005-05-24
5 | t1@t.jp| 2005-05-25
6 | t1@t.jp| 2005-05-26
7 | t1@t.jp| 2005-05-27
8 | t1@t.jp| 2005-05-28
というデータがあります。
このデータをe_mailでグループ化した場合に、表示されるe_mailを好きなidのものに置き換えたいのです。
通常のSQL文
select id,e_mail,date from table_name group by e_mail
だと、どのidのt1@t.jpというアドレスが表示されるのかコントロールできません。
好きなidやdateのt1@t.jpというe_mailを抽出する方法をご教授頂けると大変助かります。
宜しくお願い致します。
No.8ベストアンサー
- 回答日時:
代表的な書き方を忘れていました。
SQL-92で規定され、SQL Server以外の主要なRDBMSで実装されている「行値式(または行値構成子)」という書き方です。
<e_mail毎に最新の行を得たい>
select *
from t1
where (e_mail,date) in(select e_mail,max(date)
from t1
group by e_mail);
<e_mail毎に最古の行を得たい>
select *
from t1
where (e_mail,date) in(select e_mail,min(date)
from t1
group by e_mail);
#6回答のSQLでは、e_mail、dateしか取り出していませんでした。
それ以外の列も取り出せるようにしたい場合は、以下のように書けます。また、別名を再使用していたので、その部分も変更しました。
<e_mail毎に最新の行を得たい>
select a.*
from t1 as a,
(select x.e_mail,x.date,count(*) as rank
from t1 as x,t1 as y
where x.e_mail=y.e_mail and x.date<=y.date
group by x.e_mail,x.date) as b
where a.e_mail=b.e_mail and a.date=b.date and rank=1
;
<e_mail毎に最古の行を得たい>
select a.*
from t1 as a,
(select x.e_mail,x.date,count(*) as rank
from t1 as x,t1 as y
where x.e_mail=y.e_mail and x.date>=y.date
group by x.e_mail,x.date) as b
where a.e_mail=b.e_mail and a.date=b.date and rank=1
;
ありがとうございます!
いろんな副問合せの形があるんですね。
SQLは奥が深いですね。
本当に勉強になります!ありがとうございました!
No.7
- 回答日時:
#3、#6回答者です。
#6回答のSQLは、以下のような書き方もできます。
<e_mail毎にdateが最新の行を得たい>
select x.e_mail,x.date
from
t1 as x,
(select e_mail,max(date) as max_date
from t1
group by e_mail) as y
where x.e_mail=y.e_mail and x.date=y.max_date
;
<<e_mail毎にdateが最古の行を得たい>
select x.e_mail,x.date
from
t1 as x,
(select e_mail,min(date) as min_date
from t1
group by e_mail) as y
where x.e_mail=y.e_mail and x.date=y.min_date
;
No.6
- 回答日時:
#3回答者です。
RDBMSにおいて、格納順の検索は保証されません。
仮にRDBMS側で、後方追加が原則であっても、前方の領域の行が削除された場合、再利用される場合もあります。
そのため、格納順を意識したい場合は、利用者側で通番やタイムスタンプで意識する必要があります。
<e_mail毎に最新の行を得たい場合>
select *
from
(select x.e_mail,x.date,count(*) as rank
from t1 as x,t1 as y
where x.e_mail=y.e_mail and x.date<=y.date
group by x.e_mail,x.date) as x
where rank=1
;
<e_mail毎に最古の行を得たい場合>
select *
from
(select x.e_mail,x.date,count(*) as rank
from t1 as x,t1 as y
where x.e_mail=y.e_mail and x.date>=y.date
group by x.e_mail,x.date) as x
where rank=1
;
No.5
- 回答日時:
ひょっとして。
「group byしたとき、最古のデータが選択されるので困る」(質問文のケース)
という話でしょうか。
下記にズラズラ書きましたが、出来るというだけで意味はないスクリプトです。
最新のdateでの抽出目的でgroup byするのは意味が無いです。
それは#2、#3さんが書いてあるとおりです。
以下が本題です。
group byしただけだと、データベースの格納順で該当カラムの先頭データが表示されるようです。
逆に言えば、group byする前に用途にあった並びに並び替えないといけない。
つまり、今回の場合はdateを降順にしておかないといけない。
バージョンが5.xであると副問い合わせができるので、降順ソートしておいたレコード群をgroup byしてあげる必要があります。
drop table t3038229;
create table t3038229 (email varchar(30), date varchar(10),comment varchar(30));
insert into t3038229 values
("t1@t.jp","2005-05-22","t1のDB格納先頭"),
("t1@t.jp","2005-05-25","あ"),
("t1@t.jp","2005-05-23","い"),
("t1@t.jp","2005-05-24","う"),
("t1@t.jp","2005-05-26","え"),
("t1@t.jp","2005-05-27","お"),
("t1@t.jp","2005-05-28","t1の最新"),
("t2@t.jp","2005-05-24","t2のDB格納先頭"),
("t1@t.jp","2005-05-21","か"),
("t2@t.jp","2005-05-25","き"),
("t2@t.jp","2005-05-23","く"),
("t2@t.jp","2005-05-26","t2の最新"),
("t3@t.jp","2005-05-25","t3のDB格納先頭"),
("t3@t.jp","2005-05-26","t3の最新");
/* 最新date抽出スクリプト */
select email,date,comment
from (select email,date,comment from t3038229 order by date desc) as a
group by a.email;
/* group by しただけ */
select email,date,comment
from t3038229 group by email;
ありがとうございます!
副問い合わせという事ができるんですね。
今まで使った事がありませんでした。これは便利ですね!
副問合せを使用すれば、やりたいことが全て解決しました!
本当に助かりました!
No.4
- 回答日時:
そもそも「好きな」値を表示(抽出)させたいという要望を実現するのは、「where」です。
置き換えたい場合は、「replace」という関数が使えます。
select id,replace(e_mail,'@t.jp','@softbank.ne.jp'),date from table_name;
皆さんの仰る通り、group byを使ってやりたいことが見えないのでこのような回答になってしまいました。
この回答への補足
ありがとうございます。
すみません。私の説明が全然不足しておりました。
皆さんがおっしゃる通り、group関数でグループ化するなら、idやdateでコントロールする必要はないです。
私がやりたい事は、e_mailでグループ化した際にdateでソートし、最新のt1@t.jpというe_mailのレコードを抽出したいという事なのです。
No.3
- 回答日時:
標準SQLでは、「group by」指定時には、select文の選択リストには、「group by」で指定した列か、max、min、countなどの集合関数しか指定できないことになっています。
<例>
<<正しい例>>
select c1,max(c2),min(c3)
from t1
group by c1
<<誤った例>>
select c1,c2,c3
from t1
group by c1
殆どのRDBMSでは、グルー化列や、集合関数で指定していない列を、select文の選択リストで指定すると、文法エラーにしています。
MySQLでは、このことに関して、拡張仕様を持っています。
http://dev.mysql.com/doc/refman/4.1/ja/group-by- …
これは、
select c1,c2,c3
from t1
group by c1
としても、「文法エラーにはしない」という一方で、「c2やc3は、グループ化の結果、一意な値にならないなら、結果は保証しない」ということです。
利用者は、上記の前提条件を満たす場合にのみ、MySQLの拡張仕様を利用することができます。そうでないなら、この拡張仕様を利用すべきではありません。
No.2
- 回答日時:
そもそもすきなidを選ぶ時点でgroup byするのは整合性がないかと。
select `id`,`e_mail`,`date`
from `table_name`
where `e_mail`='t1@t.jp'
and `id`='1'
で十分では?
No.1
- 回答日時:
私はSQL 系のソフトの使用経験無いんで的外れだったらごめん。
>どのidのt1@t.jpというアドレスが表示されるのかコントロールできません。
そのクエリで表示されるの?
一応これを見ると
http://www.atmarkit.co.jp/fnetwork/rensai/sql03/ …
>「SELECT」句には、GROUP BYで指定した列と集計関数のみを指定することができます。
を満たさないとき
SQL Serverでエラーになっていて
以下と照らし合わせるとMySQLでも同じようなエラーが出るみたいなんだけど・・・
http://sqlzoo.net/howto/source/z.dir/err979/sqls …
http://sqlzoo.net/howto/source/z.dir/err979/mysql
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Oracle sqlで質問です。 idを元にidに紐付くデータで住所コードがjpのみのデータ以外のidを取得したい 4 2023/03/20 17:41
- PHP PostgreSQLからCSV形式でエクスポートする際にカラム内の改行をとる方法 1 2023/02/22 10:05
- Gmail スマホでメールを送るにはどうやりますか?相手のアドレスが、 mail・○○○・co・jpでしたが、、 2 2023/07/24 05:33
- Access(アクセス) アクセス 有効なフィールド名、または式として認識できませんのエラー 3 2022/08/19 11:53
- Access(アクセス) アクセス where句を使用して複数条件抽出をするには 2 2022/08/29 13:24
- PHP php my adminより取り出したデータ表示 2 2022/06/15 11:56
- その他(IT・Webサービス) MacとWindows Google検索した時のURL表示の違いについて 1 2022/11/17 10:38
- システム Recovery 表示 2 2023/04/27 14:38
- MySQL 【MySQL】本当に困っています。詳しい方、ご教授よろしくお願いします。 1 2023/06/03 14:18
- Excel(エクセル) エクセルでの文字抽出 3 2022/07/09 12:17
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
エラー 1068 (42000): 複数の主...
-
Mysql UPDATE出来ません
-
select文のwhere句に配列を入れ...
-
SQLについての質問
-
副問合せの書き方について
-
LEFT JOINが2つあるSQL文でAND...
-
mysqlで変数を使う方法
-
【初心者】特定の文字に色をつ...
-
エクセルの関数について教えて...
-
期間の重複を調べるSQL文につい...
-
ローカルルーターモードとは
-
PL/SQLの変数について
-
SQLサーバから、項目の属性(型...
-
SQLにて特定の文字を除いた検索...
-
group by 関数で表示するカラム...
-
閉包テーブルのノードの移行に...
-
Unionした最後にGROUP BYを追加...
-
sqlで、600行あるテーブルを100...
-
カンマ区切りの文字列を検索する
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
SQL Left Join で重複を排除す...
-
SQLサーバから、項目の属性(型...
-
副問合せの書き方について
-
VIEWの元のテーブルのindexって...
-
エクセルの関数について教えて...
-
SQLにて特定の文字を除いた検索...
-
マイクラPC版のコマンドで効率...
-
select文のwhere句に配列を入れ...
-
sqlで、600行あるテーブルを100...
-
ある条件の最大値+1を初番する...
-
inner joinをすると数がおかし...
-
Unionした最後にGROUP BYを追加...
-
期間の重複を調べるSQL文につい...
-
クエリ表示と、ADOで抽出したレ...
-
Access パラメータクエリをcsv...
-
PL/SQLの変数について
-
MySQLのDATE型カラム値がNULLの...
-
php+mysqlで複数選択削除について
-
上位3位を求めるSQL文は?
おすすめ情報