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
          主站蜘蛛池模板: 临汾市| 和田县| 巨鹿县| 田林县| 师宗县| 谢通门县| 汉川市| 南投县| 凌源市| 政和县| 宁明县| 罗平县| 桐梓县| 宝坻区| 北票市| 本溪市| 晋州市| 隆尧县| 即墨市| 丹阳市| 北票市| 文化| 三穗县| 灵宝市| 隆昌县| 保靖县| 木兰县| 新疆| 贵溪市| 营口市| 陇南市| 民权县| 拉孜县| 蒙山县| 北川| 吉林市| 南雄市| 武宣县| 和政县| 左权县| 新丰县|