数据库编码utf8
我看了你的这个参考,发现有个问题:你的表和数据是这样的:
create table tx(
id int primary key,
c1 char(2),
c2 char(2),
c3 int
);
insert into tx values
(1 ,'A1','B1',9),
(2 ,'A2','B1',7),
(3 ,'A3','B1',4),
(4 ,'A4','B1',2),
(5 ,'A1','B2',2)
当我插入中文(把B1换成 外语)查询的时候查询就出错是什么情况?,是不是C2字段类型的问题?高手帮忙看下:查询语句
SET @EE='';
SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=/'',C2,'/'',',C3,0)) AS ',C2,',') FROM (SELECT DISTINCT C2 FROM TX) A;
SET @QQ=CONCAT('SELECT ifnull(c1,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;
char(2) 啊,
‘A1’ 正好,
中文 就 超载了……
SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=/'',C2,'/'',',C3,0)) AS `',C2,'`,') FROM (SELECT DISTINCT C2 FROM TX) A;
SET @QQ=CONCAT('SELECT ifnull(c1,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;
SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=/'',C2,'/'',',C3,0)) AS `',C2,'`,') FROM (SELECT DISTINCT C2 FROM TX) A;
SET @QQ=CONCAT('SELECT ifnull(c1,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');
select @QQ;
PREPARE stmt2 FROM @QQ;
贴出结果。
SELECT @EE:=CONCAT(@EE,'SUM(IF(subject=\'',subject,'\'',',sources,0)) AS `',subject,'`,') FROM (SELECT DISTINCT
subject FROM test) A;
sET @QQ=CONCAT('SELECT number as 学号,',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(sources) AS 总分,avg(sources) as
平均分 FROM test GROUP BY number ');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;
结果:
[SQL]
SET @EE='';
受影响的行: 0
时间: 0.000ms[SQL]
SELECT @EE:=CONCAT(@EE,'SUM(IF(subject=\'',subject,'\'',',sources,0)) AS `',subject,'`,') FROM (SELECT DISTINCT
subject FROM test) A;
受影响的行: 0
时间: 0.000ms[SQL]
sET @QQ=CONCAT('SELECT number as 学号,',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(sources) AS 总分,avg(sources) as
平均分 FROM test GROUP BY number ');
受影响的行: 0
时间: 0.015ms[SQL]
PREPARE stmt2 FROM @QQ;
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUM(sources) AS 总分,avg(sources) as
平均分 FROM test GROUP BY number' at line 1
SUM(IF(subject='yingyu',sources,0)) AS `yingyu`,
SUM(IF(subject='yingyu',sources,0)) AS `yingyu`,SUM(IF(subject='YuWen',sources,0)) AS `YuWen`,
SUM(IF(subject='yingyu',sources,0)) AS `yingyu`,SUM(IF(subject='YuWen',sources,0)) AS `YuWen`,SUM(IF(subject='ShuXue',sources,0)) AS `ShuXue`,
SUM(IF(subject='yingyu',sources,0)) AS `yingyu`,SUM(IF(subject='YuWen',sources,0)) AS `YuWen`,SUM(IF(subject='ShuXue',sources,0)) AS `ShuXue`,SUM(IF(subject='LiShi',sources,0)) AS `LiShi`,
SUM(IF(subject='yingyu',sources,0)) AS `yingyu`,SUM(IF(subject='YuWen',sources,0)) AS `YuWen`,SUM(IF(subject='ShuXue',sources,0)) AS `ShuXue`,SUM(IF(subject='LiShi',sources,0)) AS `LiShi`,SUM(IF(subject='ZhengZhi',sources,0)) AS `ZhengZhi`,
SUM(IF(subject='yingyu',sources,0)) AS `yingyu`,SUM(IF(subject='YuWen',sources,0)) AS `YuWen`,SUM(IF(subject='ShuXue',sources,0)) AS `ShuXue`,SUM(IF(subject='LiShi',sources,0)) AS `LiShi`,SUM(IF(subject='ZhengZhi',sources,0)) AS `ZhengZhi`,SUM(IF(subject='HuaXue',sources,0)) AS `HuaXue`,
SUM(IF(subject='yingyu',sources,0)) AS `yingyu`,SUM(IF(subject='YuWen',sources,0)) AS `YuWen`,SUM(IF(subject='ShuXue',sources,0)) AS `ShuXue`,SUM(IF(subject='LiShi',sources,0)) AS `LiShi`,SUM(IF(subject='ZhengZhi',sources,0)) AS `ZhengZhi`,SUM(IF(subject='HuaXue',sources,0)) AS `HuaXue`,SUM(IF(subject='快快快',sources,0)) AS `快快快`,