这结果是咋回事
表是这样的
- 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
mysql> SELECT * FROM LSB_QCMC;+------+| MC |+------+| aa || bb |+------+2 rows in set (0.00 sec)mysql> SELECT * FROM A;+------+------+| MC | KC |+------+------+| fff | 45 || aa | 0 || NULL | NULL |+------+------+3 rows in set (0.00 sec)
[解决办法]
看着这表 哥就蛋疼，……先看看一下数据库原理先吧
[解决办法]
执行下
select 进货表.名称, SUM(进货表.数量) from \
进货表 inner join lsb_qcmc on \
进货表.名称 = lsb_qcmc.MC
select 销售表.名称, SUM(销售表.数量)*(-1) from \
销售表 inner join lsb_qcmc on \
销售表.名称 = lsb_qcmc.MC
[解决办法]
建议楼主描述一下，自己认为哪儿出了问题。
[解决办法]
[解决办法]
直接给出你的 create table , inser into 及正确结果吧。