mysql 完整的动态行列置换应该怎么完成

[keyeremail]

举例说明一下
表里的数据是这样的
年度,指标A,指标B,指标C,指标D,指标E, 指标F,指标G等


客户要这样显示成这样的
年度 1995年 1996年 1997年 1998年 1999年
指标A
指标B
指标C
指标D
指标E
指标F
指标G

我也上网查了一下大部分转换列都是统计 实际就是转了一行 然后其他 部分以GROUP BY分组来显示.
单独一行的我也写出来了但是没什么用啊有没有什么办法用一个存储过程或者SQL直接能把数据查出来?
因为年度还要增加所以要写成动态的.

[ACMAIN_CHM]

可以参考下面的代码，如果有问题楼主可以把自己写的语句贴出一同分析。 http://blog.csdn.net/acmain_chm/article/details/4283943 MySQL交叉表 在某些数据库中有交叉表，但在MySQL中却没有这个功能，但网上看到有不少朋友想找出一个解决方法，特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下：数据样本： create table tx( id int primary key, c1 c...

[keyeremail]

我看过这个http://blog.csdn.net/acmain_chm/article/details/4283943但是感觉没有什么用 因为第一这个三列表,最后C2 和C3 的数据实际是统计出来的第一列的数据其时还是C1 的group by 出来的 mysql> SET @QQ=CONCAT('SELECT ifnull(c1,/'total/'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP'); 用这种方法只能打到一行数据就是就相当于这里的SUM(C3)数据 我这个是要不累加不统计一个全表完正数据是行转列

[keyeremail]

我现在是只能写出一根据一个指标值来统计的方式 SQL 代码如下
SET @EE='';
set @str_tmpE='';

SELECT @EE:=CONCAT(@EE,'MAX(IF(year=\'',year,'\'',',total,0)) AS ',year,'年,') as aa into @str_tmpE
FROM (SELECT DISTINCT year FROM test) A order by length(aa) desc limit 1;

#SELECT @EE;

SET @QQ=CONCAT('SELECT ',LEFT(@str_tmpE,char_length(@str_tmpE)-1),' FROM test');

PREPARE stmt FROM @QQ;

EXECUTE stmt ;

deallocate prepare stmt;

这是根据TOTAL来分年得出的结果但是我只能得到一种指标的我想要所有指标的应该怎么写?

[qq_34921650]

我感觉你和我遇到的应该一样 贴出以供参考

foreach ($sqlstaff as $key => &$value) {
			$sqlservice="select ifnull(waiter,'total') as total,sum(if(table_id='$value[table_id]',count,0)) AS '$value[table_id]',SUM(count) AS count from newboll_tablelog where company='172' group by waiter with rollup";
			$servicedata=$model->query($sqlservice);
			$serviceDate[$key]=$servicedata;
		}

动态查询 没有写游标

[LongRui888]

你先把你写的动态行转列代码，贴出把。 另外，把你的实验数据和表，创建代码都贴出来，不要用图片的

[keyeremail]

