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

2016-10-26 10:50:46 358 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

VS2019+EasyX五子棋开发：从图形界面到AI算法的完整实践图形界面编程是桌面应用开发的基础，其核心在于通过图形库将数据与逻辑可视化呈现，实现用户与程序的直观交互。EasyX作为一款轻量级C++图形库，封装了Windows GDI的底层细节，提供了简洁的API，极大降低了图形编程的门槛，尤其适合初学者快速构建小型游戏和工具。在工程实践中，结合Visual Studio强大的调试功能，开发者可以高效实现事件处理、资源管理和算法集成。五子棋项目正是这一技术栈的典型应用，它涵盖了棋盘绘制、鼠标交互、音效播放等核心模块，并引入了基于评分表的AI算法，通过评估棋型价值实现基础阅读详情

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

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

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

...全文

轨迹规划——带抛物线过渡的直线插补带抛物线过渡的直线插补阅读详情

keyeremail

MySQL 57,062 成员 56,759 内容

+ 关注 + 加入社区

切换为时间正序

keyeremail 2016-10-26

打赏
举报

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');

LongRui888 2016-10-26

打赏
举报

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

qq_34921650 2016-10-26

打赏
举报

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


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;
		}

动态查询没有写游标

keyeremail 2016-10-26

打赏
举报

我现在是只能写出一根据一个指标值来统计的方式 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来分年得出的结果但是我只能得到一种指标的我想要所有指标的应该怎么写?

keyeremail 2016-10-26

打赏
举报

我看过这个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)数据我这个是要不累加不统计一个全表完正数据是行转列

ACMAIN_CHM 2016-10-26

打赏
举报

可以参考下面的代码，如果有问题楼主可以把自己写的语句贴出一同分析。 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...

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

相关推荐