當然我們查詢資料時,有時候需要作排版,或是參照時,
需要有補助表,會讓結果更具可讀性.
例如yafuu168兄在 http://ithelp.ithome.com.tw/question/10127060提到的九九乘法表,就是一個絕妙的例子.
yafuu168兄是以Oracle為例,我們來看看MySQL可以怎樣做出類似的效果.

SELECT a.anum , '*', b.bnum, '=', a.anum * b.bnum
  FROM (SELECT @num1 := @num1 +1 AS 'anum'
          FROM mysql.help_topic
             , (SELECT @num1 := 0) x
         LIMIT 9) a
     , (SELECT @num2 := @num2 +1 AS 'bnum'
          FROM mysql.help_topic
             , (SELECT @num2 := 0) y
         LIMIT 9) b;

結果的上面部份省略
|    8 | * |    9 | = |              72 |
|    9 | * |    9 | = |              81 |
+------+---+------+---+-----------------+
81 rows in set (0.00 sec)

接著再來看看其他例子.
以產品銷售來當範例,實務上會有許多資料,在此先作簡化.

CREATE TABLE ithelp1013a(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
product_name CHAR(10) NOT NULL,
product_price INT UNSIGNED NOT NULL,
qty TINYINT UNSIGNED NOT NULL,
sales_year INT UNSIGNED NOT NULL
);

INSERT INTO ithelp1013a(product_name, product_price, qty, sales_year) VALUES
('AV', 20, 30, 2010),
('AV', 22, 40, 2011),
('AV', 25, 45, 2012),
('AV', 30, 50, 2013),
('BOOK', 120, 6, 2010),
('BOOK', 150, 8, 2011),
('BOOK', 300, 10, 2012),
('BOOK', 340, 12, 2013);

接著建立補助表

CREATE TABLE ithelp1013b(
year INT UNSIGNED NOT NULL PRIMARY KEY,
year1 TINYINT UNSIGNED NOT NULL,
year2 TINYINT UNSIGNED NOT NULL,
year3 TINYINT UNSIGNED NOT NULL,
year4 TINYINT UNSIGNED NOT NULL,
rowtotal TINYINT UNSIGNED NOT NULL
);

INSERT INTO ithelp1013b(year, year1, year2, year3, year4, rowtotal) VALUES
(2010, 1, 0, 0, 0, 1),
(2011, 0, 1, 0, 0, 1),
(2012, 0, 0, 1, 0, 1),
(2013, 0, 0, 0, 1, 1);

可以利用以下指令

SELECT a.product_name
     , SUM(a.qty * a.product_price * b.year1) AS 'Y2010'
     , SUM(a.qty * a.product_price * b.year2) AS 'Y2011'
     , SUM(a.qty * a.product_price * b.year3) AS 'Y2012'
     , SUM(a.qty * a.product_price * b.year4) AS 'Y2013'
     , SUM(a.qty * a.product_price * b.rowtotal) AS 'Total'
  FROM ithelp1013a a
     , ithelp1013b b
 WHERE a.sales_year = b.year
 GROUP BY a.product_name;

+--------------+-------+-------+-------+-------+-------+
| product_name | Y2010 | Y2011 | Y2012 | Y2013 | Total |
+--------------+-------+-------+-------+-------+-------+
| AV           |   600 |   880 |  1125 |  1500 |  4105 |
| BOOK         |   720 |  1200 |  3000 |  4080 |  9000 |
+--------------+-------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

產生出可讀性較高的結果.