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中
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>";