有mtk、bcpk、cpk三个仓库表,现在要统计出三个仓库,相同产品的总数。三个仓库有相同的字段 {产品型号、产品编码}。型号编码一致,既为相同产品,这个要怎么实现?求指点。
我用的TP5框架。查询一个仓库的型号汇总这样操作的。Db::table('mtk')
    ->field('ordernum,model,figure,tp,count(model) as cnt')
    ->where('status',0)
    ->group('ordernum')
    ->order('cnt')
    ->select();
如何将三个仓库的汇总,同型号数量加在一起。
代码太长,还不能上传附件。SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;--
-- Database: `lplt`
---- ----------------------------------------------------------
-- 表的结构 `bcpk_copy1`
--CREATE TABLE IF NOT EXISTS `bcpk_copy1` (
  `id` int(10) unsigned NOT NULL COMMENT 'ID',
  `ordernum` char(32) NOT NULL COMMENT '商品编码',
  `model` char(32) NOT NULL COMMENT '型号',
  `figure` char(16) NOT NULL COMMENT '花纹',
  `tp` char(16) NOT NULL COMMENT '规格',
  `code` char(32) NOT NULL COMMENT '条形码',
  `pd` date NOT NULL COMMENT '母胎生产日期',
  `workshop` varchar(255) NOT NULL COMMENT '生产车间',
  `pdline` varchar(255) NOT NULL COMMENT '生产线',
  `lydate` date NOT NULL COMMENT '领用日期',
  `lybc` varchar(32) NOT NULL COMMENT '领用班次',
  `status` tinyint(2) unsigned NOT NULL COMMENT 'status'
) ENGINE=InnoDB AUTO_INCREMENT=1102847 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;--
-- 转存表中的数据 `bcpk_copy1`
--INSERT INTO `bcpk_copy1` (`id`, `ordernum`, `model`, `figure`, `tp`, `code`, `pd`, `workshop`, `pdline`, `lydate`, `lybc`, `status`) VALUES
(23, '03944', '235/55R18', 'LP18', '100V', '0395331530', '2017-12-01', '一车间', '1号线', '2018-11-09', 'A', 0),
(25, '03944', '235/55R18', 'LP18', '100V', '0395331352', '2017-12-01', '一车间', '1号线', '2018-11-09', 'A', 0),
(26, '03944', '235/55R18', 'LP18', '100V', '0395331464', '2017-12-01', '一车间', '1号线', '2018-11-09', 'A', 0),
(28, '03944', '235/55R18', 'LP18', '100V', '0395331388', '2017-12-01', '一车间', '1号线', '2018-11-09', 'A', 1),
(33, '03944', '235/55R18', 'LP18', '100V', '0393720995', '2017-12-01', '一车间', '1号线', '2018-11-09', 'A', 0),
(34, '03944', '235/55R18', 'LP18', '100V', '0393721046', '2017-12-01', '一车间', '1号线', '2018-11-09', 'A', 0),
(36, '03944', '235/55R18', 'LP18', '100V', '0393721014', '2017-12-01', '一车间', '1号线', '2018-11-09', 'A', 1),
(38, '03944', '235/55R18', 'LP18', '100V', '0393721121', '2017-12-01', '一车间', '1号线', '2018-11-09', 'A', 0),
(41, '03944', '235/55R18', 'LP18', '100V', '0395331337', '2017-12-01', '一车间', '1号线', '2018-11-09', 'A', 1),
(42, '03944', '235/55R18', 'LP18', '100V', '0393721161', '2017-12-01', '一车间', '1号线', '2018-11-09', 'A', 1),
(43, '03944', '235/55R18', 'LP18', '100V', '0393721204', '2017-12-01', '一车间', '1号线', '2018-11-09', 'A', 0),
(62, '03944', '235/55R18', 'LP18', '100V', '0393721220', '2017-12-01', '一车间', '1号线', '2018-11-09', 'A', 0),
(64, '03944', '235/55R18', 'LP18', '100V', '0395331427', '2017-12-01', '一车间', '1号线', '2018-11-09', 'A', 0),
(67, '03944', '235/55R18', 'LP18', '100V', '0395331377', '2017-12-01', '一车间', '1号线', '2018-11-09', 'A', 0),
(70, '03944', '235/55R18', 'LP18', '100V', '0395331473', '2017-12-01', '一车间', '1号线', '2018-11-09', 'A', 1),
(73, '03944', '235/55R18', 'LP18', '100V', '0395331353', '2017-12-01', '一车间', '1号线', '2018-11-09', 'A', 0),
(77, '03944', '235/55R18', 'LP18', '100V', '0395331547', '2017-12-01', '一车间', '1号线', '2018-11-09', 'A', 0),
(81, '03947', '245/35R20', 'LP17', '95W', '0393720996', '2017-12-01', '一车间', '1号线', '2018-11-09', 'A', 0),
(82, '03946', '235/65R17', 'LP19', '104H', '0393721047', '2017-12-01', '一车间', '1号线', '2018-11-09', 'A', 0),
(85, '03950', '245/45R18', 'LP17', '100W', '0393721033', '2017-12-01', '一车间', '1号线', '2018-11-09', 'A', 0);-- ----------------------------------------------------------
-- 表的结构 `cpk_copy1`
--CREATE TABLE IF NOT EXISTS `cpk_copy1` (
  `id` int(10) unsigned NOT NULL COMMENT 'ID',
  `ordernum` char(32) NOT NULL COMMENT '产品编码',
  `model` char(32) NOT NULL COMMENT '型号',
  `figure` char(16) NOT NULL COMMENT '花纹',
  `tp` char(16) NOT NULL COMMENT '规格',
  `code` char(32) NOT NULL COMMENT '条形码',
  `pd` date NOT NULL COMMENT 'MT生产日期',
  `rknum` int(11) NOT NULL COMMENT '入库单编号',
  `workshop` varchar(255) NOT NULL COMMENT '生产车间',
  `pdline` varchar(255) NOT NULL COMMENT '生产线',
  `bc` varchar(32) NOT NULL COMMENT '班次',
  `date` date NOT NULL COMMENT '生产日期',
  `status` tinyint(2) unsigned NOT NULL COMMENT '状态'
) ENGINE=InnoDB AUTO_INCREMENT=1102847 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;--
-- 转存表中的数据 `cpk_copy1`
--INSERT INTO `cpk_copy1` (`id`, `ordernum`, `model`, `figure`, `tp`, `code`, `pd`, `rknum`, `workshop`, `pdline`, `bc`, `date`, `status`) VALUES
(28, '03944', '235/55R18', 'LP18', '100V', '0395331388', '2017-12-01', 3248, '二车间', '1号线', 'A班', '2018-10-09', 0),
(36, '03944', '235/55R18', 'LP18', '100V', '0393721014', '2017-12-01', 4346, '二车间', '1号线', 'B班', '2018-10-20', 0),
(41, '03944', '235/55R18', 'LP18', '100V', '0395331337', '2017-12-01', 4346, '二车间', '1号线', 'B班', '2018-10-20', 0),
(42, '03944', '235/55R18', 'LP18', '100V', '0393721161', '2017-12-01', 3256, '二车间', '1号线', 'A班', '2018-10-11', 0),
(47, '03944', '235/55R18', 'LP18', '100V', '0393721112', '2017-12-01', 4346, '二车间', '1号线', 'B班', '2018-10-20', 0),
(53, '03944', '235/55R18', 'LP18', '100V', '0393721215', '2017-12-01', 3207, '二车间', '2号线', 'A班', '2018-09-27', 0),
(56, '03944', '235/55R18', 'LP18', '100V', '0393721231', '2017-12-01', 4346, '二车间', '1号线', 'B班', '2018-10-20', 0),
(58, '03944', '235/55R18', 'LP18', '100V', '0393721184', '2017-12-01', 3256, '二车间', '1号线', 'A班', '2018-10-11', 0),
(70, '03944', '235/55R18', 'LP18', '100V', '0395331473', '2017-12-01', 9993, '二车间', '2号线', 'B班', '2018-09-30', 0),
(87, '03946', '235/65R17', 'LP19', '104H', '0393721139', '2017-12-01', 4346, '二车间', '1号线', 'B班', '2018-10-20', 0),
(120,'03947', '245/35R20', 'LP17', '95W',  '0393721075', '2017-12-01', 4220, '二车间', '4号线', 'A班', '2018-09-30', 0),
(126,'03950', '245/45R18', 'LP17', '100W', '0395331346', '2017-12-01', 5934, '二车间', '3号线', 'A班', '2018-09-18', 0);-- ----------------------------------------------------------
-- 表的结构 `mtk_copy1`
--CREATE TABLE IF NOT EXISTS `mtk_copy1` (
  `id` int(10) unsigned NOT NULL COMMENT 'ID',
  `ordernum` char(32) NOT NULL COMMENT '产品编码',
  `model` char(32) NOT NULL COMMENT '型号',
  `figure` char(16) NOT NULL COMMENT '花纹',
  `tp` char(16) NOT NULL COMMENT '规格',
  `code` char(32) NOT NULL COMMENT '条形码',
  `pd` date NOT NULL COMMENT '母胎生产日期',
  `rkdate` date NOT NULL COMMENT '入库时间',
  `status` tinyint(4) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1102847 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;--
-- 转存表中的数据 `mtk_copy1`
--INSERT INTO `mtk_copy1` (`id`, `ordernum`, `model`, `figure`, `tp`, `code`, `pd`, `rkdate`, `status`) VALUES
(96, '03944', '235/55R18', 'LP18', '100V', '0393721038', '2017-12-01', '2018-11-09', 0),
(133, '03944', '235/55R18', 'LP18', '100V', '0393721035', '2017-12-01', '2018-11-09', 0),
(160, '03944', '235/55R18', 'LP18', '100V', '0395331484', '2017-12-01', '2018-11-09', 0),
(185, '03944', '235/55R18', 'LP18', '100V', '0395331340', '2017-12-01', '2018-11-09', 0),
(513, '03944', '235/55R18', 'LP18', '100V', '0393721012', '2017-12-01', '2018-11-09', 0),
(527, '03944', '235/55R18', 'LP18', '100V', '0393721137', '2017-12-01', '2018-11-09', 0),
(572, '03946', '235/65R17', 'LP19', '104H', '0395335654', '2017-12-01', '2018-11-09', 0),
(578, '03946', '235/65R17', 'LP19', '104H', '0395335710', '2017-12-01', '2018-11-09', 0),
(593, '03946', '235/65R17', 'LP19', '104H', '0395324320', '2017-12-01', '2018-11-09', 0),
(780, '03947', '245/35R20', 'LP17', '95W', '0393849640', '2017-12-01', '2018-11-09', 0),
(809, '03947', '245/35R20', 'LP17', '95W', '0393518258', '2017-12-01', '2018-11-09', 0),
(821, '03947', '245/35R20', 'LP17', '95W', '0393518233', '2017-12-01', '2018-11-09', 0),
(985, '03950', '245/45R18', 'LP17', '100W', '0393559390', '2017-12-01', '2018-11-09', 0),
(1271, '03950', '245/45R18', 'LP17', '100W', '0393559314', '2017-12-01', '2018-11-09', 0),
(1276, '03950', '245/45R18', 'LP17', '100W', '0393559319', '2017-12-01', '2018-11-09', 0),
(1332, '03950', '245/45R18', 'LP17', '100W', '0393559286', '2017-12-01', '2018-11-09', 0),
(1355, '03958', 'P235/60R18', 'LP20', '107H', '0393629850', '2017-12-01', '2018-11-09', 0);--
-- Indexes for dumped tables
----
-- Indexes for table `bcpk_copy1`
--
ALTER TABLE `bcpk_copy1`
  ADD PRIMARY KEY (`id`) USING BTREE;--
-- Indexes for table `cpk_copy1`
--
ALTER TABLE `cpk_copy1`
  ADD PRIMARY KEY (`id`) USING BTREE;--
-- Indexes for table `mtk_copy1`
--
ALTER TABLE `mtk_copy1`
  ADD PRIMARY KEY (`id`) USING BTREE;--
-- AUTO_INCREMENT for dumped tables
----
-- AUTO_INCREMENT for table `bcpk_copy1`
--
ALTER TABLE `bcpk_copy1`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',AUTO_INCREMENT=1102847;
--
-- AUTO_INCREMENT for table `cpk_copy1`
--
ALTER TABLE `cpk_copy1`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',AUTO_INCREMENT=1102847;
--
-- AUTO_INCREMENT for table `mtk_copy1`
--
ALTER TABLE `mtk_copy1`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',AUTO_INCREMENT=1102847;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

解决方案 »

  1.   

    这样如何?
    select bcpk.ordernum,bcpk.model,
    count(bcpk.ordernum)+
    (select count(1) from cpk where cpk.ordernum=bcpk.ordernum AND cpk.model=bcpk.model)+
    (select count(1) from mtk where mtk.ordernum=bcpk.ordernum AND mtk.model=bcpk.model)
    from 
    bcpk 
    group by bcpk.ordernum, bcpk.model;
      

  2.   

    这个方法可行,可是查询效率有点低了,我这数据查询需要5秒左右才能出结果。有没有更好的办法?我用的thinkphp框架,能否用TP的视图查询解决?
      

  3.   

    1.这是我的第一个解决方案(我自己是WIN7 32位,3G内存,Mysql 5.7,比较卡): 使用了3个Unionmysql> select s,sum(s0) from (select model s ,count(id) s0 from cpk_copy1 group by model union select model s2,count(id) s3 from mtk_copy1 group by s2
     union select model s4,count(id) s5 from bcpk_copy1 group by s4) s9 group by s;
    +------------+---------+
    | s          | sum(s0) |
    +------------+---------+
    | 235/65R17  |       4 |
    | 235/55R18  |      32 |
    | 245/45R18  |       5 |
    | 245/35R20  |       4 |
    | P235/60R18 |       1 |
    +------------+---------+
    5 rows in set (0.00 sec)2.1 看到楼主说用2楼的测试要5s左右,我就把3张表,每张表扩到10W,查询时间如下:mysql>  select s,sum(s0) from (select model s ,count(id) s0 from cpk_copy1 group by model union select model s2,count(id) s3 from mtk_copy1 group by
    count(id) s5 from bcpk_copy1 group by s4) s9 group by s;
    +------------+-------+
    | s          | s0    |
    ...
    5 rows in set (4.35 sec)2.2 看来我的sql语句效率很低,然后我就在每张表的model列上创建索引(虽然知道重复列多建立索引没什么大的优化空间),还是尝试了一下: create index bcpk_model on bcpk_copy1(modle);
    共创建3个2.3 再次测试了几次,那这就是第二种方案3Union+3索引,时间缩短了一半左右!
    mysql> select s,sum(s0) from (select model s ,count(id) s0 from cpk_copy1 group by model union select model s2,count(id) s3 from mtk_copy1 group by s2    ->  union select model s4,count(id) s5 from bcpk_copy1 group by s4) s9 group by s;
    +------------+---------+
    | s          | sum(s0) |
    +------------+---------+
    | 235/55R18  |  193083 |
    | 235/65R17  |   31008 |
    | 245/35R20  |   31008 |
    | 245/45R18  |   37009 |
    | P235/60R18 |    6001 |
    +------------+---------+
    5 rows in set (2.15sec)3.应该还有更好的办法,还有楼主你的全部数据是多少条?