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
          主站蜘蛛池模板: 永顺县| 杭州市| 若尔盖县| 阜南县| 海门市| 丽水市| 枣强县| 鹤山市| 临沧市| 安泽县| 进贤县| 万宁市| 财经| 深州市| 绥化市| 修武县| 临朐县| 图木舒克市| 克拉玛依市| 蓬安县| 丹凤县| 大荔县| 黑河市| 绥芬河市| 偃师市| 宜兴市| 嘉定区| 凤翔县| 泰安市| 天气| 肇庆市| 平遥县| 城口县| 凤山市| 兰溪市| 龙陵县| 伊宁市| 西乌珠穆沁旗| 武鸣县| 吴旗县| 高平市|