21,886
社区成员
发帖
与我相关
我的任务
分享
SELECT COUNT(distinct a.productid),SUM(a.number) AS asum,a.productid AS aproductid,
a.warehouseid AS awarehouseid,
a.number AS anumber,b.alarm AS balarm,a.price AS aprice,b.unit AS bunit
FROM {$tablepre}stocksalesdate AS a,{$tablepre}productbasic AS b
WHERE a.productid=b.productbasicid and a.type!=2 and b.alarm>=a.number
GROUP BY a.productid ORDER BY a.datetime desc LIMIT ".$offset.",".$psize."
*********************************************
数据库说明
stocksalesdate 库存
productbasic 产品基本数据
**********************************************
字段说明
wljk_productbasic------------------产品基本数据
productbasicid ID
name 产品名称
number 产品编码或产品编号
supplierid 供应商
type 产品型号
sortid 产品类别
unit 计量单位
buyprice 采购价
saleprice 出售价
alarm 警戒库存
content 产品描述
image 图片上传
content1 备注1
content2 备注2
datetime 添加时间
sid 添加者
wljk_stocksalesdate----------------库存管理
stocksalesdateid ID
ok 类别 1 为产品 2 为服务
productid 产品名称ID
price 单价
number 数量
sum 总价
serveid 服务ID
supplierid 供应商ID
content 备注
warehouseid 仓库ID
type 1 采购 2销售 3初始入库(用于采购入库)
datetime 添加日期
sid 添加人ID
SELECT COUNT(distinct a.productid),SUM(a.number) AS asum,a.productid AS aproductid,
a.warehouseid AS awarehouseid,
a.number AS anumber,b.alarm AS balarm,a.price AS aprice,b.unit AS bunit
FROM {$tablepre}stocksalesdate AS a,{$tablepre}productbasic AS b
WHERE a.productid=b.productbasicid and a.type!=2 and b.alarm>=SUM(a.number)
GROUP BY a.productid ORDER BY a.datetime desc LIMIT ".$offset.",".$psize."
select COUNT(distinct a.productid),SUM(a.number) AS asum,a.productid AS aproductid,
a.warehouseid AS awarehouseid,
a.number AS anumber,b.alarm AS balarm,a.price AS aprice,b.unit AS bunit
from stocksalesdate a, productbasic b
where a.productid=b.productbasicid and a.type!=2
GROUP BY a.productid
having sum(a.number)<=sum(b.alarm)/count(b.alarm)