http://blog.csdn.net/acmain_chm/article/details/4283943MySQL交叉表 在某些数据库中有交叉表,但在MySQL中却没有这个功能,但网上看到有不少朋友想找出一个解决方法,特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下:数据样本: create table tx( id int primary key, c1 c...
SET @EE=''; SELECT @EE:= CONCAT(@EE,'SUM(IF(date2=\'',date2,'\'',',value2,0)) AS \'',date2,'\',') FROM (SELECT DISTINCT date2 FROM b) b; SET @QQ=CONCAT('SELECT ',LEFT(@EE,LENGTH(@EE)-1),' from (select a.date1 as date1,a.value1 as value1, b.date2 as date2,b.value2 as value2 from a left join b on a.date1=b.date1 ) as tmp2 GROUP BY date1'); PREPARE stmt2 FROM @QQ; EXECUTE stmt2; 已解决 谢谢
http://blog.csdn.net/acmain_chm/article/details/4283943MySQL交叉表
在某些数据库中有交叉表,但在MySQL中却没有这个功能,但网上看到有不少朋友想找出一个解决方法,特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下:数据样本: create table tx( id int primary key, c1 c...
SET @EE='';
SELECT @EE:= CONCAT(@EE,'SUM(IF(date2=\'',date2,'\'',',value2,0)) AS \'',date2,'\',') FROM
(SELECT DISTINCT date2 FROM b) b;
SET @QQ=CONCAT('SELECT ',LEFT(@EE,LENGTH(@EE)-1),' from (select a.date1 as date1,a.value1 as value1, b.date2 as date2,b.value2 as value2 from a left join b on a.date1=b.date1 ) as tmp2 GROUP BY date1');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;
已解决 谢谢