[前][次][番号順一覧][スレッド一覧]

mysql:13054

From: "rishiri.info" <"rishiri.info" <mail@xxxxxxxxxx>>
Date: Fri, 2 Jun 2006 22:52:01 +0900
Subject: [mysql 13054] Re: 追加で男女別 rank フィールドを設けたい

rishiri.infoです。お騒がせしてすいません。
F.Y 様のヒント(ほぼ答えですね)
> set @male = 0, @female = 0;
> select if(性別 = '♂', @male := (@male + 1), @female:=(@female + 1)) as
> 性別順位
> from データ;

のお陰で

SELECT kiroku.scode As scode, sensyu.namae As namae, sei.seibetsu As
seibetsu,
min(if(kiroku.kcode = 4, kiroku.ktime, null)) As 4P,
min(if(kiroku.kcode = 9, kiroku.ktime, null)) As 9P,
min(if(kiroku.kcode = 13, kiroku.ktime, null)) As GP,
timediff(max(if(kiroku.kcode = 13, kiroku.ktime, null)),'06:00:00') As
tdiff,
@r1:=(@r1 + 1) As rank,
if(seibetsu = '男', @male := (@male + 1), @female:=(@female + 1)) As rank2
FROM kiroku,sensyu,kukan,sei
WHERE kiroku.scode = sensyu.scode
AND kukan.kcode = kiroku.kcode
AND sei.seicode = sensyu.seicode
GROUP BY scode, namae, seibetsu
ORDER BY tdiff DESC, rank2 DESC

男女別フィールド rank2 を追加して
男女別でも順位をつけることは可能になりました。
ありがとうございました!

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.8.1/354 - Release Date: 2006/06/01


[前][次][番号順一覧][スレッド一覧]

     13050 2006-06-02 12:45 ["rishiri.info" <mail] 追加で男女別rankフィールドを設けたい    
     13051 2006-06-02 14:36 ┗["F.Y" <fumi_sby@xxxx]                                       
->   13054 2006-06-02 22:52  ┗["rishiri.info" <mail]