按照出库要求信息匹配入库的批次信息

本帖最后由 lazygc520 于 2013-08-07 09:39:09 编辑

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

整理了一下：//按照先入先出得到已入库的零件号码及产品批次
$sql = "SELECT a.cust_no, a.lotno, count( a.lotno ) FROM vp_barcode_2d_y AS a RIGHT JOIN kw_manage AS b ON a.packageno = b.packageno GROUP BY a.lotno ORDER BY a.lotno, a.cust_no ASC";
$result = mysql_query($sql);
while($v1 = mysql_fetch_array($result,MYSQL_ASSOC))
{
var_dump($v1);
}
/*
array(3) {
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count( a.lotno )"]=>
  string(3) "480"
}
array(3) {
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3218"
  ["count( a.lotno )"]=>
  string(3) "180"
}
*///按照先纳时，再到运地，再车型的顺序求得出库依赖的结果：
$sql2 = "SELECT stock_no, cust_no, mount * snp AS total FROM `stock_detail` ORDER BY arr_time, adress, c_type ASC";
$result2 = mysql_query($sql2);
while($v2 = mysql_fetch_array($result2,MYSQL_ASSOC))
{
var_dump($v2);
}
/*
array(3) {
  ["stock_no"]=
  string(13) "SU13061301011"
  ["cust_no"]=
  string(10) "237033AW0A"
  ["total"]=
  string(3) "195"
}
array(3) {
  ["stock_no"]=
  string(13) "SU13061301011"
  ["cust_no"]=
  string(10) "237033AW0A"
  ["total"]=
  string(3) "195"
}
array(3) {
  ["stock_no"]=
  string(13) "SU13061301016"
  ["cust_no"]=
  string(10) "23703JX30A"
  ["total"]=
  string(2) "15"
}
array(3) {
  ["stock_no"]=
  string(13) "SU13061301016"
  ["cust_no"]=
  string(10) "23703JX30A"
  ["total"]=
  string(2) "30"
}
array(3) {
  ["stock_no"]=
  string(13) "SU13061301016"
  ["cust_no"]=
  string(10) "23703EX80A"
  ["total"]=
  string(3) "105"
}
array(3) {
  ["stock_no"]=
  string(13) "SU13061301016"
  ["cust_no"]=
  string(10) "23703EX80A"
  ["total"]=
  string(3) "120"
}
array(3) {
  ["stock_no"]=
  string(13) "SU13061301033"
  ["cust_no"]=
  string(10) "237033AW0A"
  ["total"]=
  string(3) "210"
}
array(3) {
  ["stock_no"]=
  string(13) "SU13061301032"
  ["cust_no"]=
  string(10) "237033AW0A"
  ["total"]=
  string(3) "255"
}
*/求按照先入先出的规则，入库信息对应出库要求的结果。注：字段total是已知对应的lotno的数量。
如果用数据库的联合查询，能匹配其关系，但是不能指定数量，这样写行吗？select c.stock_no,a.packageno,a.lotno from vp_barcode_2d_y as a left join
kw_manage as b on a.packageno = b.packageno left join stock_detail as c on
b.cust_no = concat(left(c.cust_no,5),' ',right(c.cust_no,5))where
c.stock_no = 'SU13061301011' group by a.barcode order by a.lotno,a.packageno
asc

样式如下：
== 转存表中的数据 vp_barcode_2d_y

