这结果是咋回事
表是这样的
-
SQL code
mysql> DESCRIBE 进货表; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | 名称 | text | YES | | NULL | | | 单价 | double | YES | | NULL | | | 数量 | double | YES | | NULL | | | 合计 | double | YES | | NULL | | | 进货日期 | datetime | YES | | NULL | | | 进货渠道 | text | YES | | NULL | | | 柜台号 | text | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 7 rows in set (0.00 sec) mysql> DESCRIBE 销售表; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | 名称 | text | YES | | NULL | | | 单价 | double | YES | | NULL | | | 数量 | double | YES | | NULL | | | 合计 | double | YES | | NULL | | | 销售日期 | datetime | YES | | NULL | | | 进货渠道 | text | YES | | NULL | | | 柜台号 | text | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 7 rows in set (0.00 sec) mysql> SELECT * FROM 进货表; +------+------+------+------+---------------------+----------+--------+ | 名称 | 单价 | 数量 | 合计 | 进货日期 | 进货渠道 | 柜台号 | +------+------+------+------+---------------------+----------+--------+ | aa | 0 | 0 | 0 | 2012-03-29 21:04:25 | | | | bb | 0 | 0 | 0 | 2012-03-29 21:04:25 | | | | fff | 0 | 1 | 0 | 2012-03-29 21:32:03 | | | | 电脑 | 0 | 50 | 0 | 2012-03-29 22:13:35 | | | +------+------+------+------+---------------------+----------+--------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM 销售表; +------+------+------+------+---------------------+----------+--------+ | 名称 | 单价 | 数量 | 合计 | 销售日期 | 进货渠道 | 柜台号 | +------+------+------+------+---------------------+----------+--------+ | fff | 0 | 0 | 0 | 2012-03-29 21:04:42 | | | | fff | 0 | 5 | 0 | 2012-03-29 21:30:19 | | | | 电脑 | 0 | 2 | 0 | 2012-03-29 22:13:46 | | | +------+------+------+------+---------------------+----------+--------+ 3 rows in set (0.00 sec)
然后执行下面的操作计算库存
-
C/C++ code
sql.query("create table lsb_mc (MC text)"); sql.query("create table lsb_qcmc (MC text)"); sql.query("create table a (MC text, KC double)"); sql.query("insert into lsb_mc (select 名称 from 进货表)"); sql.query("insert into lsb_mc (select 名称 from 销售表)"); sql.query("insert into lsb_qcmc (select Distinct MC from LSB_MC)"); sql.query("insert into a ( select 进货表.名称, sum(进货表.数量) - sum(销售表.数量) from 进货表 INNER JOIN 销售表 ON 进货表.名称 = 销售表.名称)"); sql.query("Delete from lsb_qcmc where lsb_qcmc.MC in (select 进货表.名称 from 进货表 inner join 销售表 on 销售表.名称 = 进货表.名称)"); sql.query("insert into a ( select 进货表.名称, SUM(进货表.数量) from 进货表 inner join lsb_qcmc on 进货表.名称 = lsb_qcmc.MC)"); sql.query("insert into a ( select 销售表.名称, SUM(销售表.数量)*(-1) from 销售表 inner join lsb_qcmc on 销售表.名称 = lsb_qcmc.MC) ");
最后得到下面的结果
-
SQL code