久しぶりにSQLを。

たまたま作成した某簡易アンケートシステム。
アンケートのデータの保存を、次のようにした。

CREATE TABLE IF NOT EXISTS `unq_data` (
  `unq_data_id` int(11) NOT NULL auto_increment,
  `sex` tinyint(1) NOT NULL default '0',
  `age` tinyint(1) NOT NULL default '0',
  `question1` tinyint(1) NOT NULL default '0',
  `question2` tinyint(1) NOT NULL default '0',
  `question3` tinyint(1) NOT NULL default '0',
  `question4` tinyint(1) NOT NULL default '0',
  `created_at` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `user_ip` varchar(16) NOT NULL default '0',
  PRIMARY KEY  (`unq_data_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

一人の回答を1レコードで保存するというものです。
まあ、設問を増やしたりとかそういうのでは対応できない形ですね。
で、この場合の集計画面の作成。
sexは3パターン。
ageは6パターン。
question1は2パターン
question2は6パターン
question3は5パターン
question4は10パターン。ある。
これを、集計するには。。。。。。。

安易に考えると、上記の３１パターンのものを動かしてたせばいい。しかし！そんなことはできないので。

$sql = "SELECT "
                . "SUM(CASE WHEN sex = 1 THEN 1 ELSE 0 END) as m, "
                . "SUM(CASE WHEN sex = 2 THEN 1 ELSE 0 END) as w, "
                . "SUM(CASE WHEN sex = 0 THEN 1 ELSE 0 END) as n, "
                . "SUM(CASE WHEN age = 1 THEN 1 ELSE 0 END) as age1, "
                . "SUM(CASE WHEN age = 2 THEN 1 ELSE 0 END) as age2, "
                . "SUM(CASE WHEN age = 3 THEN 1 ELSE 0 END) as age3, "
                . "SUM(CASE WHEN age = 4 THEN 1 ELSE 0 END) as age4, "
                . "SUM(CASE WHEN age = 5 THEN 1 ELSE 0 END) as age5, "
                . "SUM(CASE WHEN age = 6 THEN 1 ELSE 0 END) as age6, "
                . "SUM(CASE WHEN question1 = 1 THEN 1 ELSE 0 END) as q1_1, "
                . "SUM(CASE WHEN question1 = 0 THEN 1 ELSE 0 END) as q1_2, "
                . "SUM(CASE WHEN question2 = 0 THEN 1 ELSE 0 END) as q2_0, "
                . "SUM(CASE WHEN question2 = 1 THEN 1 ELSE 0 END) as q2_1, "
                . "SUM(CASE WHEN question2 = 2 THEN 1 ELSE 0 END) as q2_2, "
                . "SUM(CASE WHEN question2 = 3 THEN 1 ELSE 0 END) as q2_3, "
                . "SUM(CASE WHEN question2 = 4 THEN 1 ELSE 0 END) as q2_4, "
                . "SUM(CASE WHEN question2 = 5 THEN 1 ELSE 0 END) as q2_5, "
                . "SUM(CASE WHEN question3 = 0 THEN 1 ELSE 0 END) as q3_0, "
                . "SUM(CASE WHEN question3 = 1 THEN 1 ELSE 0 END) as q3_1, "
                . "SUM(CASE WHEN question3 = 2 THEN 1 ELSE 0 END) as q3_2, "
                . "SUM(CASE WHEN question3 = 3 THEN 1 ELSE 0 END) as q3_3, "
                . "SUM(CASE WHEN question3 = 4 THEN 1 ELSE 0 END) as q3_4, "
                . "SUM(CASE WHEN question4 = 0 THEN 1 ELSE 0 END) as q4_0, "
                . "SUM(CASE WHEN question4 = 1 THEN 1 ELSE 0 END) as q4_1, "
                . "SUM(CASE WHEN question4 = 2 THEN 1 ELSE 0 END) as q4_2, "
                . "SUM(CASE WHEN question4 = 3 THEN 1 ELSE 0 END) as q4_3, "
                . "SUM(CASE WHEN question4 = 4 THEN 1 ELSE 0 END) as q4_4, "
                . "SUM(CASE WHEN question4 = 5 THEN 1 ELSE 0 END) as q4_5, "
                . "SUM(CASE WHEN question4 = 6 THEN 1 ELSE 0 END) as q4_6, "
                . "SUM(CASE WHEN question4 = 7 THEN 1 ELSE 0 END) as q4_7, "
                . "SUM(CASE WHEN question4 = 8 THEN 1 ELSE 0 END) as q4_8, "
                . "SUM(CASE WHEN question4 = 9 THEN 1 ELSE 0 END) as q4_9 "
             . "FROM "
                . "unq_data ";
        $query = $this->db->query($sql);

これで一件落着。
ベンチとったら、0.001秒だった。