MySql 非常复杂的线性格式输出的分层查询

Question

嗨，MySql 爱好者和专家，

这是带有数据的数据库结构:

--
-- Table structure for table `categories`
--

CREATE TABLE IF NOT EXISTS `categories` (
  `catId` bigint(20) NOT NULL,
  `catName` varchar(80) NOT NULL,
  PRIMARY KEY  (`catId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`catId`, `catName`) VALUES
(1, 'CatX'),
(2, 'CatY'),
(3, 'CatZ');

-- --------------------------------------------------------

--
-- Table structure for table `company`
--

CREATE TABLE IF NOT EXISTS `company` (
  `companyId` bigint(20) NOT NULL,
  `companyName` varchar(80) NOT NULL,
  `catIds` varchar(500) default NULL,
  PRIMARY KEY  (`companyId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `company`
--

INSERT INTO `company` (`companyId`, `companyName`, `catIds`) VALUES
(1, 'CompanyX', '2,3'),
(2, 'CompanyY', '1'),
(3, 'CompanyZ', '2,3');

-- --------------------------------------------------------


--
-- Table structure for table `subcategories`
--

CREATE TABLE IF NOT EXISTS `subcategories` (
  `subCatId` bigint(20) NOT NULL,
  `subCatName` varchar(80) NOT NULL,
  `catIds` varchar(500) NOT NULL,
  PRIMARY KEY  (`subCatId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `subcategories`
--

INSERT INTO `subcategories` (`subCatId`, `subCatName`, `catIds`) VALUES
(1, 'SubCatH', '1,3'),
(2, 'SubCatM', '1,2'),
(3, 'SubCatN', '1,2,3');

-- --------------------------------------------------------

--
-- Table structure for table `items`
--

CREATE TABLE IF NOT EXISTS `items` (
  `itemId` bigint(20) NOT NULL,
  `itemName` varchar(80) NOT NULL,
  `subCatIds` varchar(500) NOT NULL,
  PRIMARY KEY  (`itemId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `items`
--

INSERT INTO `items` (`itemId`, `itemName`, `subCatIds`) VALUES
(1, 'Item1', '1,2'),
(2, 'Item2', '1,2,3'),
(3, 'Item3', '1,2,3'),
(4, 'Item4', '3'),
(5, 'Item5', '2');

-- --------------------------------------------------------

每个公司都生产部分类别，但 cats->subcats->items Hierarchy 在所有公司之间共享。

所以查询输出必须产生这样的结果[基于上面提供的数据]:

CompanyX -> CatY -> SubCatM -> Item1  
CompanyX -> CatY -> SubCatM -> Item2  
CompanyX -> CatY -> SubCatM -> Item3  
CompanyX -> CatY -> SubCatM -> Item5  

CompanyX -> CatY -> SubCatN -> Item2  
CompanyX -> CatY -> SubCatN -> Item3  
CompanyX -> CatY -> SubCatN -> Item4  

CompanyX -> CatZ -> SubCatH -> Item1    
CompanyX -> CatZ -> SubCatH -> Item2  
CompanyX -> CatZ -> SubCatH -> Item3  

CompanyX -> CatZ -> SubCatN -> Item2  
CompanyX -> CatZ -> SubCatN -> Item3  
CompanyX -> CatZ -> SubCatN -> Item4

CompanyY 和 CompanyZ 同样的方法

如果可能的话，所有这些都在一个没有循环的查询中完成？

[编辑]

这是我正在尝试的东西!它很远，但我在这个领域的知识不多!

SELECT 
  concat(company.companyName,' -> ', categories.catName, ' -> ',subcategories.subCatName,' -> ', items.itemName) as output FROM `company`
left outer join categories on 1=1 and FIND_IN_SET(company.catIds, 
(SELECT Distinct GROUP_CONCAT(Distinct categories.catId) FROM `categories` WHERE FIND_IN_SET(categories.catId, '1,2,3')))
left outer join subcategories on 1=1 
left outer join items on 1=1
;

谢谢，

Answer 1

免责声明:我强烈建议您不要使用此答案，并修复数据库设计。 FIND_IN_SET 会产生非常糟糕的性能，它不会比较整数，主要是因为它不能使用索引。

要回答您的问题，您的方向是正确的，只需转动 FIND_IN_SET 即可。您想要加入类别 ID 在公司类别 ID 中的位置。

以下查询产生的结果如 OP 中所示(尽管第一家公司是 company*X* 而不是 company*A*)

SELECT 
  concat(p.companyName,' -> ',
       c.catName, ' -> ',s.subCatName,' -> ',
       i.itemName) as output
FROM company p
    JOIN categories c ON FIND_IN_SET(c.catId,  p.catIds)
    JOIN subcategories s ON FIND_IN_SET(c.catId, s.catIds)
    JOIN items i ON FIND_IN_SET(s.subCatId, i.subCatIds)