【無料配信♪】Renta !全タテコミ作品第1話

いつもお世話になっております。
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を抽出する方法をご教授頂けると大変助かります。
宜しくお願い致します。

このQ&Aに関連する最新のQ&A

A 回答 (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
;
    • good
    • 0
この回答へのお礼

ありがとうございます!
いろんな副問合せの形があるんですね。
SQLは奥が深いですね。
本当に勉強になります!ありがとうございました!

お礼日時:2007/06/01 22:05

#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
;
    • good
    • 0

#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
;
    • good
    • 0

ひょっとして。


「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;
    • good
    • 0
この回答へのお礼

ありがとうございます!
副問い合わせという事ができるんですね。
今まで使った事がありませんでした。これは便利ですね!
副問合せを使用すれば、やりたいことが全て解決しました!
本当に助かりました!

お礼日時:2007/06/01 22:04

そもそも「好きな」値を表示(抽出)させたいという要望を実現するのは、「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のレコードを抽出したいという事なのです。

補足日時:2007/05/30 17:07
    • good
    • 0

標準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の拡張仕様を利用することができます。そうでないなら、この拡張仕様を利用すべきではありません。
    • good
    • 0

そもそもすきなidを選ぶ時点でgroup byするのは整合性がないかと。



select `id`,`e_mail`,`date`
from `table_name`
where `e_mail`='t1@t.jp'
and `id`='1'

で十分では?
    • good
    • 0

私は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
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

関連するカテゴリからQ&Aを探す

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qgroup byで指定したカラム以外のカラムの値を取得したい

t_meisaiは以下の定義で調味料の使用履歴を記録しています。
idint4uniqueな値
date timestampyy/mm/dd
type int41 or 2 or 3
orderint4純粋な順番

typeには塩、砂糖のように固形のものは1、酢、醤油のように液状のものは2、それ以外は3が入ります。
orderはdate毎に使った順番が記録されていきます。

date || type のgroupごとの一番最初に使ったものだけ抽出したのが下のSQLです。
select date || type as key, min(order) from t_meisai group by key

上記の条件に当てはまるレコードのidを抽出したいのですがどうしたらよいのでしょうか?

Aベストアンサー

>クエリを実行したまま処理が戻りません。

というか、遅いんでしょう(^^;;;


>下のものはgroup by keyのkeyがないのでできませんでした。

うーん、「できませんでした」って書くより、group by句を直したほうが早いですよね?

select id, minorder from t_meisai t1,
(select date, type, min(order) as minorder from t_meisai group by date, type) t2
where t1.date = t2.date and t1.type = t2.type

QMAX値を条件にデータを取得するには?

SQL文で困っています。
ご教授下さい。


下記のようなデータがあった場合、それぞれの区分毎に
年月が最大(最新)のデータを取得したいです。
(実際には1レコードにその他項目があり、それらも取得します。)
<検索対象データ>
区分 年月   金額
-----------------------------
A   200412  600
A   200503  560
B   200311  600
B   200508  1000
B   200504  560
C   200508  400
C   200301  1100


<取得したいデータ>

区分 年月   金額
-----------------------------
A   200503  560
B   200508  1000
C   200508  400

よろしくお願いします。

Aベストアンサー

テーブル名をXXXとすると次のようなSQLでよいと思います。(最善の方法かどうかは自信がないですが)

select B.* from (select 区分, max(年月) as 年月 from XXX group by 区分) As A
inner join XXX as B on A.区分 = B.区分 and A.年月 = B.年月
order by B.区分

QSQLで優先順位が高いレコードを抽出する方法

こんにちは。

あるテーブル"T_A"が有り、
そのテーブルにカラム"C1"、"C2"が有ります。

テーブル"T_A"にレコードが以下の用に登録されていると仮定します。

C1|C2
-----
AA|S
AA|P
BB|R
BB|S
CC|S

※C2に登録されている値は抽出する優先順位が有り、
R:1
P:2
S:3
Rが一番優先順位が高く、Sが優先順位が最も低いです。

この条件から、C1の値が同じレコードに対して、C2の優先順位が
最も高いレコードを抽出したいです。

欲しい結果↓
C1|C2
-----
AA|P
BB|R
CC|S

Aベストアンサー

SELECT
C1,
(
CASE MIN(
CASE C2
WHEN 'R' THEN 1
WHEN 'P' THEN 2
ELSE 3
END
)
WHEN 1 THEN 'R'
WHEN 2 THEN 'P'
ELSE 'S'
END
) AS C2
FROM
Table1
GROUP BY
C1
ORDER BY
C1

QSELECTで1件のみ取得するには?

こんにちわ。
いまORACLE9iを使用している者です。

ACCESSでは
SELECT TOP 1 項目名 FROM テーブル名
ORDER BY 項目名;
で並べ替えたデータ群のうち,先頭の1件だけを
取ることができますが,
ORACLEでそのような機能(SQL)はあるでしょうか?
教えてください。
よろしくお願いします。

Aベストアンサー

order by と rownum を併用する場合は注意が必要です。

[tbl01]
cola | colb
------------
1000 | aaaa
1001 | bbbb

というデータがある場合、
select cola from tbl01 where rownum < 1 order by cola desc;
とすると、「1001」ではなく、「1000」が返されます。
これは、order by の前に rownum < 1 が適用されてしまうからです。

解決するには、
select aaa from (select cola aaa from tbl01 order by cola desc) where rownum = 1;
とすれば良いです。

QDataTableから条件を満たした行を別のDatatableへコピーしたい

VC#2005とSQLServer2005ExpressEditionでWindowsアプリケーションを作成しています。

データベースの中から1つのマスタテーブルのデータを呼び出すのにTableAdapterを使ってDataTableにデータをバインドしました。
そこから条件を満たしている行をすべて抽出して同じ型のDataTableにデータをコピーしたいのです。

ですから、DataTableは2つ用意しています。1つは上記の通りデータをバインドしていますが、もう1つは宣言しただけなのでまだ空っぽの状態です。
DataTableにはカラムが3列あり、その中の1列をグループIDとしています。
条件としてはグループIDが同じであるということです。
やりたいことは条件を満たしている行をすべて抽出して空のデータテーブルにコピーすることです。

どなたかご存知の方いらっしゃれば教えてください。
よろしくお願いします。

Aベストアンサー

C#だったんですね … さほど変わりないと思いますが

お使いのコードが提示されていないのでこちらで適当な変数をでっち上げております
現在お使いのコードを支障の無い範囲で提示しましょう

//元のデータテーブルがdtSourceとすると
// テーブル構造をコピー
DataTable dt = dtSource.Clone();
DataRow r = null;
foreach( DataRow dtRow in dtSource.Select("選択するための文字列"))
{
  r = dt.NewRow();
  for( int n = 0; n < dtRow.ItemArray.Length; n++ )
  {
    r[n] = dtRow[n];
  }
  dt.Rows.Add( r );
}
といった具合になると思います

# 前回の投稿中のstSorceはdtSourceの単なるミスです

Q[SQL]重複内容を持つデータから1件抽出かつ複数のカラムを表示したい

[SQL]重複内容を持つデータから1件抽出かつ複数のカラムを表示したい

初めてお世話になります。

データベース種別はH2です。
参考:http://www.h2database.com/html/main.html

下記のようなテーブルAがあるとします。

  ID  |  TAG  |
―――――+―――――|
  1   |  abc   |
  1   |  def   |
  1   |  ghi   |
  2   |  abc   |
  2   |  ghi   |
  3   |  abc   |
  3   |  def   |
  3   |  ghi   |
  4   |  abc   |


上記テーブルAから下記例のような結果を得られるSQLを知りたいのです。

  ID  |  TAG  |
―――――+―――――|
  1   |  abc   |
  2   |  abc   |
  3   |  abc   |
  4   |  abc   |

上記結果でなくとも、


  ID  |  TAG  |
―――――+―――――|
  1   |  def   |
  2   |  ghi   |
  3   |  def   |
  4   |  abc   |

であっても構いません。(TAG列のデータはどんな内容でもよいです。)

実現させたいのは、
(1)ID列が重複しない結果を表示させたい、
かつ
(2)TAG列も表示させたい
ということです。

これでイケるだろ!と思ってあえなく失敗したSQLは下記です;
select distinct(ID), TAG from A


宜しくお願い致します。

[SQL]重複内容を持つデータから1件抽出かつ複数のカラムを表示したい

初めてお世話になります。

データベース種別はH2です。
参考:http://www.h2database.com/html/main.html

下記のようなテーブルAがあるとします。

  ID  |  TAG  |
―――――+―――――|
  1   |  abc   |
  1   |  def   |
  1   |  ghi   |
  2   |  abc   |
  2   |  ghi   |
  3   |  abc   |
  3   |  def   |
  3   |  ghi   |
  4   |  abc   |


上記テーブルAか...続きを読む

Aベストアンサー

H2を知らないケド。Oracleだったら。

select ID, MIN(TAG) from A
GROUP BY ID
ORDER BY ID

ではどうでしょう?


select ID, MAX(TAG) from A
GROUP BY ID
ORDER BY ID
もお試しください。

QSQLで部分的にGROUP BYしたいとき

はじめまして。
SQLで部分的にGROUP BYで集計したいのですが、
どうもしっくりくるSQLがかけません。
多分CASEあたりを使うと綺麗で高速なSQLがかけると思っています。
皆様のお知恵をお借りしたく投稿しました。

具体的には以下のような出納帳データで

出納帳
日付     金額
2012/8/20 1000
2012/8/20 2000
2012/8/21 -1000
2012/8/21 -2000
2012/8/22 3000
2012/8/23 4000
2012/8/24 -3000
2012/8/24 -4000
2012/8/27 5000

↓集計

日付     金額
2012/8/20 1000
2012/8/20 2000
2012/8/21 -3000(-1000と-2000を集約)
2012/8/22 3000
2012/8/23 4000
2012/8/24 -7000(-3000と-4000を集約)
2012/8/27 5000

というように、マイナスの金額は集約してしまいたいのですが、どのようなSQLが最適でしょうか?
とりあえず、UNIONかなと思い、

(SELECT 日付, 金額
FROM 出納帳
WHERE 金額>=0
UNION ALL
SELECT 日付, 金額
FROM 出納帳
WHERE 金額<0
GROUP BY 日付 )
ORDER BY 日付

とプラス金額とマイナス金額にわけUNIONしたのですが、
もっと綺麗(高速)にやる方法があるのではないか?と思い投稿いたしました。
なにかヒントございましたら、ご教授ください。

DB環境:Oracle 11g

はじめまして。
SQLで部分的にGROUP BYで集計したいのですが、
どうもしっくりくるSQLがかけません。
多分CASEあたりを使うと綺麗で高速なSQLがかけると思っています。
皆様のお知恵をお借りしたく投稿しました。

具体的には以下のような出納帳データで

出納帳
日付     金額
2012/8/20 1000
2012/8/20 2000
2012/8/21 -1000
2012/8/21 -2000
2012/8/22 3000
2012/8/23 4000
2012/8/24 -3000
2012/8/24 -4000
2012/8/27 5000

↓集計

日付     金額
2012/8/20 1000
2012/8/20 2000
2012/8/...続きを読む

Aベストアンサー

--綺麗かどうかはともかく、かなり無理やり
SELECT 日付, SUM(金額) AS 金額
FROM (
SELECT 日付, 金額,
ROW_NUMBER() OVER (PARTITION BY 日付 ORDER BY 金額) AS id
FROM 出納帳
)
GROUP BY 日付, CASE WHEN 金額 < 0 THEN 0 ELSE id END;


人気Q&Aランキング