数据表关联查询

-- 导出  表 jbtcloud_quick_purchase.brand_management 结构

CREATE TABLE IF NOT EXISTS `brand_management` (

  `brand_number` varchar(20) NOT NULL COMMENT '品牌编号（根据每天在Redis自增获取）',

  `brand_name` varchar(120) DEFAULT NULL COMMENT '品牌名称',

  `brand_image` varchar(120) DEFAULT NULL COMMENT '品牌图片',

  `sort` int(20) DEFAULT '0' COMMENT '排序权重',

  `state` tinyint(1) DEFAULT '1' COMMENT '状态',

  `add_time` bigint(20) DEFAULT NULL COMMENT '增加时间',

  `update_time` bigint(20) DEFAULT '0' COMMENT '修改时间',

  KEY `query` (`brand_number`,`sort`,`state`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='品牌管理';

-- 正在导出表  jbtcloud_quick_purchase.brand_management 的数据：~2 rows (大约)

/*!40000 ALTER TABLE `brand_management` DISABLE KEYS */;

INSERT INTO `brand_management` (`brand_number`, `brand_name`, `brand_image`, `sort`, `state`, `add_time`, `update_time`) VALUES

('1656501973988', '黑胡椒', 'http://img1-test.e-travelnote.cn/quick/taocan/2022-06-29/6947862259045699584uTbVBy.png', 3, 1, 1656499446088, 1656501973988),

('20220610000042', '威客', 'http://img1-test.e-travelnote.cn/quick/taocan/2022-06-30/6948154570963095552UhYrM1.png', 2, 1, 1656569150988, 0);

/*!40000 ALTER TABLE `brand_management` ENABLE KEYS */;

-- 导出  表 jbtcloud_quick_purchase.classification_management 结构

CREATE TABLE IF NOT EXISTS `classification_management` (

  `classification_number` varchar(20) NOT NULL COMMENT '分类编号（根据每天在Redis自增获取）',

  `classification_name` varchar(120) DEFAULT NULL COMMENT '分类名称',

  `classification_image` varchar(120) DEFAULT NULL COMMENT '分类图片',

  `sort` int(20) DEFAULT '0' COMMENT '排序权重',

  `state` tinyint(1) DEFAULT '1' COMMENT '状态',

  `add_time` bigint(20) DEFAULT NULL COMMENT '增加时间',

  `update_time` bigint(20) DEFAULT '0' COMMENT '修改时间'

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分类管理';

-- 正在导出表  jbtcloud_quick_purchase.classification_management 的数据：~2 rows (大约)

/*!40000 ALTER TABLE `classification_management` DISABLE KEYS */;

INSERT INTO `classification_management` (`classification_number`, `classification_name`, `classification_image`, `sort`, `state`, `add_time`, `update_time`) VALUES

('20220620000001', '机油养护', 'http://img1-test.e-travelnote.cn/quick/taocan/2022-06-30/6948075157886013440copnzQ.png', 1, 1, 1656550208176, 0),

('20220620000002', '智能车载', 'http://img1-test.e-travelnote.cn/quick/taocan/2022-06-30/6948075322604720128282AqL.png', 2, 1, 1656550245224, 1656569072204);

/*!40000 ALTER TABLE `classification_management` ENABLE KEYS */;

-- 导出  表 jbtcloud_quick_purchase.commodity 结构

CREATE TABLE IF NOT EXISTS `commodity` (

  `id` bigint(11) NOT NULL AUTO_INCREMENT,

  `number` varchar(20) DEFAULT NULL COMMENT '商品编号',

  `state` tinyint(1) DEFAULT '1' COMMENT '状态 0下架 1上架',

  `name` varchar(50) DEFAULT NULL COMMENT '商品名称',

  `package_exhibition_image` text COMMENT '套餐轮播图',

  `package_deatil_image` text COMMENT '套餐详情图',

  `explicate` varchar(120) DEFAULT NULL COMMENT '产品说明',

  `sold_count` int(10) DEFAULT NULL COMMENT '商品所有套餐销售总数',

  `add_time` bigint(20) DEFAULT NULL COMMENT '增加时间',

  `update_time` bigint(20) DEFAULT '0' COMMENT '修改时间',

  `add_time_str` varchar(50) DEFAULT NULL COMMENT '字符串时间',

  `update_time_str` varchar(50) DEFAULT '0' COMMENT '字符串时间',

  PRIMARY KEY (`id`),

  UNIQUE KEY `number` (`number`),

  KEY `query` (`number`,`state`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT='商品';

-- 正在导出表  jbtcloud_quick_purchase.commodity 的数据：~3 rows (大约)

/*!40000 ALTER TABLE `commodity` DISABLE KEYS */;

INSERT INTO `commodity` (`id`, `number`, `state`, `name`, `package_exhibition_image`, `package_deatil_image`, `explicate`, `sold_count`, `add_time`, `update_time`, `add_time_str`, `update_time_str`) VALUES

(2, '20220650000045', 1, '安南青97', '', '', '安南青-------------4111111', 0, 1656576729236, 0, '2022-06-30 16:12:09', '0'),

(3, '20220650000051', 1, '安南青60', '', '', '安南青-------------72', 0, 1656594024751, 0, '2022-06-30 21:00:24', '0'),

(4, '20220650000058', 1, '安南青37', '', '', '安南青-------------12', 0, 1656594138274, 0, '2022-06-30 21:02:18', '0');

/*!40000 ALTER TABLE `commodity` ENABLE KEYS */;

-- 导出  表 jbtcloud_quick_purchase.commodity_attachment 结构

CREATE TABLE IF NOT EXISTS `commodity_attachment` (

  `commodity_number` varchar(50) DEFAULT NULL COMMENT '商品编号',

  `attachment_type` int(2) DEFAULT NULL COMMENT '属性类型',

  `attachment_number` varchar(50) DEFAULT NULL COMMENT '属性编号',

  `add_time` bigint(20) DEFAULT NULL COMMENT '增加时间',

  `add_time_str` varchar(20) DEFAULT NULL COMMENT '字符串时间',

  UNIQUE KEY `uniqueKey` (`commodity_number`,`attachment_type`,`attachment_number`),

  KEY `query` (`commodity_number`,`attachment_type`,`attachment_number`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 正在导出表  jbtcloud_quick_purchase.commodity_attachment 的数据：~16 rows (大约)

/*!40000 ALTER TABLE `commodity_attachment` DISABLE KEYS */;

INSERT INTO `commodity_attachment` (`commodity_number`, `attachment_type`, `attachment_number`, `add_time`, `add_time_str`) VALUES

('20220650000051', 1, '20220620000002', 1656594024751, '2022-06-30 21:00:24'),

('20220650000051', 1, '20220620000001', 1656594024751, '2022-06-30 21:00:24'),

('20220650000051', 2, '20220610000042', 1656594024751, '2022-06-30 21:00:24'),

('20220650000051', 4, '20220640000044', 1656594024751, '2022-06-30 21:00:24'),

('20220650000051', 4, '20220640000010', 1656594024751, '2022-06-30 21:00:24'),

('20220650000051', 3, '20220630000009', 1656594024751, '2022-06-30 21:00:24'),

('20220650000051', 3, '20220630000008', 1656594024751, '2022-06-30 21:00:24'),

('20220650000058', 1, '20220620000002', 1656594138274, '2022-06-30 21:02:18'),

('20220650000058', 2, '20220610000042', 1656594138274, '2022-06-30 21:02:18'),

('20220650000058', 4, '20220640000011', 1656594138274, '2022-06-30 21:02:18'),

('20220650000058', 3, '20220630000009', 1656594138274, '2022-06-30 21:02:18'),

('20220650000058', 3, '20220630000008', 1656594138274, '2022-06-30 21:02:18'),

('20220650000045', 1, '20220620000002', 1656656979672, '2022-07-01 14:29:39'),

('20220650000045', 2, '1656501973988', 1656656979672, '2022-07-01 14:29:39'),

('20220650000045', 3, '20220630000043', 1656656979672, '2022-07-01 14:29:39'),

('20220650000045', 4, '20220640000011', 1656656979672, '2022-07-01 14:29:39');

/*!40000 ALTER TABLE `commodity_attachment` ENABLE KEYS */;

这个是建表语句以及测试数据，我想要得到根据商品编号分组得到数据，这个感觉用关联查询没办法找到on的条件

同学你好，老师这里使用同学的sql尝试了一下，没办法找到合适的关联条件来书写on语句。那么说明这个需求本身就是无法使用sql直接完成的，建议同学尝试使用Java代码对数据进行处理来代替使用sql处理。

祝学习愉快~