wiflish
          Loving Life! Loving Coding!
          posts - 98,comments - 98,trackbacks - 0
          # Host: localhost??? Database: test
          # ------------------------------------------------------
          # Server version 5.0.45-community-nt-log

          #
          # Table structure for table sale
          #

          DROP TABLE IF EXISTS `sale`;
          CREATE TABLE `sale` (
          ? `id` int(10) unsigned NOT NULL auto_increment,
          ? `year` int(11) NOT NULL,
          ? `quarter` int(11) NOT NULL,
          ? `amount` decimal(15,2) NOT NULL,
          ? PRIMARY KEY? (`id`)
          ) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

          #
          # Dumping data for table sale
          #

          /*!40101 SET NAMES latin1 */;

          INSERT INTO `sale` VALUES (1,2004,1,2328);
          INSERT INTO `sale` VALUES (2,2004,2,3822);
          INSERT INTO `sale` VALUES (3,2004,3,7071);
          INSERT INTO `sale` VALUES (4,2004,4,8931);
          INSERT INTO `sale` VALUES (5,2005,1,2633);
          INSERT INTO `sale` VALUES (6,2005,2,3910);
          INSERT INTO `sale` VALUES (7,2005,3,237193);
          INSERT INTO `sale` VALUES (8,2005,4,567444);
          INSERT INTO `sale` VALUES (9,2006,1,12313);
          插入數據后結果為:
          id
          year
          quarteramount
          1
          2004
          1
          2328.00
          2
          2004
          23822.00
          3
          2004
          3
          7071.00
          4
          2004
          4
          8931.00
          5
          2005
          1
          2633.00
          6
          2005
          2
          3910.00
          7
          2005
          3
          237193.00
          8
          2005
          4
          567444.00
          9
          2006
          1
          12313.00


          交叉表查詢語句:
          select a.year, 1d, 2d, 3d, 4d from
          (select distinct year from sale) a left join
          (select year, amount 1d from sale where quarter=1 group by year) a1d on a.year = a1d.year
          left join (select year, amount 2d from sale where quarter=2 group by year) a2d on a2d.year=a.year
          left join (select year, amount 3d from sale where quarter=3 group by year) a3d on a3d.year=a.year
          left join (select year, amount 4d from sale where quarter=4 group by year) a4d on a4d.year=a.year
          該語句查詢某年的四個季度的amount,以行顯示,顯示結果:

          year
          1d
          2d
          3d4d
          2004
          2328.00
          3822.00
          7071.00
          8931.00
          2005
          2633.00
          3910.00
          237193.00
          567444.00
          2006
          12313.00
          NULL
          NULL
          NULL


          實現定長列的查詢(即quarter的最大取值為4,定長為4列).
          posted on 2007-10-09 22:07 想飛的魚 閱讀(1179) 評論(0)  編輯  收藏 所屬分類: database
          主站蜘蛛池模板: 安庆市| 安阳县| 馆陶县| 威海市| 突泉县| 土默特右旗| 宜州市| 谷城县| 天柱县| 定兴县| 青岛市| 湄潭县| 澄城县| 凉城县| 赣榆县| 乳源| 铜陵市| 永嘉县| 甘泉县| 炎陵县| 泌阳县| 博白县| 云和县| 太仆寺旗| 东丰县| 施甸县| 景德镇市| 栾城县| 屯昌县| 来凤县| 潜山县| 治多县| 洛阳市| 彩票| 陆川县| 山阳县| 芷江| 淮安市| 宝清县| 肇源县| 方城县|