Hatena::ブログ(Diary)

f8g

 | 

2006/05/19

| 19:56

http://fairydoll.net/

ここに昔あったデータをMySQLに入れる。

mysql> SELECT stallion FROM blood LIMIT 5;
+-----------------+
| stallion        |
+-----------------+
| A FLEETS DANCER |
| A P VALENTINE   |
| A. P. DELTA     |
| A. P. JET       |
| A. P. MILLION   |
+-----------------+
5 rows in set (0.01 sec)

DANZIG産句を調べる。

mysql> SELECT COUNT(stallion) FROM blood WHERE sire='DANZIG';
+-----------------+
| COUNT(stallion) |
+-----------------+
|              46 |
+-----------------+
1 row in set (0.00 sec)

うえ、46頭もいんのかよ、ってことでMR. PROSPECTORで調べる。

mysql> SELECT COUNT(stallion) FROM blood WHERE sire='MR. PROSPECTOR';
+-----------------+
| COUNT(stallion) |
+-----------------+
|              80 |
+-----------------+
1 row in set (0.01 sec)

増えてます。

mysql> SELECT COUNT(stallion) FROM blood WHERE sire='NORTHERN DANCER';
+-----------------+
| COUNT(stallion) |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.00 sec)

3頭になった。

mysql> SELECT stallion FROM blood WHERE sire='NORTHERN DANCER';
+----------------+
| stallion       |
+----------------+
| DANZIG         |
| DIXIELAND BAND |
| OBLIGATO       |
+----------------+
3 rows in set (0.01 sec)
mysql> SELECT COUNT(stallion) FROM blood WHERE sire='DIXIELAND BAND';
+-----------------+
| COUNT(stallion) |
+-----------------+
|              15 |
+-----------------+
1 row in set (0.00 sec)
mysql> SELECT stallion as s,
    -> (SELECT COUNT(stallion) FROM blood WHERE sire = s) AS count
    -> FROM blood
    -> WHERE (SELECT COUNT(stallion) FROM blood WHERE sire = s) > 1
    -> ORDER BY count DESC;
+-------------------+-------+
| s                 | count |
+-------------------+-------+
| STORM CAT         |   102 |
| DANZIG            |    46 |
| DEPUTY MINISTER   |    38 |
| GONE WEST         |    32 |
| A.P. INDY         |    29 |
| SEEKING THE GOLD  |    17 |
| WILD AGAIN        |    16 |
| DIXIELAND BAND    |    15 |
| CARSON CITY       |    14 |
| MT. LIVERMORE     |    13 |
| CRAFTY PROSPECTOR |    11 |
| PHONE TRICK       |    10 |
| RUNAWAY GROOM     |     9 |
| MEADOWLAKE        |     9 |
| KINGMAMBO         |     9 |
| DEVIL'S BAG       |     9 |
| WOODMAN           |     8 |
| GULCH             |     8 |
| BROAD BRUSH       |     8 |
| IN EXCESS         |     7 |
| CRYPTOCLEARANCE   |     7 |
| UNBRIDLED'S SONG  |     7 |
| TWO PUNCH         |     7 |
| HENNESSY          |     6 |
| GILDED TIME       |     6 |
| MONTBROOK         |     5 |
| SLEW CITY SLEW    |     5 |
| COZZENE           |     5 |
| PINE BLUFF        |     4 |
| MARQUETRY         |     4 |
| SILVER GHOST      |     4 |
| EASTERN ECHO      |     4 |
| CHEROKEE RUN      |     4 |
| HOLY BULL         |     4 |
| SKY CLASSIC       |     3 |
| SALT LAKE         |     3 |
| SKIP TRIAL        |     3 |
| RAHY              |     3 |
| PULPIT            |     3 |
| CITIDANCER        |     3 |
| SILVER DEPUTY     |     3 |
| ALYDEED           |     3 |
| THUNDER GULCH     |     3 |
| HOUSEBUSTER       |     3 |
| BERTRANDO         |     3 |
| FIT TO FIGHT      |     3 |
| MR. GREELEY       |     3 |
| BLACK TIE AFFAIR  |     2 |
| CEE'S TIZZY       |     2 |
| SMOKESTER         |     2 |
| STORM BOOT        |     2 |
| TOUR D'OR         |     2 |
| TAKE ME OUT       |     2 |
| THEATRICAL        |     2 |
| DAYJUR            |     2 |
| DISTANT VIEW      |     2 |
| LORD CARSON       |     2 |
| HONOUR AND GLORY  |     2 |
| GO FOR GIN        |     2 |
| PEAKS AND VALLEYS |     2 |
| FAST PLAY         |     2 |
| POLISH NAVY       |     2 |
| PRIVATE TERMS     |     2 |
| PROPER REALITY    |     2 |
| QUIET AMERICAN    |     2 |
| REGAL CLASSIC     |     2 |
| ROBYN DANCER      |     2 |
| DYNAFORMER        |     2 |
| IS IT TRUE        |     2 |
+-------------------+-------+
69 rows in set (3.56 sec)

始めっから調べとけ!

トラックバック - http://d.hatena.ne.jp/arikui/20060519/1148036210
 |