select class2 ,January2014_Number_stores,January2014_Quantity_sold, January2014_qn=January2014_Quantity_sold/January2014_Number_stores ,February2014_Number_stores,February2014_Quantity_sold, February2014_qn=February2014_Quantity_sold/February2014_Number_stores ,March2014_Number_stores,March2014_Quantity_sold, March2014_qn=March2014_Quantity_sold/March2014_Number_stores from ( select db_bi.class2 ,January2014_Number_stores = count(distinct case when left(db_bi.qsq,6) <='201401' then db_bi.dbno_bi else null end) ,January2014_Quantity_sold = SUM(case left(CMD..cmd_xjjx_sale.outdate,6) when '201401'then CMD..cmd_xjjx_sale.nb else 0 end) ,February2014_Number_stores = count(distinct case when left(db_bi.qsq,6) <='201402' then db_bi.dbno_bi else null end) ,February2014_Quantity_sold = SUM(case left(CMD..cmd_xjjx_sale.outdate,6) when '201402'then CMD..cmd_xjjx_sale.nb else 0 end) ,March2014_Number_stores = count(distinct case when left(db_bi.qsq,6) <='201403' then db_bi.dbno_bi else null end) ,March2014_Quantity_sold = SUM(case left(CMD..cmd_xjjx_sale.outdate,6) when '201403'then CMD..cmd_xjjx_sale.nb else 0 end) ,April2014_Number_stores = count(distinct case when left(db_bi.qsq,6) <='201404' then db_bi.dbno_bi else null end) ,April2014_Quantity_sold = SUM(case left(CMD..cmd_xjjx_sale.outdate,6) when '201404'then CMD..cmd_xjjx_sale.nb else 0 end) from db_bi ,CMD..cmd_xjjx_sale ,BI_Product_Information where db_bi.dbno = CMD..cmd_xjjx_sale.store and BI_Product_Information.colthno = CMD..cmd_xjjx_sale.colthno and CMD..cmd_xjjx_sale.outdate>='2014-01' and CMD..cmd_xjjx_sale.outdate<='2014-04' and charindex( '已撤',db_bi.names) = 0 and db_bi.class2 in ('A','B','C','O') and closeif <> 1 and flags_bi in('1','2','3','4') and CMD..cmd_xjjx_sale.endprice <> 0 group by db_bi.class2 ) a order by class2
class2 为 B、C 时的相应值都不一样 你能确定表数据没有变化吗?
class2为B、C时值不一样;能确定表数据没变化
这下悲剧鸟....有好几个都是同样的sql用程序和直接在数据库编译器中执行的结果不一样(确定是同样的sql,确定数据库不是实时更新的) 测试如下 代码: <?php include_once 'DB_connmssql.php'; $sql="select class2 ,January2014_Number_stores,January2014_Quantity_sold, January2014_qn=January2014_Quantity_sold/January2014_Number_stores,February2014_Number_stores,February2014_Quantity_sold, February2014_qn=February2014_Quantity_sold/February2014_Number_stores,March2014_Number_stores,March2014_Quantity_sold, March2014_qn=March2014_Quantity_sold/March2014_Number_stores from (select db_bi.class2,January2014_Number_stores = count(distinct case when left(db_bi.qsq,6) <='201401' then db_bi.dbno_bi else null end),January2014_Quantity_sold = SUM(case left(CMD..cmd_xjjx_sale.outdate,6) when '201401'then CMD..cmd_xjjx_sale.nb else 0 end),February2014_Number_stores = count(distinct case when left(db_bi.qsq,6) <='201402' then db_bi.dbno_bi else null end),February2014_Quantity_sold = SUM(case left(CMD..cmd_xjjx_sale.outdate,6) when '201402'then CMD..cmd_xjjx_sale.nb else 0 end),March2014_Number_stores = count(distinct case when left(db_bi.qsq,6) <='201403' then db_bi.dbno_bi else null end),March2014_Quantity_sold = SUM(case left(CMD..cmd_xjjx_sale.outdate,6) when '201403'then CMD..cmd_xjjx_sale.nb else 0 end)from db_bi ,CMD..cmd_xjjx_sale ,BI_Product_Information where db_bi.dbno = CMD..cmd_xjjx_sale.store and BI_Product_Information.colthno = CMD..cmd_xjjx_sale.colthno and CMD..cmd_xjjx_sale.outdate>='2014-01' and CMD..cmd_xjjx_sale.outdate<='2014-04' and charindex( '已撤',db_bi.names) = 0 and db_bi.class2 in ('A','B','C','O') and closeif <> 1 and flags_bi in('1','2','3','4') and CMD..cmd_xjjx_sale.endprice <> 0 group by db_bi.class2 ) a order by class2"; $query = mssql_query($sql); while($row=mssql_fetch_array($query)){ print_r($row); echo "<br><br>"; }?>结果
2)在程序处输出sql,并且打印结果集看看。
,January2014_Number_stores,January2014_Quantity_sold, January2014_qn=January2014_Quantity_sold/January2014_Number_stores
,February2014_Number_stores,February2014_Quantity_sold, February2014_qn=February2014_Quantity_sold/February2014_Number_stores
,March2014_Number_stores,March2014_Quantity_sold, March2014_qn=March2014_Quantity_sold/March2014_Number_stores
from (
select db_bi.class2
,January2014_Number_stores = count(distinct case when left(db_bi.qsq,6) <='201401' then db_bi.dbno_bi else null end)
,January2014_Quantity_sold = SUM(case left(CMD..cmd_xjjx_sale.outdate,6) when '201401'then CMD..cmd_xjjx_sale.nb else 0 end)
,February2014_Number_stores = count(distinct case when left(db_bi.qsq,6) <='201402' then db_bi.dbno_bi else null end)
,February2014_Quantity_sold = SUM(case left(CMD..cmd_xjjx_sale.outdate,6) when '201402'then CMD..cmd_xjjx_sale.nb else 0 end)
,March2014_Number_stores = count(distinct case when left(db_bi.qsq,6) <='201403' then db_bi.dbno_bi else null end)
,March2014_Quantity_sold = SUM(case left(CMD..cmd_xjjx_sale.outdate,6) when '201403'then CMD..cmd_xjjx_sale.nb else 0 end)
,April2014_Number_stores = count(distinct case when left(db_bi.qsq,6) <='201404' then db_bi.dbno_bi else null end)
,April2014_Quantity_sold = SUM(case left(CMD..cmd_xjjx_sale.outdate,6) when '201404'then CMD..cmd_xjjx_sale.nb else 0 end)
from db_bi
,CMD..cmd_xjjx_sale ,BI_Product_Information
where db_bi.dbno = CMD..cmd_xjjx_sale.store
and BI_Product_Information.colthno = CMD..cmd_xjjx_sale.colthno
and CMD..cmd_xjjx_sale.outdate>='2014-01'
and CMD..cmd_xjjx_sale.outdate<='2014-04'
and charindex( '已撤',db_bi.names) = 0
and db_bi.class2 in ('A','B','C','O')
and closeif <> 1
and flags_bi in('1','2','3','4')
and CMD..cmd_xjjx_sale.endprice <> 0 group by db_bi.class2
) a
order by class2
你能确定表数据没有变化吗?
测试如下 代码:
<?php
include_once 'DB_connmssql.php';
$sql="select class2 ,January2014_Number_stores,January2014_Quantity_sold,
January2014_qn=January2014_Quantity_sold/January2014_Number_stores,February2014_Number_stores,February2014_Quantity_sold,
February2014_qn=February2014_Quantity_sold/February2014_Number_stores,March2014_Number_stores,March2014_Quantity_sold,
March2014_qn=March2014_Quantity_sold/March2014_Number_stores from (select db_bi.class2,January2014_Number_stores = count(distinct case when left(db_bi.qsq,6) <='201401' then db_bi.dbno_bi else null end),January2014_Quantity_sold = SUM(case left(CMD..cmd_xjjx_sale.outdate,6) when '201401'then CMD..cmd_xjjx_sale.nb else 0 end),February2014_Number_stores = count(distinct case when left(db_bi.qsq,6) <='201402' then db_bi.dbno_bi else null end),February2014_Quantity_sold = SUM(case left(CMD..cmd_xjjx_sale.outdate,6) when '201402'then CMD..cmd_xjjx_sale.nb else 0 end),March2014_Number_stores = count(distinct case when left(db_bi.qsq,6) <='201403' then db_bi.dbno_bi else null end),March2014_Quantity_sold = SUM(case left(CMD..cmd_xjjx_sale.outdate,6) when '201403'then CMD..cmd_xjjx_sale.nb else 0 end)from db_bi
,CMD..cmd_xjjx_sale
,BI_Product_Information
where db_bi.dbno = CMD..cmd_xjjx_sale.store
and BI_Product_Information.colthno = CMD..cmd_xjjx_sale.colthno
and CMD..cmd_xjjx_sale.outdate>='2014-01'
and CMD..cmd_xjjx_sale.outdate<='2014-04'
and charindex( '已撤',db_bi.names) = 0
and db_bi.class2 in ('A','B','C','O')
and closeif <> 1
and flags_bi in('1','2','3','4')
and CMD..cmd_xjjx_sale.endprice <> 0
group by db_bi.class2
) a
order by class2";
$query = mssql_query($sql);
while($row=mssql_fetch_array($query)){
print_r($row);
echo "<br><br>";
}?>结果
不要 php 查询的是在用的库(数值大些),而控制台查询的是备份的库
and charindex( '已撤',db_bi.names) = 0
这一个条件在php程序查询数据库时没起作用,换成 not like也不行,该怎么解决呢?
". iconv('utf-8', 'gbk', '已撤')."
在导出成excel文件时我用number_format函数对数据进行了处理,然后在最后求和是调用了excel的sum函数ru
$objActSheet->setCellValue($arr[$j].$n,"=SUM($x1:$x2)");
导致求和的结果不对,如下。 请问怎么做才能保证数据格式和求和的结果正确?
而应该用单元格格式来控制,比如
$sheet->getStyle('D6')
->getNumberFormat()
->setFormatCode('#,##0');