您现在的位置是：首页 >技术交流 >【MySQL】单表获取库存数网站首页技术交流

【MySQL】单表获取库存数

花北城 2024-07-13 12:01:02
简介【MySQL】单表获取库存数

系列文章

C#底层库–MySQLBuilder脚本构建类（select、insert、update、in、带条件的SQL自动生成）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/129179216

C#底层库–MySQL数据库操作辅助类（推荐阅读）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/126886379

C#底层库–SQLite的使用（小型、本地数据库）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/123666958

【提高编程效率】Excel数据批量导入数据库
本文链接：https://blog.csdn.net/youcheng_ge/article/details/126427323

【Oracle】数据库服务器限制ip访问
本文链接：https://blog.csdn.net/youcheng_ge/article/details/122220930

【Oracle】Excel导入数据教程
本文链接：https://blog.csdn.net/youcheng_ge/article/details/118722756

【Oracle】数据库还原教程_数据泵
本文链接：https://blog.csdn.net/youcheng_ge/article/details/118054855

【SQL】如何查询表字段并识别主键
本文链接：https://blog.csdn.net/youcheng_ge/article/details/110820405

【SQL】outer apply的用法
本文链接：https://blog.csdn.net/youcheng_ge/article/details/79903489

【SQL】多表连接重复数据处理
本文链接：https://blog.csdn.net/youcheng_ge/article/details/79903619

MySQL安装教程（详细）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/126037520

MySQL卸载教程（详细）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/129279265

【MySQL】group by分类汇总，如何增加“总计”字段?
本文链接：https://blog.csdn.net/youcheng_ge/article/details/128217837

【MySQL】WITH CHECK OPTION的用法
本文链接：https://blog.csdn.net/youcheng_ge/article/details/128147196

【MySQL】使用存储过程插入千万级数据如何提升效率？
本文链接：https://blog.csdn.net/youcheng_ge/article/details/77728189

【MySQL】数据库表行列转置的实现
本文链接：https://blog.csdn.net/youcheng_ge/article/details/77625052

【MySQL】查询中，NULL值转换为空字符串
本文链接：https://blog.csdn.net/youcheng_ge/article/details/130381878

文章目录

前言

本专栏为【数据库】，主要介绍SQL的功能与特点、SQL数据定义语言（表、视图、索引、约束）、SQL数据操作语言（数据检索、数据插入、数据删除、数据更新）、创建与删除触发器、SQL数据控制语言（安全性和授权、事务处理）以及嵌入式SQL。
如果你对本专辑感兴趣，持续关注吧。大家有任何问题，也可以评论区反馈，私信我。
在这里插入图片描述

一、技术介绍

由于子公司人员偷懒，用友U8存货编码，一对多。多个不同规格型号的物料，对应一个物料。然而导致一个新问题，模具无法按不同规格，分类显示库存数，只有一个总计库存数。
故而需要采用SQL，采用GROUP BY按规格型号分类汇总，SUM小计数量，实时显示库存数，由于数据量过多，尽可能我们采用交运算，列出必要字段，不要采用select *。

二、测试用例

2.1 数据准备

