sql 总计记录数与明细查询记录数不一致
select b.inv_code 材料编码,c.inv_name 材料名称,c.inv_model 材料规格,u.unit_name 单位,b.price 成本价,b.amount 数量,b.sell_price 零售单价,v.ven_name 生产厂家,c.cert_code 证件号, a.year_month 出库日期,a.iow_no 出库单号,a.conf_date 出库确认日期,dept.dept_name 领用科室,d.store_name 移出仓库
from mate_whr_detail b
inner join mate_whr_main a on a.iow_id=b.iow_id
inner join mate_inv_dict c on b.inv_code=c.inv_code
left join sys_dept dept on a.dept_code=dept.dept_code
left join sys_store_dict d1 on a.exch_store_code = d1.store_code
left join sys_store_dict d on a.store_code = d.store_code
left join sys_vendor_dict v on c.cus_code = v.ven_code
left join sys_unit u on c.unit_code = u.unit_code
where a.bus_type_code =14
and a.make_date>='2012-01-01' and a.make_date<='2012-12-31'
and is_in_out=0
and a.exch_store_code ='05'
group by a.year_month ,a.iow_no,b.inv_code,c.inv_name,c.inv_model,c.cert_code,u.unit_name,b.price,b.amount,b.sell_price,v.ven_name,a.year_month,a.iow_no,dept.dept_name ,d.store_name,a.conf_date
order by a.iow_no
select count(a.iow_id) as 条数
from mate_whr_detail b
inner join mate_whr_main a on a.iow_id=b.iow_id
inner join mate_inv_dict c on b.inv_code=c.inv_code
left join sys_dept dept on a.dept_code=dept.dept_code
left join sys_store_dict d1 on a.exch_store_code = d1.store_code
left join sys_store_dict d on a.store_code = d.store_code
left join sys_vendor_dict v on c.cus_code = v.ven_code
left join sys_unit u on c.unit_code = u.unit_code
where a.bus_type_code =14
and a.make_date>='2012-01-01' and a.make_date<='2012-12-31'
and is_in_out=0
and a.exch_store_code='05'
[解决办法]
关联查询中你用到了left join
比如:
left join sys_vendor_dict v on c.cus_code = v.ven_code
left join sys_unit u on c.unit_code = u.unit_code
这两个关联都与a表,b表没有关系,有可能在这里产生了冗余
[解决办法]
SELECT b.inv_code 材料编码 ,
c.inv_name 材料名称 ,
c.inv_model 材料规格 ,
u.unit_name 单位 ,
b.price 成本价 ,
b.amount 数量 ,
b.sell_price 零售单价 ,
v.ven_name 生产厂家 ,
c.cert_code 证件号 ,
a.year_month 出库日期 ,
a.iow_no 出库单号 ,
a.conf_date 出库确认日期 ,
dept.dept_name 领用科室 ,
d.store_name 移出仓库
FROM mate_whr_detail b
INNER JOIN mate_whr_main a ON a.iow_id = b.iow_id
INNER JOIN mate_inv_dict c ON b.inv_code = c.inv_code
LEFT JOIN sys_dept dept ON a.dept_code = dept.dept_code
LEFT JOIN sys_store_dict d1 ON a.exch_store_code = d1.store_code
LEFT JOIN sys_store_dict d ON a.store_code = d.store_code
LEFT JOIN sys_vendor_dict v ON c.cus_code = v.ven_code
LEFT JOIN sys_unit u ON c.unit_code = u.unit_code
WHERE a.bus_type_code = 14
AND a.make_date >= '2012-01-01'
AND a.make_date <= '2012-12-31'
AND is_in_out = 0
AND a.exch_store_code = '05'
GROUP BY a.year_month ,--但是楼主这儿用GROUP BY了,也就是说：GROUP BY的字段中存在重复数据
a.iow_no ,
b.inv_code ,
c.inv_name ,
c.inv_model ,
c.cert_code ,
u.unit_name ,
b.price ,
b.amount ,
b.sell_price ,
v.ven_name ,
a.year_month ,
a.iow_no ,
dept.dept_name ,
d.store_name ,
a.conf_date
ORDER BY a.iow_no
SELECT COUNT(a.iow_id) AS 条数--楼主统计的是a.iow_id的计数(如果a与其它表存在1对多关系,会有多个a.iow_id产生)
FROM mate_whr_detail b
INNER JOIN mate_whr_main a ON a.iow_id = b.iow_id
INNER JOIN mate_inv_dict c ON b.inv_code = c.inv_code
LEFT JOIN sys_dept dept ON a.dept_code = dept.dept_code
LEFT JOIN sys_store_dict d1 ON a.exch_store_code = d1.store_code
LEFT JOIN sys_store_dict d ON a.store_code = d.store_code
LEFT JOIN sys_vendor_dict v ON c.cus_code = v.ven_code
LEFT JOIN sys_unit u ON c.unit_code = u.unit_code
WHERE a.bus_type_code = 14
AND a.make_date >= '2012-01-01'
AND a.make_date <= '2012-12-31'
AND is_in_out = 0
AND a.exch_store_code = '05'
