こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

グループ内最大値に印を付けるSQL文

MySQL5 に年度、通し番号が 格納されており、
select year, number from mytable
・・・と抽出すると以下のようになります。

2005 1
2005 2
2005 3
2008 4
2008 5
2008 6
2008 7
2008 8
2010 9
2011 10
2011 11
2011 112

同じ年度グループ内で、最大となる通し番号の行に「myFlg =1」と付けるSQL文をご教示ください。if とか group by とか as myFlg =1 みたいな記述をするのかなと想像するんですが・・・

PHP5のWHILE文でmyFlg = 1となっている行だけを出力し、上記データ例では、

while ($col = mysql_fetch_array($rst)) {
if ($col['myFlg']) == '1') {
echo $col['year'] . "年の最大番号は、" . $col['myFlg']. "番";
}
}

を実行することで、以下のような結果を得ることを期待しています。

2005年の最大番号は、3番
2008年の最大番号は、8番
2010年の最大番号は、9番
2011年の最大番号は、112番

よろしくお願いします。

投稿日時 - 2013-10-17 16:09:37

QNo.8309533

暇なときに回答ください

質問者が選んだベストアンサー

うーん、いまいち意図がよくわかりませんが
こういうことでしょうか?

//元データ
create table mytable(year int not null,number int not null);
insert into mytable(year,number) values(2005,1),(2005,2),(2005,3),(2008,4),(2008,5),(2008,6),(2008,7),(2008,8),(2010,9),(2011,10),(2011,11),(2011,112);

//検索
select year,number,(select if(max(number)=t1.number,1,null) from mytable where year=t1.year) as myflg
from mytable as t1

投稿日時 - 2013-10-17 22:00:07

お礼

yamabejpさん、おかげさまでご提示のSQL文にて解決できました。
何度も、本当にありがとうございました。

当方でやりたいことがお伝えきれなかったので、手元のテストコードを記しておきます。
もし冗長な部分があればご指摘ください。改善したいと思います。


月刊誌の過去記事の目録を構築していてプルダウンメニューに
発行年月と号数、特集スポーツ名を列記したい目的だったのですが、
月によって特集テーマが2つ、3つある号があり、これらも
プルダウンに連記するのに「myflg = 1」を活用したかったものです。


