select left(a.recvtime,16) as lr, count(a.id) a_id,left(b.sendtime,16) as st, b.status, count(b.id) as b_id,count(distinct b.feetermid),sum(b.fee)/100 as b_fee from table1 a,table2 b where a.packageid in (1,23,13) and b.pid=a.packageid group by lr,st;这样写哪儿不对,怎么结果不正确?
select left(recvtime,16) as lr, count(*) as num from table1 where packageid in (1,23,13) group by left(recvtime,16)2.
select left(sendtime,16) as st, status, count(*),count(distinct feetermid),sum(fee)/100 from table2 where pid in (1,23,13) group by left(sendtime,16),status这是两个表分开统计的结果
你可能理解错我的意思了,我分开统计的语句也是正确的,我是想用一个语句实现这个查询功能,用关联怎么实现呢?
但是这将产生多行的结果,这应该不是你所需要的
结果中先按lrt分组,再在lr中按st分组。如果出现lr、st不相交的情况则情况还要复杂。所以在不能预知结果的情况下,不要做这种关联其实对这种不相关的结果,还是分开来查询为好
6月29日 count(distinct b.feetermid) count(a.id) count(b.id)
$arr_up[0]=array("lr"=>"2006-06-06","cntup"=>"3");
$arr_up[1]=array("lr"=>"2006-06-07","cntup"=>"4");
$arr_up[2]=array("lr"=>"2006-06-08","cntup"=>"3");
$arr_up[3]=array("lr"=>"2006-06-10","cntup"=>"4");
$arr_up[4]=array("lr"=>"2006-06-11","cntup"=>"3");
$arr_up[5]=array("lr"=>"2006-06-13","cntup"=>"4");$arr_dn[0]=array("st"=>"2006-06-05","cntdn"=>"3");
$arr_dn[1]=array("st"=>"2006-06-06","cntdn"=>"2");
$arr_dn[2]=array("st"=>"2006-06-08","cntdn"=>"3");
$arr_dn[3]=array("st"=>"2006-06-09","cntdn"=>"2");
$arr_dn[4]=array("st"=>"2006-06-11","cntdn"=>"3");
$arr_dn[5]=array("st"=>"2006-06-12","cntdn"=>"2");$sc1=count($arr_up);
$sc2=count($arr_dn);
$i=0;$i_u=0;
$j=0;$j_u=0;
echo "<table border=\"1\">\n";
while($i<$sc1 or $j<$sc2) {
if(($arr_up[$i]["lr"]<$arr_dn[$j]["st"] or $j==$sc2) and $i<$sc1){
echo "<tr><td>".$arr_up[$i]["lr"]."</td><td>".$arr_up[$i]["cntup"]."</td><td>0</td></tr>\n";
$i++;
}
if(($arr_up[$i]["lr"]>$arr_dn[$j]["st"] or $i==$sc1) and $j<$sc2){
echo "<tr><td>".$arr_dn[$j]["st"]."</td><td>0</td><td>".$arr_dn[$j]["cntdn"]."</td></tr>\n";
$j++;
}
if($arr_up[$i]["lr"]==$arr_dn[$j]["st"] && $i<$sc1 and $j<$sc2){
echo "<tr><td>".$arr_up[$i]["lr"]."</td><td>".$arr_up[$i]["cntup"]."</td><td>".$arr_dn[$j]["cntdn"]."</td></tr>\n";
$i++;
$j++;
}
}echo "</table>\n";?>