连接多个列上的两个表，并根据每个连接拉取值

Question

我有一个表，上面有产品和前5名竞争对手，我们有数千种产品，竞争对手可能是我们自己产品的一部分。

product     comp 1  comp 2  comp 3  comp 4  comp 5
A           B       C       D       E       F

我需要为每个竞争对手提取他们的销售价值

Product Sales
A       10
B       20
C       30
D       40
E       50
F       60

为了拉取comp 1、2、3、4和5的销售额，我必须在不同的列上将product表与sales表连接5次，并且在合并(联合)所有这些表时也会出现问题，因为所有竞争对手的销售额必须不同，例如comp1 sales、Comp2 sales、Comp3 sales等等

product comp 1 comp 2 comp 3 comp 4 comp 5 comp 1 sales comp 2 sales comp 3 sales comp 4 sales  comp 5 sales
A   B   C   D   E   F   20  30  40  50  60

有没有其他方法可以连接这些表以获得所需的输出？

Answer 1

您可以使用IN条件执行单个JOIN，然后执行条件聚合：

SELECT
    p.product,
    p.comp1,
    p.comp2,
    p.comp3,
    p.comp4,
    p.comp5,
    MAX(CASE WHEN s.product = p.comp1 THEN s.sales END) comp1sales,
    MAX(CASE WHEN s.product = p.comp2 THEN s.sales END) comp2sales,
    MAX(CASE WHEN s.product = p.comp3 THEN s.sales END) comp3sales,
    MAX(CASE WHEN s.product = p.comp4 THEN s.sales END) comp4sales,
    MAX(CASE WHEN s.product = p.comp5 THEN s.sales END) comp5sales
FROM product p
INNER JOIN sales s 
    ON s.product IN (p.comp1, p.comp2, p.comp3, p.comp4, p.comp5)
GROUP BY
    p.product,
    p.comp1,
    p.comp2,
    p.comp3,
    p.comp4,
    p.comp5

Answer 2

为了避免5次连接，您可以使用UNION将列转换为行：

  SELECT
     p.product,
     p.comp
    FROM(
    SELECT
     p.product,
     compA AS comp
    FROM(
    -- this is your product table
    SELECT '1' AS product, 'A' AS compA, 'B' AS compB, 'C' AS compC
    UNION ALL
    SELECT '2' AS product, 'E' AS compA, 'F' AS compB, 'G' AS compC
    )p

    UNION ALL

    SELECT
     p.product,
     compB AS comp
    FROM(
    -- this is your product table
    SELECT '1' AS product, 'A' AS compA, 'B' AS compB, 'C' AS compC
    UNION ALL
    SELECT '2' AS product, 'E' AS compA, 'F' AS compB, 'G' AS compC
    )p

    UNION ALL

    SELECT
     p.product,
     compC AS comp
   FROM(
    -- this is your product table
    SELECT '1' AS product, 'A' AS compA, 'B' AS compB, 'C' AS compC
    UNION ALL
    SELECT '2' AS product, 'E' AS compA, 'F' AS compB, 'G' AS compC
    )p

) p
INNER JOIN (
    -- Sales table
    SELECT 'A' as comp
    UNION ALL
    SELECT 'B' as comp
    UNION ALL
    SELECT 'C' as comp
    UNION ALL
    SELECT 'D' as comp
    UNION ALL
    SELECT 'E' as comp
    UNION ALL
    SELECT 'F' as comp
    UNION ALL
    SELECT 'G' as comp
) sales ON p.comp = sales.comp;

http://sqlfiddle.com/#!9/9eecb/109833