这结果是咋回事

Question

表是这样的
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)
然后执行下面的操作计算库存
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) ");
最后得到下面的结果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)

Answer 1

执行下
select 进货表.名称, SUM(进货表.数量) from \
                进货表 inner join lsb_qcmc on \
              进货表.名称 = lsb_qcmc.MCselect 销售表.名称, SUM(销售表.数量)*(-1)  from \
                 销售表 inner join lsb_qcmc on \
              销售表.名称 = lsb_qcmc.MC

Answer 2

很明显不对，电脑是数量没有，而且不应该有NULL，bb的记录也没有

Answer 3

直接给出你的 create table , inser into 及正确结果吧。