第9回オフラインリアルタイムどう書くの問題をSQLで解く

問題はこちら。
http://nabetani.sakura.ne.jp/hena/ord9busfare/
テストに使用したDBはMySQLです。

まず問題を入れるテーブル作成。
問題番号のカラムは省略。一問毎に作りなおす。

DROP TABLE IF EXISTS charge;
CREATE TABLE charge(charge INT,expect INT);
DROP TABLE IF EXISTS PASSENGERS;
CREATE TABLE passengers(age CHAR(1),type CHAR(1));

問題登録。

INSERT INTO charge VALUES(210,170);
INSERT INTO passengers VALUES('C','n');
INSERT INTO passengers VALUES('I','n');
INSERT INTO passengers VALUES('I','w');
INSERT INTO passengers VALUES('A','p');
INSERT INTO passengers VALUES('I','w');

そして解く。
OFFSET指定でサブクエリが使えたらテンポラリテーブルを作らなくてすんだのに・・。

CREATE TEMPORARY TABLE wk(id INT PRIMARY KEY AUTO_INCREMENT,charge INT)
  SELECT * FROM(
	SELECT TRUNCATE(charge/20+0.9,0)*10 AS charge FROM passengers JOIN charge WHERE age='I' AND type='n'
  	UNION ALL
  	SELECT TRUNCATE((TRUNCATE(charge/20+0.9,0)*10)/20+0.9,0)*10 AS charge FROM passengers JOIN charge WHERE age='I' AND type='w'
  ) AS wk ORDER BY charge DESC;

SELECT SUM(charge) AS charge, SUM(charge) = (SELECT expect FROM charge) AS result FROM(
  SELECT SUM(charge) AS charge FROM passengers JOIN charge WHERE age='A' AND type='n'
  UNION ALL
  SELECT SUM(TRUNCATE(charge/20+0.9,0)*10) AS charge FROM passengers JOIN charge WHERE age='A' AND type='w'
  UNION ALL
  SELECT SUM(TRUNCATE(charge/20+0.9,0)*10) AS charge FROM passengers JOIN charge WHERE age='C' AND type='n'
  UNION ALL
  SELECT SUM(TRUNCATE((TRUNCATE(charge/20+0.9,0)*10)/20+0.9,0)*10) AS charge FROM passengers JOIN charge WHERE age='C' AND type='w'
  UNION ALL
  SELECT SUM(charge) AS charge FROM wk WHERE id > (SELECT COUNT(*)*2 FROM passengers WHERE age='A')
) AS total;