|SU13061301011|VP3.0 130219001|3207
|SU13061301011|VP3.0 130219001|3207
|SU13061301011|VP3.0 130219001|3207
|SU13061301011|VP3.0 130219001|3207
|SU13061301011|VP3.0 130219001|3207
|SU13061301011|VP3.0 130219001|3207
|SU13061301011|VP3.0 130219001|3207
|SU13061301011|VP3.0 130219001|3207
|SU13061301011|VP3.0 130219001|3207
|SU13061301011|VP3.0 130219001|3207
|SU13061301011|VP3.0 130219001|3207
|SU13061301011|VP3.0 130219001|3207
|SU13061301011|VP3.0 130219001|3207
|SU13061301011|VP3.0 130219001|3207
|SU13061301011|VP3.0 130219001|3207
|SU13061301011|VP3.0 130219002|3207
|SU13061301011|VP3.0 130219002|3207
|SU13061301011|VP3.0 130219002|3207
|SU13061301011|VP3.0 130219002|3207
|SU13061301011|VP3.0 130219002|3207
|SU13061301011|VP3.0 130219002|3207
|SU13061301011|VP3.0 130219002|3207
|SU13061301011|VP3.0 130219002|3207
|SU13061301011|VP3.0 130219002|3207
|SU13061301011|VP3.0 130219002|3207
|SU13061301011|VP3.0 130219002|3207
|SU13061301011|VP3.0 130219002|3207
|SU13061301011|VP3.0 130219002|3207
|SU13061301011|VP3.0 130219002|3207
|SU13061301011|VP3.0 130219002|3207
|SU13061301011|VP3.0 130219003|3207
|SU13061301011|VP3.0 130219003|3207
|SU13061301011|VP3.0 130219003|3207
|SU13061301011|VP3.0 130219003|3207
|SU13061301011|VP3.0 130219003|3207
|SU13061301011|VP3.0 130219003|3207
|SU13061301011|VP3.0 130219003|3207
|SU13061301011|VP3.0 130219003|3207
|SU13061301011|VP3.0 130219003|3207
|SU13061301011|VP3.0 130219003|3207
|SU13061301011|VP3.0 130219003|3207
|SU13061301011|VP3.0 130219003|3207
|SU13061301011|VP3.0 130219003|3207
|SU13061301011|VP3.0 130219003|3207
|SU13061301011|VP3.0 130219003|3207
....
//得到的结果是：显示行 0 - 29 (540 总计, 查询花费 0.0072 秒)
能不能这样处理？我已知stock_no的值，只要求得它对应的数量值(mount*snp的结果)，再通过对应的数量值的结果去入库数据里按照先入先出的顺序匹配对应的lotno。按照这个表去匹配：
select a.packageno,a.lotno from vp_barcode_2d_y as a left join
kw_manage as b on a.packageno = b.packageno left join stock_detail as c on
b.cust_no = concat(left(c.cust_no,5),' ',right(c.cust_no,5))where
c.stock_no = 'SU13061301011' group by a.barcode order by a.lotno,a.packageno
asc

样式如下：
== 转存表中的数据 vp_barcode_2d_y