//元データ
CREATETABLE`mymagazine`(`myCode`int(5)unsignedNOTNULLauto_incrementCOMMENT'管理コード',`myPubYear`int(4)defaultNULLCOMMENT'発行年',`myPubMonth`int(2)defaultNULLCOMMENT'発行月',`mySequence`int(3)defaultNULLCOMMENT'号',`mySortNo`int(2)defaultNULLCOMMENT'巻毎のソート番号',`myCategoryCode`int(2)defaultNULLCOMMENT'分類コード',`myTheme`varchar(900)defaultNULLCOMMENT'テーマ',PRIMARYKEY(`myCode`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='テーブル'AUTO_INCREMENT=1;
INSERTINTO`mymagazine`(`myPubYear`,`myPubMonth`,`mySequence`,`mySortNo`,`myCategoryCode`,`myTheme`)VALUES(2005,7,138,1,99,''),(2005,7,138,2,1,'サッカー'),(2005,7,138,3,1,'サッカー'),(2005,7,138,14,16,''),(2005,7,138,15,15,''),(2005,8,139,4,1,'野球'),(2005,8,139,5,1,'野球'),(2005,8,139,17,17,''),(2005,8,139,18,17,''),(2005,9,140,1,99,''),(2005,9,140,2,1,'テニス'),(2005,9,140,3,1,'バスケ'),(2005,9,140,4,1,'バスケ'),(2005,9,140,5,1,'バスケ'),(2005,9,140,6,1,'水泳'),(2005,9,140,7,2,''),(2005,9,140,8,14,''),(2005,9,140,9,13,''),(2005,11,142,2,1,'柔道'),(2005,11,142,3,1,'剣道'),(2005,11,142,4,1,'剣道'),(2005,11,142,5,8,''),(2005,11,142,6,2,''),(2006,1,144,2,1,'卓球'),(2006,1,144,3,1,'卓球'),(2006,1,144,4,1,'バレー'),(2007,9,236,2,1,'陸上'),(2007,9,236,3,1,'陸上'),(2007,9,236,4,1,'陸上'),(2007,9,236,5,4,'ソフト'),(2007,9,236,6,5,'')

//コード
<?php
$con = mysql_connect(serv, user, pw);
$selectdb = mysql_select_db(DBNAME, $con);

$sql = "select *,";
$sql .= " (select if(max(mySortNo)=t1.mySortNo,1,null) from mymagazine where mySequence=t1.mySequence and myCategoryCode =1) as myflg"; // フラグ
$sql .= " from mymagazine as t1";
$sql .= " order by mySequence desc";

$rst = mysql_query($sql, $con);

$body = "<select name=\"myselect\"\">\n";

while ($col = mysql_fetch_array($rst)) {

// 発行年を見出しにセット
if ( $sv_myPubYear != $col["myPubYear"] ) {
if ( $sv_myPubYear != '' ) {
$body .= "</optgroup>\n";
}
$body .= "<optgroup label=\"" . $col["myPubYear"] . "年\">\n";
}

//号数(mySequence)と分類コード(myCategoryCode=特集、連載、投稿など各コーナー名で1だけを抽出する)
//が変わらないのに特集テーマが変わったらテーマの文言を追記する
if (($col["mySequence"] == $sv_mySequence) && ($col["myCategoryCode"] == $sv_myCategoryCode) && ( $col["myTheme"] != $sv_myTheme)) {
$selectStr = $selectStr . "◆" . $col["myTheme"];
} else {
$selectStr = $col["myTheme"];
}

//同じ号数で、直前のループと比較して文字数が減っていたら、前の方を採用
if (($col["mySequence"] == $sv_mySequence) && (mb_strlen($selectStr,"utf-8") < mb_strlen($sv_selectStr,"utf-8"))) {
$selectStr = $sv_selectStr;
}

//SQLで設置したフラグがたっている場合だけ、プルダウンメニューとして書き出す。
if ($col["myflg"] == '1') {
$body .= "<option>" . $col["myPubMonth"] . "月号(第" . $col["mySequence"] . "号) " . $selectStr . "</option>\n";
}

//次のループで比較検討用に各値の持ち越し
$sv_myPubYear = $col["myPubYear"];
$sv_mySequence = $col["mySequence"];
$sv_myCategoryCode = $col["myCategoryCode"];
$sv_myTheme = $col["myTheme"];
$sv_selectStr = $selectStr;

}
$body .= "</optgroup>\n";
$body .= "</select>\n";

?>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
</head>
<body>
<?= $body ?>
</body>
</html>


なお先日、別質問でMySQL関数の非推奨を教わり、こちらには後日に取り組むつもりです
http://oshiete.goo.ne.jp/qa/8277847.html

投稿日時 - 2013-10-18 00:57:26

ANo.4

このQ&Aは役に立ちましたか?

8人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.3

>SELECT文だけで実現できないか

ああ、なるほど・・・
そうなるともっと簡単な話で、たぶん考えすぎです

select year,max(number) as number,1 as myFlg from mytable group by year

投稿日時 - 2013-10-17 18:49:14

お礼

yamabejpさん、速攻のご回答ありがとうございます。

max( number )を出してGROUP BY yearでくくる、なるほど、確かに考えすぎだったかもしれません。

#2のご回答でお示しいただいたcreate文のテーブルに対して
実行してみましたが、こういう照会結果になります。

year number myFlg
2005  3  1
2008  8  1
2010  9  1
2011  112  1

これを、myFlgなるカラムは実存しないのですがSELECT時だけの一時的な存在として実存させられないかという希望なのですが、そもそも論として不可能でしょうか。

year number myFlg
2005 1 NULL(←NULLでなくてもいいですが)
2005 2 NULL
2005 3 1
2008 4 NULL
2008 5 NULL
2008 6 NULL
2008 7 NULL
2008 8 1
2010 9 1
2011 10 NULL
2011 11 NULL
2011 112 1

投稿日時 - 2013-10-17 19:11:39

ANo.2

//元データ
create table mytable(year int not null,number int not null,myFlg tinyint null);
insert into mytable(year,number) values(2005,1),(2005,2),(2005,3),(2008,4),(2008,5),(2008,6),(2008,7),(2008,8),(2010,9),(2011,10),(2011,11),(2011,112);

//更新
update mytable as t1
inner join (select year,max(number) as number from mytable group by year) as t2
on t1.year=t2.year and t1.number=t2.number
set t1.myFlg=1

投稿日時 - 2013-10-17 17:14:35

お礼

yamabejpさん、大変ご無沙汰しておりますが、
相変わらずお世話になり本当にありがとうございます。

私がよく理解できていないinner joinまでをも動員する高度な問題とは思いませんでした。

それで、元質問が非常~~~に悪く申し訳なく思います。
元データを入れて実際に実行してみましたが、myFlgというtinyintなカラムを設置せず、SELECT文だけで実現できないかと思っていたのですが、この場合、仮想テーブルとか、もっと難しい話になってしまうでしょうか。
(同時並行的に、ご提示のSQL文を分解しながら本番環境に組み込めないか試行錯誤してみます。)

投稿日時 - 2013-10-17 18:36:37

同じ年度で最大値を持つレコードが複数あった場合はどうしますか?

投稿日時 - 2013-10-17 16:41:36

お礼

kosukejlampnetさん、早速のコメントありがとうございました、

>同じ年度で最大値を持つレコードが複数あった場合

基本的に、通し番号(number)は、auto_incrementでユニーク&自動採番なので重複レコードは存在しませんが、より後ろに格納されているデータと解釈お願いできればと存じます。よろしくお願い致します。

投稿日時 - 2013-10-17 17:04:41

あなたにオススメの質問