在mysql中将INNER-JOIN与UNION ALL和另一个表一起使用

Question

对于询问问题，我正在尝试使用SUBSTRING_INDEX和UNION ALL将多个字段按公共分隔“”拆分，我已经从其他堆栈溢出答案中成功地完成了这一任务。

在另一个示例中，我还设法内部联接了另一个表(product_info)

问题是当两者结合在一起的时候。我不熟悉联合(或者说内部连接)。所以要达到预期的效果一直很困难。

有些字段包含多个项目，据我所知，这本身就是管理不善。其中NITM包含项目，其他字段包含以空格分隔的每个相应项目的数量和价格。

表‘salesimport’中的代码示例：

    ID  |       NITM        |  NQTY  |    SALE_PRICE     |
    1   |  CAP LIGHT CHAIR  | 1 1 2  |  2.99 4.99 44.99  |
    2   |      LIGHT        |   2    |       4.99        |
    3   |      CHAIR        |   4    |      44.99        |

表‘product_info’中的代码示例：

    ID  |    PROD_ID   |  UNIT_COST  |  SUPPLIER  |
    25  |    CAP       |    1.00     |    X&Y     |
    87  |    LIGHT     |    1.23     |    X&Y     |
    79  |    CHAIR     |    9.00     |  JONES_CO  |

只需将第一个spit方法与SUBSTRING_INDEX和UNION ALL一起使用，订单中的每个项目都会输入到新行，例如ID1、1、1、2、3等。

但当我尝试添加以下内容时：

    INNER JOIN product_info
    ON salesimport.NITM = product_info.PROD_ID

订单行再次变得重复，类似于下面的示例，其中每个项目都获得了每个排列。

    ID  |    ITEM    |  QTY  |    PRICE     |  UNIT_COST  |  SUPPLIER |
    1   |    CAP     |   1   |     2.99     |    1.00     |    X&Y    |
    1   |    CAP     |   1   |     2.99     |    1.23     |  JONES_CO |
    1   |   LIGHT    |   1   |     4.99     |    1.00     |    X&Y    |
    1   |   LIGHT    |   1   |     4.99     |    1.23     |  JONES_CO |

以下是我的工作代码：

SELECT *,
       Substring_index(Substring_index(nitm, ' ', n.digit + 1), ' ', -1)
       AS ITEM,
       Substring_index(Substring_index(nqty, ' ', n.digit + 1), ' ', -1)
       AS QTY,
       Substring_index(Substring_index(sale_price, ' ', n.digit + 1), ' ', -1)
       AS PRICE,
       Format(Substring_index(Substring_index(nqty, ' ', n.digit + 1), ' ', -1)
              *
              Substring_index(Substring_index(sale_price, ' ', n.digit + 1), ' '
              , -1),
       2)
       AS SUBTOTAL,
FROM   salesimport
       INNER JOIN (SELECT 0 digit
                   UNION ALL
                   SELECT 1
                   UNION ALL
                   SELECT 2
                   UNION ALL
                   SELECT 3
                   UNION ALL
                   SELECT 4) AS n
               ON Length(Replace(nitm, ' ', '')) <= Length(nitm) - n.digit

以上代码示例：

    ID  |    ITEM    |  QTY  |    PRICE     |
    1   |    CAP     |   1   |     2.99     |
    1   |   LIGHT    |   1   |     4.99     |
    1   |   CHAIR    |   2   |    44.99     |
    2   |   LIGHT    |   2   |     4.99     |
    3   |   CHAIR    |   4   |    44.99     |

这是我希望最终输出的样子：

    ID  |    ITEM    |  QTY  |    PRICE     |  UNIT_COST  |  SUPPLIER |
    1   |    CAP     |   1   |     2.99     |    1.00     |    X&Y    |
    1   |   LIGHT    |   1   |     4.99     |    1.23     |    X&Y    |
    1   |   CHAIR    |   2   |    44.99     |    9.00     |  JONES_CO |
    2   |   LIGHT    |   2   |     4.99     |    1.23     |    X&Y    |
    3   |   CHAIR    |   4   |    44.99     |    9.00     |  JONES_CO |

如有任何错误，敬请谅解。

Answer 1

如果时间允许，我宁愿规范化数据，而不是利用低效的字符串操作。阅读：Is storing a delimited list in a database column really that bad?

无论如何，在您的情况下，您可以在此处使用JOIN的Derived Table：

SELECT *
FROM   product_info AS pi
       JOIN (SELECT *,
                    Substring_index(Substring_index(nitm, ' ', n.digit + 1), ' '
                    , -1)
                                                   AS ITEM,
                    Substring_index(Substring_index(nqty, ' ', n.digit + 1), ' '
                    , -1)
                                                   AS QTY,
                    Substring_index(Substring_index(sale_price, ' ', n.digit + 1
                                    ), ' ',
                    -1)
                                                   AS PRICE,
                    Format(Substring_index(Substring_index(nqty, ' ',
                                           n.digit + 1), ' '
                           , -1)
                           *
                           Substring_index(Substring_index(sale_price, ' ',
                                           n.digit + 1
                                           ),
                           ' ', -1),
                    2)
                                                   AS SUBTOTAL,
             FROM   salesimport
                    INNER JOIN (SELECT 0 digit
                                UNION ALL
                                SELECT 1
                                UNION ALL
                                SELECT 2
                                UNION ALL
                                SELECT 3
                                UNION ALL
                                SELECT 4) AS n
                            ON Length(Replace(nitm, ' ', '')) <=
                               Length(nitm) - n.digit
                                      ) AS dt
         ON dt.nitm = pi.prod_id