|VP3.0 130219001|3207
|VP3.0 130219001|3207
|VP3.0 130219001|3207
|VP3.0 130219001|3207
|VP3.0 130219001|3207
|VP3.0 130219001|3207
|VP3.0 130219001|3207
|VP3.0 130219001|3207
|VP3.0 130219001|3207
|VP3.0 130219001|3207
|VP3.0 130219001|3207
|VP3.0 130219001|3207
|VP3.0 130219001|3207
|VP3.0 130219001|3207
|VP3.0 130219001|3207
|VP3.0 130219002|3207
|VP3.0 130219002|3207
|VP3.0 130219002|3207
|VP3.0 130219002|3207
|VP3.0 130219002|3207
|VP3.0 130219002|3207
|VP3.0 130219002|3207
|VP3.0 130219002|3207
|VP3.0 130219002|3207
|VP3.0 130219002|3207
|VP3.0 130219002|3207
|VP3.0 130219002|3207
|VP3.0 130219002|3207
|VP3.0 130219002|3207
|VP3.0 130219002|3207
|VP3.0 130219003|3207
|VP3.0 130219003|3207
|VP3.0 130219003|3207
|VP3.0 130219003|3207
|VP3.0 130219003|3207
|VP3.0 130219003|3207
|VP3.0 130219003|3207
|VP3.0 130219003|3207
|VP3.0 130219003|3207
|VP3.0 130219003|3207
|VP3.0 130219003|3207
|VP3.0 130219003|3207
|VP3.0 130219003|3207
|VP3.0 130219003|3207
|VP3.0 130219003|3207
....
//得到的结果是：显示行 0 - 29 (540 总计, 查询花费 0.0072 秒)
库存表增加一个出库字段
入库时该字段为0
出库时该字段为已出库数量
入库数量 - 出库数量 = 该批次剩余数量又
设 select * from tbl_nameid  num
1   20
2   20
3   30
SELECT * , (SELECT sum( num ) FROM tbl_name WHERE id<=a.id ) as c FROM tbl_name aid  num  c
1   20   20
2   20   40
3   30   70则 c 列可做为出库来源的依据
如果出库包装的规模为 30
那么对应的id为：
包装1 1,2
包装2 2,3
包装3 3，后续id
c列表示的是在以id排序的方向上，累加到当前记录的 num 列的和
id=1 20
id=2 20+20=40
id=3 20+20+30=70
我还有一个问题，虽然增加了这个出库数量字段，我怎么判断是哪个packageno出库？
像这样，入库的packageno对应的lotno批次及数量，导出了结果：$sql = "select a.packageno,b.cust_no,b.lotno,count(b.lotno) from `kw_manage` as a left join `vp_barcode_2d_y` as b on a.packageno = b.packageno group by b.lotno,a.packageno order by b.lotno,a.packageno asc";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
    var_dump($row);
}结果：
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219001"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219002"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219003"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219004"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219005"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219006"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219007"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219008"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219009"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219010"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219011"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219012"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219013"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219014"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219015"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219016"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219017"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219018"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219019"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219020"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219021"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219022"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219023"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219024"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219048"
  ["cust_no"]=>
  string(11) "23710 JE22A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219049"
  ["cust_no"]=>
  string(11) "23710 JE22A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219050"
  ["cust_no"]=>
  string(11) "23710 JE22A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219051"
  ["cust_no"]=>
  string(11) "23710 JE22A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219052"
  ["cust_no"]=>
  string(11) "23710 JE22A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219053"
  ["cust_no"]=>
  string(11) "23710 JE22A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219054"
  ["cust_no"]=>
  string(11) "23710 JE22A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219055"
  ["cust_no"]=>
  string(11) "23710 JE22A"
  ["lotno"]=>
  string(4) "3207"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219107"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3218"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219108"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3218"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219109"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3218"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219110"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3218"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219111"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3218"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219112"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3218"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219113"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3218"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219114"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3218"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219115"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3218"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219116"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3218"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219117"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3218"
  ["count(b.lotno)"]=>
  string(2) "15"
}
array(4) {
  ["packageno"]=>
  string(15) "VP3.0 130219118"
  ["cust_no"]=>
  string(11) "23703 3AW0A"
  ["lotno"]=>
  string(4) "3218"
  ["count(b.lotno)"]=>
  string(2) "15"
}已知stock_no为SU13061301011时的结果为195，195，这样的话怎么处理成为已知出库？SELECT stock_no, mount*snp as total FROM `stock_detail` where `stock_no` = 'SU13061301011'CREATE TABLE IF NOT EXISTS `stock_detail` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `stock_no` varchar(20) NOT NULL,
  `adress` varchar(10) NOT NULL,
  `arr_time` datetime NOT NULL,
  `c_type` varchar(20) NOT NULL,
  `pu_no` varchar(20) NOT NULL,
  `cust_no` varchar(20) NOT NULL,
  `mount` int(10) NOT NULL,
  `snp` int(10) NOT NULL,
  `c1_time` datetime NOT NULL,
  `c2_time` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB;--
-- 转存表中的数据 `stock_detail`
--INSERT INTO `stock_detail` (`stock_no`, `snp`) VALUES
('SU13061301011', 195),
('SU13061301011', 195);
出库信息缺了cust_no，现在补上：SELECT stock_no, cust_no,mount*snp as total FROM `stock_detail` where `stock_no` = 'SU13061301011'输出结果：array(3) {
  ["stock_no"]=>
  string(13) "SU13061301011"
  ["cust_no"]=>
  string(10) "237033AW0A"
  ["total"]=>
  string(3) "195"
}
array(3) {
  ["stock_no"]=>
  string(13) "SU13061301011"
  ["cust_no"]=>
  string(10) "237033AW0A"
  ["total"]=>
  string(3) "195"
}
又在贴只有你自己才能看懂的数据出库时有一个累加的过程，到达预设值就做断行处理
其实这个过程就是你最初帖子的问题，但你始终都以入库为基准，所以总是做不好
应该以出库为准：从A取n，不够再从B中取m，如此循环直到取够