有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 */;
我用的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 */;
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;
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.应该还有更好的办法,还有楼主你的全部数据是多少条?