| 网站首页 | 业界新闻 | 小组 | 威客 | 人才 | 下载频道 | 博客 | 代码贴 | 在线编程 | 编程论坛
欢迎加入我们,一同切磋技术
用户名:   
 
密 码:  
共有 1792 人关注过本帖
标题:帮忙看看,语句问题,交叉表
只看楼主 加入收藏
BlizzardKen
Rank: 1
来 自:广西桂林
等 级:新手上路
帖 子:36
专家分:0
注 册:2011-4-26
结帖率:33.33%
收藏
 问题点数:0 回复次数:0 
帮忙看看,语句问题,交叉表
下边是两个表的表结构
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

高手帮忙看下,该怎么改?
搜索更多相关主题的帖子: describe mysql 
2012-05-11 14:15
快速回复:帮忙看看,语句问题,交叉表
数据加载中...
 
   



关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.010578 second(s), 7 queries.
Copyright©2004-2024, BCCN.NET, All Rights Reserved