下边是两个表的表结构
mysql> describe tx;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| c1 | char(2) | YES | | NULL | |
| c2 | char(2) | YES | | NULL | |
| c3 | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> describe test;
+---------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| Number | int(11) | YES | | NULL | |
| Subject | char(20) | YES | | NULL | |
| Sources | int(11) | YES | | NULL | |
+---------+----------+------+-----+---------+----------------+
我用动态语句查询tx表可以查出结果：
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 c1 as 学号,',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS 总分,avg(c3) as
平均分 FROM TX GROUP BY C1 ');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;
+------+------+------+------+------+------+--------+
| 学号 | B1 | B2 | B3 | B4 | 总分 | 平均分 |
+------+------+------+------+------+------+--------+
| A1 | 9 | 2 | 1 | 11 | 23 | 4.6000 |
| A2 | 7 | 9 | 8 | 7 | 31 | 6.2000 |
| A3 | 4 | 8 | 8 | 8 | 28 | 5.6000 |
| A4 | 2 | 5 | 6 | 14 | 27 | 5.4000 |
+------+------+------+------+------+------+--------+
4 rows in set (0.00 sec)
但是我用类似的语句查test表就查不出结果：
SET @EE='';
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;
出错提示：
-----------+
4 rows in set (0.00 sec)
mysql>
mysql> sET @QQ=CONCAT('SELECT number as 学号,',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(so
urces) AS 总分,avg(sources) as 平均分 FROM test GROUP BY number ');
Query OK, 0 rows affected (0.00 sec)
mysql> PREPARE stmt2 FROM @QQ;
ERROR 1064 (42000): 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(s
ources) AS 总分,avg(sources) as 平均分 FROM test GROUP BY number' at line 1
高手帮忙看下，该怎么改？