模具信息表


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for 模具信息表
-- ----------------------------
DROP TABLE IF EXISTS `模具信息表`;
CREATE TABLE `模具信息表`  (
  `AutoID` bigint(20) NOT NULL AUTO_INCREMENT,
  `公司编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `工厂名称` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '一期',
  `存货编码` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `采购订单编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `领用申请单编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `销售订单编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具供应商编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具类型` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具材质` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具出厂编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具出厂规格` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具出厂直径` decimal(10, 4) NULL DEFAULT 0.0000,
  `模具编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模链编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具规格` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模芯规格` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `粒度` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `状态描述` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `库存状态` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `仓库编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `货位编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `设备编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `下盘编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `下盘规格` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `累计公里数` decimal(10, 3) NULL DEFAULT 0.000,
  `行动` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具打标时间` datetime NULL DEFAULT '1000-01-01 00:00:00',
  `模具打标录入时间` datetime NULL DEFAULT '1000-01-01 00:00:00',
  `模具打标操作人编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具打标备注` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具检验时间` datetime NULL DEFAULT '1000-01-01 00:00:00',
  `模具检验录入时间` datetime NULL DEFAULT '1000-01-01 00:00:00',
  `模具检验人编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `异常类型` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `特殊检验说明` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具检验备注` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `最小直径` decimal(10, 4) NULL DEFAULT 0.0000,
  `最大直径` decimal(10, 4) NULL DEFAULT 0.0000,
  `平均直径` decimal(10, 4) NULL DEFAULT 0.0000,
  `椭圆度` decimal(10, 4) NULL DEFAULT 0.0000,
  `椭圆率` decimal(10, 3) NULL DEFAULT 0.000,
  `左定径长度` decimal(10, 3) NULL DEFAULT 0.000,
  `右定径长度` decimal(10, 3) NULL DEFAULT 0.000,
  `定径长度` decimal(10, 3) NULL DEFAULT 0.000,
  `有效值` decimal(10, 3) NULL DEFAULT 0.000,
  `定径区AR` decimal(10, 3) NULL DEFAULT 0.000,
  `左压缩角` decimal(10, 3) NULL DEFAULT 0.000,
  `右压缩角` decimal(10, 3) NULL DEFAULT 0.000,
  `压缩角` decimal(10, 3) NULL DEFAULT 0.000,
  `压缩区AR1` decimal(10, 3) NULL DEFAULT 0.000,
  `压缩区AR2` decimal(10, 3) NULL DEFAULT 0.000,
  `孔偏角X` decimal(10, 3) NULL DEFAULT 0.000,
  `孔偏角Y` decimal(10, 3) NULL DEFAULT 0.000,
  `倍率` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0',
  `检验结果` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具首次入库时间` datetime NULL DEFAULT '1000-01-01 00:00:00',
  `领用类型` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '生产领用',
  `模具领用时间` datetime NULL DEFAULT '1000-01-01 00:00:00',
  `模具领用录入时间` datetime NULL DEFAULT '1000-01-01 00:00:00',
  `模具领用人编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具领用备注` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具退库时间` datetime NULL DEFAULT '1000-01-01 00:00:00',
  `模具退库录入时间` datetime NULL DEFAULT '1000-01-01 00:00:00',
  `模具退库操作人编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具退库原因` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具退库备注` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具采购退货时间` datetime NULL DEFAULT '1000-01-01 00:00:00',
  `模具采购退货录入时间` datetime NULL DEFAULT '1000-01-01 00:00:00',
  `模具采购退货操作人编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具采购退货原因` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具采购退货备注` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具报废时间` datetime NULL DEFAULT '1000-01-01 00:00:00',
  `模具报废录入时间` datetime NULL DEFAULT '1000-01-01 00:00:00',
  `模具报废操作人编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具报废原因` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具报废备注` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  PRIMARY KEY (`AutoID`) USING BTREE,
  INDEX `AK_Key_CompanyID`(`公司编号` ASC) USING BTREE,
  INDEX `AK_Key_FactoryName`(`工厂名称` ASC) USING BTREE,
  INDEX `AK_Key_MaterialID`(`存货编码` ASC) USING BTREE,
  INDEX `AK_Key_U8POID`(`采购订单编号` ASC) USING BTREE,
  INDEX `AK_Key_U8TakeID`(`领用申请单编号` ASC) USING BTREE,
  INDEX `AK_Key_U8SOID`(`销售订单编号` ASC) USING BTREE,
  INDEX `AK_Key_SupplierID`(`模具供应商编号` ASC) USING BTREE,
  INDEX `AK_Key_ModeClass`(`模具类型` ASC) USING BTREE,
  INDEX `AK_Key_ModeMaterial`(`模具材质` ASC) USING BTREE,
  INDEX `AK_Key_ModeIniID`(`模具出厂编号` ASC) USING BTREE,
  INDEX `AK_Key_ModeIniType`(`模具出厂规格` ASC) USING BTREE,
  INDEX `AK_Key_ModeID`(`模具编号` ASC) USING BTREE,
  INDEX `AK_Key_ModeLineID`(`模链编号` ASC) USING BTREE,
  INDEX `AK_Key_ModeType`(`模具规格` ASC) USING BTREE,
  INDEX `AK_Key_ModeHeartType`(`模芯规格` ASC) USING BTREE,
  INDEX `AK_Key_State`(`状态描述` ASC) USING BTREE,
  INDEX `AK_Key_InOutState`(`库存状态` ASC) USING BTREE,
  INDEX `AK_Key_StoreID`(`仓库编号` ASC) USING BTREE,
  INDEX `AK_Key_PositionID`(`货位编号` ASC) USING BTREE,
  INDEX `AK_Key_MCID`(`设备编号` ASC) USING BTREE,
  INDEX `AK_Key_DownID`(`下盘编号` ASC) USING BTREE,
  INDEX `AK_Key_DownType`(`下盘规格` ASC) USING BTREE,
  INDEX `AK_Key_Action`(`行动` ASC) USING BTREE,
  INDEX `AK_Key_ModePrintTime`(`模具打标时间` ASC) USING BTREE,
  INDEX `AK_Key_ModeCheckTime`(`模具检验时间` ASC) USING BTREE,
  INDEX `AK_Key_NGType`(`异常类型` ASC) USING BTREE,
  INDEX `AK_Key_CheckResult`(`检验结果` ASC) USING BTREE,
  INDEX `AK_Key_FirstInTime`(`模具首次入库时间` ASC) USING BTREE,
  INDEX `AK_Key_TakeOutType`(`领用类型` ASC) USING BTREE,
  INDEX `AK_Key_TakeOutTime`(`模具领用时间` ASC) USING BTREE,
  INDEX `AK_Key_ReturnTime`(`模具退库时间` ASC) USING BTREE,
  INDEX `AK_Key_BuySendBack`(`模具采购退货时间` ASC) USING BTREE,
  INDEX `AK_Key_WasteTime`(`模具报废时间` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 92302 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '0-1-基础信息' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

模具出入库


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for 模具出入库表
-- ----------------------------
DROP TABLE IF EXISTS `模具出入库表`;
CREATE TABLE `模具出入库表`  (
  `AutoID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `公司编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `采购订单编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `产成品入库单编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `领料申请单编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `销售订单编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `出入库单据编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `出入库类型` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '蓝字入库',
  `存货编码` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `仓库编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `货位编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `模具编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `数量` decimal(10, 3) NULL DEFAULT 0.000,
  `出入库时间` datetime NULL DEFAULT '1000-01-01 00:00:00',
  `出入库录入时间` datetime NULL DEFAULT '1000-01-01 00:00:00',
  `出入库操作人编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `库管员编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `出入库备注` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  PRIMARY KEY (`AutoID`) USING BTREE,
  INDEX `AK_Key_CompanyID`(`公司编号` ASC) USING BTREE,
  INDEX `AK_Key_U8POID`(`采购订单编号` ASC) USING BTREE,
  INDEX `AK_Key_U8MakeIn`(`产成品入库单编号` ASC) USING BTREE,
  INDEX `AK_Key_U8TakeID`(`领料申请单编号` ASC) USING BTREE,
  INDEX `AK_Key_U8SOID`(`销售订单编号` ASC) USING BTREE,
  INDEX `AK_Key_InOutID`(`出入库单据编号` ASC) USING BTREE,
  INDEX `AK_Key_InOutType`(`出入库类型` ASC) USING BTREE,
  INDEX `AK_Key_MaterialID`(`存货编码` ASC) USING BTREE,
  INDEX `AK_Key_StoreID`(`仓库编号` ASC) USING BTREE,
  INDEX `AK_Key_PositionID`(`货位编号` ASC) USING BTREE,
  INDEX `AK_Key_ModeID`(`模具编号` ASC) USING BTREE,
  INDEX `AK_Key_InOutTime`(`出入库时间` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2828 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '2-3-仓库' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

物料出入库表


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for 物料出入库表
-- ----------------------------
DROP TABLE IF EXISTS `物料出入库表`;
CREATE TABLE `物料出入库表`  (
  `AutoID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `公司编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `采购订单编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `产成品入库单编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `领料申请单编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `销售订单编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `出入库类型` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '蓝字入库',
  `存货编码` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `仓库编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `货位编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `数量` decimal(10, 3) NULL DEFAULT 0.000,
  `出入库时间` datetime NULL DEFAULT '1000-01-01 00:00:00',
  `出入库录入时间` datetime NULL DEFAULT '1000-01-01 00:00:00',
  `出入库操作人编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `库管员编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `出入库备注` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  PRIMARY KEY (`AutoID`) USING BTREE,
  INDEX `AK_Key_CompanyID`(`公司编号` ASC) USING BTREE,
  INDEX `AK_Key_U8POID`(`采购订单编号` ASC) USING BTREE,
  INDEX `AK_Key_U8MakeIn`(`产成品入库单编号` ASC) USING BTREE,
  INDEX `AK_Key_U8TakeID`(`领料申请单编号` ASC) USING BTREE,
  INDEX `AK_Key_U8SOID`(`销售订单编号` ASC) USING BTREE,
  INDEX `AK_Key_InOutType`(`出入库类型` ASC) USING BTREE,
  INDEX `AK_Key_MaterialID`(`存货编码` ASC) USING BTREE,
  INDEX `AK_Key_StoreID`(`仓库编号` ASC) USING BTREE,
  INDEX `AK_Key_PositionID`(`货位编号` ASC) USING BTREE,
  INDEX `AK_Key_InOutTime`(`出入库时间` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '2-3-仓库' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

物料库存表


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for 物料库存表
-- ----------------------------
DROP TABLE IF EXISTS `物料库存表`;
CREATE TABLE `物料库存表`  (
  `AutoID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `公司编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `存货编码` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `仓库编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `货位编号` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空',
  `库存数量` decimal(10, 3) NULL DEFAULT 0.000,
  PRIMARY KEY (`AutoID`) USING BTREE,
  INDEX `AK_Key_CompanyID`(`公司编号` ASC) USING BTREE,
  INDEX `AK_Key_MaterialID`(`存货编码` ASC) USING BTREE,
  INDEX `AK_Key_StoreID`(`仓库编号` ASC) USING BTREE,
  INDEX `AK_Key_PositionID`(`货位编号` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '2-3-仓库' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

三、用法总结

由于子公司人员偷懒，用友U8存货编码，一对多。多个不同规格型号的物料，对应一个物料。然而导致一个新问题，模具无法按不同规格，分类显示库存数，只有一个总计库存数。

select MAX(模具信息表.存货编码) AS '存货编码',
MAX(模具信息表.模具供应商编号) AS '模具供应商编号',
MAX(模具信息表.模具类型) AS '模具类型',
MAX(模具信息表.模具材质) AS '模具材质',
MAX(模具信息表.模具出厂规格) AS '模具出厂规格',
MAX(模具信息表.模具出厂直径) AS '模具出厂直径',
SUM(模具出入库表.数量) AS '数量'
FROM 模具信息表
INNER JOIN 模具出入库表
ON 模具信息表.模具编号=模具出入库表.模具编号
GROUP BY 模具信息表.存货编码,模具信息表.模具供应商编号,模具信息表.模具类型,
模具信息表.模具材质,模具信息表.模具出厂规格,模具信息表.模具出厂直径
ORDER BY 模具信息表.模具供应商编号,模具信息表.模具类型,
模具信息表.模具材质,模具信息表.模具出厂直径

在这里插入图片描述

风语者！平时喜欢研究各种技术，目前在从事后端开发工作，热爱生活、热爱工作。

站长推荐