今天有人在群里问库存的事，顺手写了一个求库存的，Mysql版本

建三张表，goods，goods_in，goods_out，分别代表商品表，商品进货表，商品出库表

DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `goods_title` varchar(255) DEFAULT NULL,
  `price_in` decimal(20,2) DEFAULT NULL,
  `price_out` decimal(20,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of goods
-- ----------------------------
INSERT INTO `goods` VALUES ('1', 'A', '80.00', '100.00');
INSERT INTO `goods` VALUES ('2', 'B', '89.00', '100.00');
INSERT INTO `goods` VALUES ('3', 'C', '50.00', '80.00');
INSERT INTO `goods` VALUES ('4', null, null, null);

-- ----------------------------
-- Table structure for goods_in
-- ----------------------------
DROP TABLE IF EXISTS `goods_in`;
CREATE TABLE `goods_in` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `goods_id` int(11) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of goods_in
-- ----------------------------
INSERT INTO `goods_in` VALUES ('1', '1', '90');
INSERT INTO `goods_in` VALUES ('2', '2', '300');
INSERT INTO `goods_in` VALUES ('3', '3', '100');
INSERT INTO `goods_in` VALUES ('4', '1', '50');
INSERT INTO `goods_in` VALUES ('5', '3', '30');

-- ----------------------------
-- Table structure for goods_out
-- ----------------------------
DROP TABLE IF EXISTS `goods_out`;
CREATE TABLE `goods_out` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `goods_id` int(11) DEFAULT NULL,
  `amount` int(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of goods_out
-- ----------------------------
INSERT INTO `goods_out` VALUES ('1', '1', '3');
INSERT INTO `goods_out` VALUES ('2', '2', '4');
INSERT INTO `goods_out` VALUES ('3', '1', '1');
INSERT INTO `goods_out` VALUES ('4', '3', '8');

 

 

下面来求库存。。。。。

 

SELECT
    t3.id,
    t3.goods_title,
    (t1.in_amount - t2.out_amount) storge_amount
FROM
    (
        SELECT
            goods_id,
            sum(amount) in_amount
        FROM
            goods_in a
        GROUP BY
            goods_id
    ) t1,
    (
        SELECT
            goods_id,
            sum(amount) out_amount
        FROM
            goods_out a
        GROUP BY
            goods_id
    ) t2,
    goods t3
WHERE
    t1.goods_id = t2.goods_id
AND t1.goods_id = t3.id