php - SQL SELECT join with union 子查询返回空结果和多个结果

Question

我有一个名为 chapters 的表，其中包含 5 个书籍字段(book1、book2 等)和五个产品字段(product1、product2 等)，以及一些其他不需要的字段，但是为了以防万一，我已经包含了每条记录唯一的 key (文件名)。

章节

|   filename |   title |   book1 | book2 |  book3 | book4 | book5 |  product1 | product2 |  product3 | product4 | product5 |
|------------|---------|---------|-------|--------|-------|-------|-----------|----------|-----------|----------|----------|
|  file1.pdf |  Title1 |   Book1 |       |        |       |       |  Product4 |          |           |          |          |
|  file2.pdf |  Title2 |   Book9 |       |        |       |       |  Product1 | Product4 |           |          |          |
|  file3.pdf |  Title3 |   Book4 |       |        |       |       |  Product5 |          |           |          |          |
|  file4.pdf |  Title4 |   Book7 |       |        |       |       |  Product1 |          |           |          |          |
|  file5.pdf |  Title5 |  Book21 |       |        |       |       | Product13 | Product9 | Product11 |          |          |
|  file6.pdf |  Title6 |   Book5 |       |        |       |       | Product23 | Product3 |           |          |          |
|  file7.pdf |  Title7 | Book675 |       |        |       |       | Product45 | Product6 |           |          |          |
|  file8.pdf |  Title8 |   Book3 | Book5 |        |       |       |  Product9 |          |           |          |          |
|  file9.pdf |  Title9 |   Book1 | Book7 | Book34 |       |       | Product77 |          |           |          |          |
| file10.pdf | Title10 |  Book22 |       |        |       |       |  Product3 |          |           |          |          |

我最终需要进行 2 次查询。第一个是获取所有可能的书籍的列表，以及任何相关产品，第二个是镜像，所有可能的产品，以及相关书籍的列表。基本一样，只是信息的镜像。这是一个理想的最终结果。

|   books |                       products |
|---------|--------------------------------|
|   Book1 |            Product4, Product77 |
|  Book21 | Product13, Product9, Product11 |
|  Book22 |                       Product3 |
|   Book3 |                       Product9 |
|  Book34 |                      Product77 |
|   Book4 |                       Product5 |
|   Book5 |  Product9, Product23, Product3 |
| Book675 |            Product45, Product6 |
|   Book7 |            Product77, Product1 |
|   Book9 |             Product1, Product4 |'

我已经尝试了很多选择(不要问多少时间)，但以下让我非常接近，但我得到空白的图书 list ，而且我得到重复的(book1 会出现两次，等等)。

SELECT booklist.books, 
concat_ws(', ', 
          IF(LENGTH(chapters.product1),chapters.product1, NULL),
          IF(LENGTH(chapters.product2),chapters.product2, NULL),
          IF(LENGTH(chapters.product3),chapters.product3, NULL),
          IF(LENGTH(chapters.product4),chapters.product4, NULL),
          IF(LENGTH(chapters.product5),chapters.product5, NULL)) 
AS products 
FROM
  (SELECT book1 AS books FROM chapters
  UNION
  SELECT book2 FROM chapters
  UNION
  SELECT book3 FROM chapters
  UNION
  SELECT book4 FROM chapters
  UNION
  SELECT book5 FROM chapters) booklist
JOIN 
  chapters ON chapters.book1 = booklist.books
  OR chapters.book2 = booklist.books
  OR chapters.book3 = booklist.books
  OR chapters.book4 = booklist.books
  OR chapters.book5 = booklist.books
ORDER BY booklist.books;

这给了我以下内容:

|   books |                       products |
|---------|--------------------------------|
|         | Product13, Product9, Product11 |
|         |                       Product1 |
|         |                       Product5 |
|         |             Product1, Product4 |
|         |                       Product3 |
|         |                       Product4 |
|         |                      Product77 |
|         |                       Product9 |
|         |            Product45, Product6 |
|         |            Product23, Product3 |
|   Book1 |                       Product4 |
|   Book1 |                      Product77 |
|  Book21 | Product13, Product9, Product11 |
|  Book22 |                       Product3 |
|   Book3 |                       Product9 |
|  Book34 |                      Product77 |
|   Book4 |                       Product5 |
|   Book5 |                       Product9 |
|   Book5 |            Product23, Product3 |
| Book675 |            Product45, Product6 |
|   Book7 |                      Product77 |
|   Book7 |                       Product1 |
|   Book9 |             Product1, Product4 |

那么最后，我该如何去除空记录并合并多本书。数据集永远不会超过 200-300 条记录。感谢您阅读到这里!

这是一个sqlfiddle

Answer 1

如果您无法规范化模式，则使用 View 进行“动态伪规范化”:

CREATE VIEW books AS 
SELECT `filename`, `book1` As `book` FROM chapters
  UNION
  SELECT `filename`, `book2` FROM chapters
  UNION
  SELECT `filename`, `book3` FROM chapters
  UNION
  SELECT `filename`, `book4` FROM chapters
  UNION
  SELECT `filename`, `book5` FROM chapters
;

CREATE VIEW products AS 
    SELECT `filename`, `product1` As `product` FROM chapters
    UNION
    SELECT `filename`, `product2` FROM chapters
    UNION
    SELECT `filename`, `product3` FROM chapters
    UNION
    SELECT `filename`, `product4` FROM chapters
    UNION
    SELECT `filename`, `product5` FROM chapters
;

然后以与规范化模式类似的方式对这些 View 进行查询，例如:

SELECT `book`,
       group_concat( `product` ) products
FROM books b
JOIN products p USING (`filename`)
WHERE b.`book` IS NOT NULL
  AND p.`product` IS NOT NULL
GROUP BY `book`
;

演示:http://sqlfiddle.com/#!9/8717d/2

但是您被警告 - 这种方法将(非常)缓慢。
您能做的最好的事情就是规范化架构。