iT邦幫忙

DAY 13
5

MySQL那些事兒系列 第 13

補助表的運用

  • 分享至 

  • xImage
  •  

當然我們查詢資料時,有時候需要作排版,或是參照時,
需要有補助表,會讓結果更具可讀性.
例如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)

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


上一篇
MySQL GROUP BY ROLLUP 的應用
下一篇
關於索引的一些探討(一)
系列文
MySQL那些事兒30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 則留言

0

我要留言

立即登入留言