mysql - 交叉连接并将值插入到 SQL 中的表中

Question

我有一个如下的 SQL 表：

item    | subitem    |   stage    |   id     | 
-----------------------------------------------
i1      | s1         |  picked    |    1     |
i1      | s2         |  shipped   |    1     |
i2      | s4         |  picked    |    2     |
i3      | s10        | shipped    |    2     |
i3      | s11        | eligible   |    0     |
i4      | s2         |not eligible|    0     |
i1      | s1         |  picked    |    3     |
i1      | s2         |  picked    |    3     |

我希望输出如下所示：

item1|subitem1|item2|subitem2|pair_volume|item1pick|item1ship|item2pick|item2ship|
----------------------------------------------------------------------------------
i1   |   s1   |  i1 | s2     | 2         |  2      | 0       | 2       | 1       |
i1   | s1     | i2  | s4     | 1         |1        | 0       | 1       | 1       |  

....
.....
....

本质上，我想在这里做的是：

我想首先item, subitem与自身进行交叉连接，所以我有item1,subitem1和item2,的所有可能组合subitem2。

以下是阶段的定义方式。如果一个阶段是合格的，那么它就是合格的，而如果一个阶段被选中，则意味着该项目既符合条件又被选中。如果某个阶段已发货，则意味着该项目符合条件、已挑选并已发货。只提到了最后一个阶段。

现在，对于每一item1, subitem1 - item2, subitem2对，我想计算id这对发生并具有阶段的会话数，(eligible, shipped, picked)并将值填充到pair_volume. 例如，(i1,s1)-(i1,s2)pair 出现两次（一次在id1 中，一次在id3 中），并且在这两个id会话中，pair 中的两个项目都是eligible（从picked和暗示的shipped）阶段。在这对符合条件的配对出现的 2 次中，item1 被拣选、item2 被拣选、item1 发货和 item2 发货了多少次？这就是我要解决的问题。

在交叉加入之前我可以做得很好。但我不知道如何将值插入到交叉连接表中。任何帮助深表感谢！

这是我到目前为止所拥有的：

select combination.item1, combination.subitem1, combination.item2, 
combination.subitem2,
    (
    select tmp.item1, tmp.subitem1, tmp.item2, tmp.subitem2,
    case when ((tmp.item1 = tmp.item2) and 
    (tmp.subitem1 = tmp.subitem2)) then 'TRUE' else 
    'FALSE' end as indicator from
        (
        select distinct item as item1, subitem as subitem2
        from old
        cross join
        select distinct item as item2, subitem as subitem2 
        from old
        ) tmp
    ) combination
where combination.indicator = 'FALSE'

直到交叉连接的查询工作正常。但我不知道如何进一步计算额外的列。非常感谢任何帮助，谢谢！

标签： mysqlsql