DROP TABLE IF EXISTS `test`; CREATE TABLE `test` ( `year` year(4) NOT NULL, `total` decimal(20,2) DEFAULT NULL, `primary_product` decimal(20,2) DEFAULT NULL, `food_animal` decimal(20,2) DEFAULT NULL, `beverage_tobacco` decimal(20,2) DEFAULT NULL, `material` decimal(20,2) DEFAULT NULL, `oil_wax` decimal(20,2) DEFAULT NULL, `industrial_product` decimal(20,2) DEFAULT NULL, `chemical` decimal(20,2) DEFAULT NULL, `goods` decimal(20,2) DEFAULT NULL, `machinery` decimal(20,2) DEFAULT NULL, `miscellaneous_goods` decimal(20,2) DEFAULT NULL, `other` decimal(20,2) DEFAULT NULL, `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of test -- ---------------------------- INSERT INTO `test` VALUES ('1995', '147.80', '21.85', '99.54', '13.70', '53.32', '4.54', '127.95', '90.94', '22.40', '314.07', '545.48', '0.06', '1'); INSERT INTO `test` VALUES ('1996', '150.48', '21.25', '102.31', '13.42', '59.31', '3.67', '121.23', '88.77', '24.98', '353.12', '564.24', '0.12', '2'); INSERT INTO `test` VALUES ('1997', '187.92', '23.53', '110.75', '10.49', '69.87', '6.47', '158.39', '102.27', '34.32', '437.09', '704.67', '0.04', '3'); INSERT INTO `test` VALUES ('1998', '137.12', '20.89', '106.13', '9.75', '51.75', '3.07', '162.20', '103.21', '32.77', '502.17', '702.00', '0.06', '4'); INSERT INTO `test` VALUES ('1999', '149.31', '19.41', '104.58', '7.71', '46.59', '1.32', '179.90', '103.73', '33.62', '588.36', '725.10', '0.09', '5'); INSERT INTO `test` VALUES ('2000', '242.03', '25.60', '122.82', '7.48', '78.55', '1.16', '227.43', '120.98', '42.46', '826.00', '862.78', '2.21', '6'); INSERT INTO `test` VALUES ('2001', '260.98', '26.38', '127.77', '8.73', '84.05', '1.11', '237.60', '133.52', '43.13', '949.01', '871.10', '5.84', '7'); INSERT INTO `test` VALUES ('2002', '325.96', '28.40', '146.21', '9.84', '84.35', '0.98', '290.56', '153.25', '59.55', '1269.76', '1011.53', '6.48', '8'); INSERT INTO `test` VALUES ('2003', '433.71', '34.10', '175.33', '10.19', '111.10', '1.15', '405.60', '195.86', '69.30', '1878.88', '1261.01', '9.56', '9'); INSERT INTO `test` VALUES ('2004', '593.69', '40.50', '188.70', '12.14', '144.76', '1.48', '558.18', '263.68', '106.54', '2682.91', '1563.93', '11.12', '10'); INSERT INTO `test` VALUES ('2005', '769.99', '49.39', '224.81', '11.83', '176.21', '2.68', '719.60', '357.72', '121.26', '3522.62', '1941.91', '16.09', '11'); INSERT INTO `test` VALUES ('2006', '968.78', '52.25', '257.22', '11.93', '177.76', '3.73', '911.47', '445.31', '174.36', '4563.64', '2380.29', '23.88', '12'); INSERT INTO `test` VALUES ('2007', '122.56', '61.47', '307.51', '13.96', '199.44', '3.03', '1154.68', '603.56', '218.94', '5771.89', '2968.53', '21.76', '13'); INSERT INTO `test` VALUES ('2008', '146.93', '77.48', '327.64', '15.30', '316.35', '5.74', '1356.98', '793.09', '261.43', '6733.25', '3346.06', '17.15', '14'); INSERT INTO `test` VALUES ('2009', '120.12', '63.99', '326.03', '16.41', '203.83', '3.16', '1135.64', '620.48', '184.75', '5904.27', '2996.70', '16.45', '15'); INSERT INTO `test` VALUES ('2010', '1577.54', '81.17', '411.53', '19.06', '267.00', '3.56', '1492.16', '875.87', '249.51', '7803.30', '3776.80', '14.68', '16'); INSERT INTO `test` VALUES ('2011', '189.81', '105.52', '504.97', '22.76', '322.76', '5.26', '1790.48', '1147.87', '319.00', '9019.12', '4594.10', '23.39', '17'); INSERT INTO `test` VALUES ('2012', '204.14', '105.81', '520.80', '25.90', '310.26', '5.45', '1943.54', '1136.29', '333.68', '9644.22', '5357.18', '14.17', '18'); INSERT INTO `test` VALUES ('2013', '220.19', '172.83', '557.29', '26.08', '337.92', '5.84', '2107.36', '1196.59', '306.53', '10392.46', '5814.48', '17.29', '19'); INSERT INTO `test` VALUES ('2014', '237.47', '127.05', '589.18', '28.83', '344.53', '6.23', '2230.41', '1345.93', '403.75', '10706.32', '6221.74', '22.67', '20'); INSERT INTO `test` VALUES ('2015', '225.30', '139.80', '581.60', '33.10', '279.40', '6.40', '2179.70', '1296.00', '391.10', '10594.50', '5881.50', '24.60', '21');