举例说明一下
表里的数据是这样的
年度,指标A,指标B,指标C,指标D,指标E, 指标F,指标G等
客户要这样显示成这样的
年度 1995年 1996年 1997年 1998年 1999年
指标A
指标B
指标C
指标D
指标E
指标F
指标G我也上网查了一下大部分转换列都是统计 实际就是转了一行 然后其他 部分以GROUP BY分组来显示.
单独一行的我也写出来了但是没什么用啊有没有什么办法用一个存储过程或者SQL直接能把数据查出来?
因为年度还要增加所以要写成动态的.
表里的数据是这样的
年度,指标A,指标B,指标C,指标D,指标E, 指标F,指标G等
客户要这样显示成这样的
年度 1995年 1996年 1997年 1998年 1999年
指标A
指标B
指标C
指标D
指标E
指标F
指标G我也上网查了一下大部分转换列都是统计 实际就是转了一行 然后其他 部分以GROUP BY分组来显示.
单独一行的我也写出来了但是没什么用啊有没有什么办法用一个存储过程或者SQL直接能把数据查出来?
因为年度还要增加所以要写成动态的.
解决方案 »
- Illegal mix of collations for operation '>='
- 求助,新安装的mysql,新增普通用户无法创建密码
- 现在的数据库,一个中文是一个字符还是两个字符?就是定义一个六个汉字的字段要定义为varchar(12)还是varchar(6)?
- 问个简单的查询问题,速度来拿分!
- 取1个表 前10% 的数据 这个sql如俄写?
- MySql中插入汉字
- mysql怎么写备份的sql语句
- object brower
- 一个网站资金流水记录表的设计
- mysql 触发器插入数据可以,update前后的insert都执行了,但是update没有反应
- SQL 查询
- mysql5.6触发器问题
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');
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...
因为第一这个三列表,最后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)数据
我这个是要不累加不统计一个全表完正数据是行转列
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来分年得出的结果但是我只能得到一种指标的我想要所有指标的应该怎么写?
$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;
}
动态查询 没有写游标