有几条数据,
姓名 零件名称 加工数目
张三 底板 100
李四 螺丝 50
张三 螺丝 50
王五 机架 30
查询后,想要的效果是
张三 李四 王五
底板 100
螺丝 50 50
机架 30
这个sql语句该怎么写?
语句在php要能执行,
最好给出语句注释
姓名 零件名称 加工数目
张三 底板 100
李四 螺丝 50
张三 螺丝 50
王五 机架 30
查询后,想要的效果是
张三 李四 王五
底板 100
螺丝 50 50
机架 30
这个sql语句该怎么写?
语句在php要能执行,
最好给出语句注释
sum(if(name='李四',加工数目,0)) as 李四,
sum(if(name='王五',加工数目,0)) as 王五 from tt group by 零件名称哪个地方不明白
,sum(case when 姓名='李四' then 加工数目 end) as 李四
,sum(case when 姓名='王五' then 加工数目 end) as 王五
from tb
group by 零件名称
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;
这段代码mysql可以达到我要的效果,但在php怎么转换,取到查询的结果,并显示出来
2、在MYSQL中用变量累加的方式,
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');
分步SELECT @EE
SELECT @QQ,看看内容是什么
SET @ee='';# MySQL 返回的查询结果为空(即零行)。SELECT @EE:=CONCAT(@EE,'SUM(IF(`name`=\'',`name`,'\'',',Due,0)) AS ',`name`,',') FROM (SELECT DISTINCT `name` FROM ty_test) A;# 行数: 3SET @QQ=CONCAT('SELECT ifnull(picName,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),',SUM(Due) AS TOTAL FROM ty_test GROUP BY `picname` WITH ROLLUP');# MySQL 返回的查询结果为空(即零行)。PREPARE stmt2 FROM @QQ;# MySQL 返回的查询结果为空(即零行)。EXECUTE stmt2;# 行数: 5
这是在phpmyadmin中执行显示的东西
再执行,取得执行后的记录集( OR 生成新表)
生成1个临时表,再访问此表取数据(newtt)SET @ee='';# MySQL 返回的查询结果为空(即零行)。SELECT @EE:=CONCAT(@EE,'SUM(IF(`name`=\'',`name`,'\'',',Due,0)) AS ',`name`,',') FROM (SELECT DISTINCT `name` FROM ty_test) A;# 行数: 3SET @QQ=CONCAT('create table newtt as SELECT ifnull(picName,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),',SUM(Due) AS TOTAL FROM ty_test GROUP BY `picname` WITH ROLLUP');# MySQL 返回的查询结果为空(即零行)。PREPARE stmt2 FROM @QQ;# MySQL 返回的查询结果为空(即零行)。EXECUTE stmt2;# 行数: 5;
select * from newtt
$RsArray = $db->SelectSQL($SQL);
foreach ($RsArray as $i => $Rs)
{
$sq=" SET @ee='';";
$sq.="SELECT @EE:=CONCAT(@EE,'SUM(IF(`name`=\\'',`".$Rs[i][0]."`,'\\'',',Due,0)) AS ',`name`,',') FROM (SELECT DISTINCT `name` FROM ty_test) A ;";
$sq.="SET @QQ=CONCAT('SELECT ifnull(picName,\\'total\\'),',LEFT(@EE,LENGTH(@EE)-1),',SUM(Due) AS TOTAL FROM ty_test GROUP BY `picname` WITH ROLLUP');";
$sq.="PREPARE stmt2 FROM @QQ;EXECUTE stmt2;";
}
// 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);
?>