create table tx(
id int primary key,
c1 char(2),
c2 char(2),
c3 int
); insert into tx values
(1 ,'A1','B1',9),
(2 ,'A2','B1',7),
(3 ,'A3','B1',4),
(4 ,'A4','B1',2),
(5 ,'A1','B2',2),
(6 ,'A2','B2',9),
(7 ,'A3','B2',8),
(8 ,'A4','B2',5),
(9 ,'A1','B3',1),
(10 ,'A2','B3',8),
(11 ,'A3','B3',8),
(12 ,'A4','B3',6),
(13 ,'A1','B4',8),
(14 ,'A2','B4',2),
(15 ,'A3','B4',6),
(16 ,'A4','B4',9),
(17 ,'A1','B4',3),
(18 ,'A2','B4',5),
(19 ,'A3','B4',2),
(20 ,'A4','B4',5); mysql> select * from tx;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | A1 | B1 | 9 |
| 2 | A2 | B1 | 7 |
| 3 | A3 | B1 | 4 |
| 4 | A4 | B1 | 2 |
| 5 | A1 | B2 | 2 |
| 6 | A2 | B2 | 9 |
| 7 | A3 | B2 | 8 |
| 8 | A4 | B2 | 5 |
| 9 | A1 | B3 | 1 |
| 10 | A2 | B3 | 8 |
| 11 | A3 | B3 | 8 |
| 12 | A4 | B3 | 6 |
| 13 | A1 | B4 | 8 |
| 14 | A2 | B4 | 2 |
| 15 | A3 | B4 | 6 |
| 16 | A4 | B4 | 9 |
| 17 | A1 | B4 | 3 |
| 18 | A2 | B4 | 5 |
| 19 | A3 | B4 | 2 |
| 20 | A4 | B4 | 5 |
+----+------+------+------+
20 rows in set (0.00 sec)mysql>期望结果+------+-----+-----+-----+-----+------+
|C1 |B1 |B2 |B3 |B4 |Total |
+------+-----+-----+-----+-----+------+
|A1 |9 |2 |1 |11 |23 |
|A2 |7 |9 |8 |7 |31 |
|A3 |4 |8 |8 |8 |28 |
|A4 |2 |5 |6 |14 |27 |
|Total |22 |24 |23 |40 |109 |
+------+-----+-----+-----+-----+------+
在php中如何实现期望结果,结果显示在html中
id int primary key,
c1 char(2),
c2 char(2),
c3 int
); insert into tx values
(1 ,'A1','B1',9),
(2 ,'A2','B1',7),
(3 ,'A3','B1',4),
(4 ,'A4','B1',2),
(5 ,'A1','B2',2),
(6 ,'A2','B2',9),
(7 ,'A3','B2',8),
(8 ,'A4','B2',5),
(9 ,'A1','B3',1),
(10 ,'A2','B3',8),
(11 ,'A3','B3',8),
(12 ,'A4','B3',6),
(13 ,'A1','B4',8),
(14 ,'A2','B4',2),
(15 ,'A3','B4',6),
(16 ,'A4','B4',9),
(17 ,'A1','B4',3),
(18 ,'A2','B4',5),
(19 ,'A3','B4',2),
(20 ,'A4','B4',5); mysql> select * from tx;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | A1 | B1 | 9 |
| 2 | A2 | B1 | 7 |
| 3 | A3 | B1 | 4 |
| 4 | A4 | B1 | 2 |
| 5 | A1 | B2 | 2 |
| 6 | A2 | B2 | 9 |
| 7 | A3 | B2 | 8 |
| 8 | A4 | B2 | 5 |
| 9 | A1 | B3 | 1 |
| 10 | A2 | B3 | 8 |
| 11 | A3 | B3 | 8 |
| 12 | A4 | B3 | 6 |
| 13 | A1 | B4 | 8 |
| 14 | A2 | B4 | 2 |
| 15 | A3 | B4 | 6 |
| 16 | A4 | B4 | 9 |
| 17 | A1 | B4 | 3 |
| 18 | A2 | B4 | 5 |
| 19 | A3 | B4 | 2 |
| 20 | A4 | B4 | 5 |
+----+------+------+------+
20 rows in set (0.00 sec)mysql>期望结果+------+-----+-----+-----+-----+------+
|C1 |B1 |B2 |B3 |B4 |Total |
+------+-----+-----+-----+-----+------+
|A1 |9 |2 |1 |11 |23 |
|A2 |7 |9 |8 |7 |31 |
|A3 |4 |8 |8 |8 |28 |
|A4 |2 |5 |6 |14 |27 |
|Total |22 |24 |23 |40 |109 |
+------+-----+-----+-----+-----+------+
在php中如何实现期望结果,结果显示在html中
解决方案 »
- 请高手赐教 50分
- 怎么在textarea 实现查找功能?
- php 页面跳转
- php导出长数据到excel文件时的自动科学计数问题
- php 多选题评分算法 求指导
- 数据数(phpmyadmin)为什么老是用不了?
- 需要编写一个进销存管理系统
- 判断从第一页传过来的数据,若数据库中不存该数据(ID字段),就打开注册页,并把数据传过去,否则打开出错页面,怎么做?
- if(empty(strpos($fileele["dirname"],"\\images\\"))) 為什麼會出錯???
- centos 7.0最小化安装nginx 源代码编译安装 过程都记录下来了 不能访问 这是什么原因?
- PHP的echo!!谢谢!!
- PHP JQUERY AJAX如何实现多行列表修改删除
A1 |9 |2 |1 |11 这行可以通过select c1,c2,sum(c3)as ct from tx group by c1,c2 having c1='A1' order by c2 asc查询,然后循环写出来就可以了
这行的统计结果23可以通过select sum(c3)as ct from tx group by c1 having c1='A1' order by c1 asc查询出来其他行的查询你参照着写就可以了!
我想在PHP实现这个帖子里mysql交叉表的效果,我的列数是动态的。
现在它这个帖子里最后一个解决方法在mysql客户端可以使用,但在php无法执行那个sql语句,
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');
这是sql语句,下面这段语句可以在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');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;
,现在我想把它在php执行,并把结果输出到HTML网页
"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;";
把我之前的语句改写就行了!
select c1,c2,sum(c3)as ct from tx group by c1,c2 order by c2 asc
select c1,sum(c3)as ct from tx group by c1 order by c1 asc
你执行下看看结果如何~
ps:12楼强力党
$sql = "select c1,c2,c3 from tx order by c1";
$result = mysql_query($sql,$connectionhandle);
while(list(a,b,c) = mysql_fetch_row($result))
{
if(!isset(d2_array[a][b]))
{
d2_array[a][b] = 0;
}
d2_array[a][b] +=c;
}在显示的时候设置规则遍历即可,以输出表格第一行为例foreach($d2_array as $key=>v_arr)
{
if($key == "A1")
{
usort($d2_array[$key]); //如果B的值没有排序规则就需要自己判断
foreach($d2_array[$key] as $k=>$v)
{
echo $v;
}
}
}
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$query = <<<query
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;
query;/* execute multi query */
if ($mysqli->multi_query($query)) {
do {
/* store first result set */
if ($result = $mysqli->store_result()) {
$k = 0;
while ($row = $result->fetch_row()) {
if(count($row) > 1)
{
if(!$fields)
{
while($f = $result->fetch_field())
{
$fields[] = $f->name;
}
}
foreach( $row as $v)
{
$values[$k][] = $v;
}
$k++; }
}
$result->close();
} } while ($mysqli->next_result());
}echo "<table>";
echo "<tr><th>";
echo implode('</th><th>',$fields);
echo "</th></tr>";
foreach((array)$values as $val)
{
echo "<tr><td>";
echo implode('</td><td>',$val);
echo "</td></tr>";
}
echo "</table>";
/* close connection */
$mysqli->close();
参考
$conn = mysql_connect('localhost','root','');
mysql_select_db('test',$conn);$sql = "select * from tx order by c1,c2";$rs = mysql_query($sql);
while($res = mysql_fetch_object($rs))
{
$cl[$res->c2] = true;
$ra[$res->c1][$res->c2] += $res->c3;
$cc[$res->c2] += $res->c3;
}echo "<table>";
echo "<tr>";
echo "<th> A/C </th>";
foreach($cl as $k=>$v)
{
echo "<th>".$k."</th>";
}
echo "</tr>";foreach($ra as $k=>$v)
{
$rv = 0;
echo "<tr><td>{$k}</td>";
foreach($v as $v1)
{
echo "<td>".$v1."</td>";
$rv += $v1;
}
echo "<td>{$rv}</td></tr>";
}
echo "<tr><td>Total</td>";
foreach($cc as $v)
{
echo "<td>{$v}</td>";
}
echo "<td>".array_sum($cc)."</td>";
echo "</tr>";
echo "</table>";
如果同样类型记录插入2条以上,就显示有问题,没有把2条记录之和算出来,显示成一条记录
A1,B1,9
A1,B1,10
要的效果是
A1,B1,19
而不是
A1,B1,9
A1,B1,10
$ra[$res->c1][$res->c2] += $res->c3;
这样有可能显示成两行?你是copy代码运行了吗?
\ AA 刘丽 天心 李飞 total
后门板 2.387 22.134 15.407 39.928
同步轮轴 154.34 154.34
电机固定板 135.966 135.966
磁钢 110 110
Total 2.387 312.44 110 15.407 440.234
应该是如果没有,则应该是0,
foreach($ra as $k=>$v)
{
$rv = 0;
echo "<tr><td>{$k}</td>";
foreach($v as $v1)
{
echo "<td>".($v1+0)."</td>";
$rv += $v1;
}
echo "<td>".($rv+0)."</td></tr>";
}
foreach($array as $key=>$value)
{
if(array_key_exists($key,$array))
{}
}
改改,多次循环。$conn = mysql_connect('localhost','root','');
mysql_select_db('test',$conn);
mysql_query("set names utf8");
$sql = "select * from tx order by c1,c2";$rs = mysql_query($sql);
while($res = mysql_fetch_object($rs))
{
$ra[$res->c2][$res->c1] += $res->c3;
$rk[$res->c1] += $res->c3;
$cc[$res->c2] += $res->c3;
}echo "<table>";
echo "<tr>";
echo "<th> A/C </th>";
foreach(array_keys($cc) as $k=>$v)
{
echo "<th>".$v."</th>";
}
echo "<th>Total</th>";
echo "</tr>";
foreach(array_keys($rk) as $key=>$kv){
echo "<tr>";
echo "<td>{$kv}</td>";
foreach(array_keys($ra) as $k=>$v) echo "<td>".($ra[$v][$kv]+0)."</td>";
echo "<td>".($rk[$kv] + 0)."</td>";
echo "</tr>";
}
echo "<tr><td>Total</td>";
foreach($cc as $v)
{
echo "<td>{$v}</td>";
}
$sc = array_sum($cc);echo "<td>".($sc + 0)."</td>";
echo "</tr>";
echo "</table>";