有几条数据,
姓名 零件名称 加工数目
张三 底板 100
李四 螺丝 50
张三 螺丝 50
王五 机架 30
查询后,想要的效果是
张三 李四 王五
底板 100
螺丝 50 50
机架 30
这个sql语句该怎么写?
姓名 零件名称 加工数目
张三 底板 100
李四 螺丝 50
张三 螺丝 50
王五 机架 30
查询后,想要的效果是
张三 李四 王五
底板 100
螺丝 50 50
机架 30
这个sql语句该怎么写?
sum(if(姓名='李四',加工数目,0)) as 李四,
sum(if(姓名='王五',加工数目,0)) as 王五
from tt group by 零件名称
sum(if(姓名='张三',加工数目,null)) as 张三,
sum(if(姓名='李四',加工数目,null)) as 李四,
sum(if(姓名='王五',加工数目,null)) as 王五
from 有几条数据
group by 零件名称
MySQL交叉表
SET @EE='';
SELECT @EE:=CONCAT(@EE,'SUM(IF(Materialname=\'',Materialname,'\'',',Due,0)) AS ',Materialname,',') FROM (SELECT DISTINCT Materialname FROM ty_salary) A;
SET @QQ=CONCAT('SELECT ifnull(C1,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),',SUM(Due) AS TOTAL FROM ty_salary GROUP BY C1 WITH ROLLUP');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;
贴建表(ty_salary)及插入记录的SQL
将@EE中的内容显示出来看看
C1 varchar(20) utf8_general_ci 否
Materialid varchar(50) utf8_general_ci 否
Picname varchar(50) utf8_general_ci 否
Materialname varchar(50) utf8_general_ci 否
Stepname varchar(20) utf8_general_ci 否
Quota double 否
Good int(32) 否
Substand int(32) 否
Materialbad int(32) 否
Mybad int(32) 否
Deduct double 否
Due double 否
Time datetime 否
Re varchar(50) utf8_general_ci 否
以供测试
Name varchar(20) ,
Materialid varchar(50),
Picname varchar(50),
Due int(32)
);
insert into ty_test values
('zxj','1120','机床','50');
insert into ty_test values
('xj','1110','底板','10');
insert into ty_test values
('zxj','1130','螺丝','20');
insert into ty_test values
('xj','11120','机床','35');
insert into ty_test values
('ty','1110','底板','25');
insert into ty_test values
('ty','1130','螺丝','15');
SET @EE='';
SELECT @EE:=CONCAT(@EE,'SUM(IF(Materialid=\'',Materialid,'\'',',Due,0)) AS ',Materialid,',') FROM (SELECT DISTINCT Materialid FROM ty_test) A;
SET @QQ=CONCAT('SELECT ifnull(Name,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),',SUM(Due) AS TOTAL FROM ty_test GROUP BY C1 WITH ROLLUP');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;
SELECT @EE:=CONCAT(@EE,'SUM(IF(Materialid=\'',picname,'\'',',Due,0)) AS ',picname,',') FROM (SELECT DISTINCT picname FROM ty_test) A;
SET @QQ=CONCAT('SELECT ifnull(Name,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),'SUM(Due) AS TOTAL FROM ty_test GROUP BY `name` WITH ROLLUP');
SELECT @qq;
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;
SET @ee='';
SELECT @EE:=CONCAT(@EE,'SUM(IF(`name`=\'',`name`,'\'',',Due,0)) AS ',`name`,',') FROM (SELECT DISTINCT `name` FROM ty_test) A;
SET @QQ=CONCAT('SELECT ifnull(picName,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),',SUM(Due) AS TOTAL FROM ty_test GROUP BY `picname` WITH ROLLUP');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;
记录集,循环,累加字符串生成SQL语句,再执行
// Connecting, selecting database
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('my_database') or die('Could not select database');// Performing SQL query
$query = 'SELECT * FROM my_table';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());// Printing results in HTML
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "\t<tr>\n";
foreach ($line as $col_value) {
echo "\t\t<td>$col_value</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>\n";// Free resultset
mysql_free_result($result);// Closing connection
mysql_close($link);
?>