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

解決済

質問者：litton101
質問日時：2013/10/17 16:09
回答数：4件

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番

よろしくお願いします。

通報する

この質問への回答は締め切られました。

質問の本文を隠す

回答 (4件)

ベストアンサー優先
最新から表示
回答順に表示

No.4ベストアンサー

回答者： yambejp
回答日時：2013/10/17 22:00

うーん、いまいち意図がよくわかりませんが

こういうことでしょうか？

//元データ
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

- 0
- 件

通報する

この回答へのお礼

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

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

月刊誌の過去記事の目録を構築していてプルダウンメニューに
発行年月と号数、特集スポーツ名を列記したい目的だったのですが、
月によって特集テーマが２つ、３つある号があり、これらも
プルダウンに連記するのに「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

No.3

回答者： yambejp
回答日時：2013/10/17 18:49

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

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

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

- 0
- 件

通報する

この回答へのお礼

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

No.2

回答者： yambejp
回答日時：2013/10/17 17:14

//元データ

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

- 0
- 件

通報する

この回答へのお礼

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

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

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

通報する

お礼日時：2013/10/17 18:36

No.1

回答者： kosukejlampnet
回答日時：2013/10/17 16:41

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

- 0
- 件

通報する

この回答へのお礼

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

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

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

通報する

お礼日時：2013/10/17 17:04

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