如何在 MySQL 中表示交叉应用和拆分字符串 [英] How to represent Cross Apply and Split String in MySQL

问题描述

对于某些背景，我正在尝试创建一个包含多个食谱的数据库.但是，有必要将各个成分与它们最初来自的配方相关联.

例如，我有一张包含所有单独成分的表格.

还有一张桌子，用来储存食谱，减去成分.

现在，我找到了

如果您需要进一步解释它是如何工作的，请发表评论.

For some background, what i'm trying to do create a database that contains multiple recipes. However, it's necessary for the individual ingredients to be linked to the recipe they originally came from.

For instance, I have table containing all the individual ingredients.

And a table where the recipes are stored, minus the ingredients.

Now, i've found this article which covers how to split strings using T-SQL XML Commands, and the code that is used to do so is:

SELECT
  Books.BookId,
  Books.Book,
  BookAuthors.AuthorId,
  BookAuthors.Author
FROM Books
CROSS APPLY dbo.split(Books.Authors,',') split
INNER JOIN BookAuthors ON BookAuthors.AuthorId = split.val

The result i'm looking for would be very similar to this:

However, CROSS APPLY etc only works on MS SQL Server and my question is:

Is it possible to achieve the same, or very similar effect using MySQL?

Thanks for any help.

解决方案

This should quite match what you're trying to get:

SELECT
  Books.BookId,
  Books.Book,
  BookAuthors.AuthorId,
  BookAuthors.Author
FROM Books
  LEFT JOIN BookAuthors ON (find_in_set(BookAuthors.AuthorId, Books.Authors) <> 0)

Found this article very helpful: MySQL query finding values in a comma separated string

Leave a comment if you need further explanation how it works.

这篇关于如何在 MySQL 中表示交叉应用和拆分字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！