在表格的每行创建一个分隔字符串时，选择项目的类别和SubCategory时出现问题

Question

一些上下文:数据库是Oracle。我正在尝试为表的每一行创建一个分隔字符串。分隔字符串需要包含项目的两个类别(如果可用，则始终至少有一个类别)。有3个表，ITEM、ITEM_CAT和ITEM_ITEM_CAT。ITEM表保存所有项目。ITEM_CAT表包含所有可能的项目类别。ITEM_ITEM_CAT保存项目ID和类别ID之间的所有映射，本质上是一个键值表格。

我已经创建了下面的SQL，它能够获得特定项目的分隔字符串，但我需要一个可以对整个表运行的查询。

SELECT 'ITEM'||'%#'|| outerTable.ITEM_ID ||'%#'|| 
    (SELECT midTable.item_cat_nam
     FROM
            (SELECT innerTable.item_cat_nam AS item_cat_nam, innerTable.item_id AS item_id, ROWNUM AS rn
             FROM
                    (SELECT ic.ITEM_CAT_NAM AS item_cat_nam, i.ITEM_ID AS item_id
                     FROM ITEM_CAT ic, ITEM_ITEM_CAT iic, ITEM i
                     WHERE i.ITEM_ID = iic.ITEM_ID 
                     AND iic.ITEM_CAT_CD = ic.ITEM_CAT_CD
                     AND 287484 = i.item_id
                    ) innerTable
            ) midTable
    WHERE rn = 1
    ) ||'%#'||
    (SELECT midTable.item_cat_nam
     FROM
            (SELECT innerTable.item_cat_nam AS item_cat_nam, innerTable.item_id AS item_id, ROWNUM AS rn
             FROM
                    (SELECT ic.ITEM_CAT_NAM AS item_cat_nam, i.ITEM_ID AS item_id
                     FROM ITEM_CAT ic, ITEM_ITEM_CAT iic, ITEM i
                     WHERE i.ITEM_ID = iic.ITEM_ID 
                     AND iic.ITEM_CAT_CD = ic.ITEM_CAT_CD
                     AND 287484 = i.item_id
                    ) innerTable
            ) midTable
    WHERE rn = 2
    )
FROM OFR outerTable
WHERE outerTable.ITEM_ID = 287484;

我需要能够将外部表的ITEM_ID向下传递到最后一个内部连接中。当我只需要category (通过下面的SQL语句，只需要一个inner join )，但是引入了多个category时，我可以这样做；我需要rownum (获取多个category)，然后需要更多的inner，并且我似乎不能将ITEM_ID传递给多个inner join，这就是问题所在……

SELECT 'ITEM'||'%#'|| outerTable.OFR_ID ||'%#'|| 
    (SELECT ic.ITEM_CAT_NAM
     FROM ITEM_CAT ic, ITEM_ITEM_CAT iic, ITEM i
     WHERE i.ITEM_ID = iic.ITEM_ID 
     AND iic.ITEM_CAT_CD = ic.ITEM_CAT_CD
     AND outerTable.OFR_ID = i.item_id
             AND rownum = 1
    ) innerTable
FROM OFR outerTable;

有人能帮上忙吗？

提前感谢您的帮助。

Answer 1

别担心。你需要这样的东西..。

SELECT 'ITEM' || '%#' || Item_ID || '%#' || CatName1 || '%#' || CatName2
FROM outerTable
INNER JOIN (
  SELECT
    Item_ID,
    MAX(CASE WHEN rn = 1 THEN Item_Cat_Nam ELSE NULL END) CatName1,
    MAX(CASE WHEN rn = 2 THEN Item_Cat_Nam ELSE NULL END) CatName2
  FROM (
    SELECT
      Item_ID,
      Item_Cat.Item_Cat_Nam,
      ROW_NUMBER() OVER (PARTITION BY Item_ID ORDER BY Item_ID) rn
    FROM Item
    INNER JOIN Item_Item_Cat USING (Item_ID)
    INNER JOIN Item_Cat USING (Item_Cat_Cd)
  ) GROUP BY Item_ID
) USING (Item_ID)

最里面的查询使用ROW_NUMBER函数将1、2、3等分配给为每个商品找到的每个类别。分区通过重新开始每个项目的编号为1。ORDER BY是必需的，所以我使用了Item_ID，因为嘿，为什么不呢？如果您有要排序的首选列，只需使用该列-它将用于分配行号。内部查询将输出如下所示：

Item_ID  Item_Cat_Nam  rn
-------  ------------  --
      1  Category aa    1
      1  Category xy    2
      1  Category ef    3
      2  Category xy    1
      2  Category ax    2
      3  Category ef    1

围绕最内层查询的查询使用MAX将每个Item_ID的前两个rn值展平为一行。rn=1的Item_Cat_Nam转到CatName1列，rn=2的Item_Cat_Nam转到CatName2列。当将上面显示的结果提供给它时，您将得到以下结果：

Item_ID  CatName1     CatName2
-------  -----------  -----------
      1  Category aa  Category xy  (note Category ef is rn=3 so it's ignored)
      2  Category xy  Category ax
      3  Category ef               (note only one row for Item_ID 3)

然后最外层的查询将所有内容连接在一起。

还有一件事:我使用了"JOIN ... USING“语法，因为在这种情况下，它允许您消除所有别名(innerTable、i、ic、iic、midTable等)。这纯粹是因为我使用它更舒服，所以它帮助我更快地弄清楚这一点。你应该可以自由地使用你自己的连接风格--毕竟你会被困在维护它的人身上:)