这结果是怎么回事

这结果是咋回事
表是这样的

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
[解决办法]
建议楼主描述一下，自己认为哪儿出了问题。
[解决办法]

探讨

表是这样的
SQL code
mysql> DESCRIBE 进货表;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+------……
最后得到下面的结果
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)

[解决办法]
直接给出你的 create table , inser into 及正确结果吧。

查看更多 下一篇