mysql - INNER JOIN 与 SELECT STATEMENT ISSUE

Question

我希望有人能提供帮助，因为我已经尝试解决这个问题一段时间了，但遇到了一个非常小的问题。

对于下面的查询，我主要尝试执行以下操作:

从订单中选择所有股票代码。

然后，在STOCK_LOCATIONS表中找到每个股票代码存储的所有位置。

然后，从 STOCK_LOCATIONS 表中返回一个位置。

但是，我需要交叉引用 LOCATION_DATA 表。LOCATION_DATA 表中的每个位置都有一个名为 PICK 的列，并且包含 Y 或 N。我想返回带有 Y 的位置作为首选，如果不存在则返回 N，如果 PICK 列中有 Y。

我实际上需要对内部联接 LOCATION_DATA 查询中的结果进行排序，但这是不可能的。我曾尝试在 INNER JOIN 中添加一个 SELECT 语句(我相信这可能是可行的方法)，但未能使其正常工作。

欢迎任何建议。非常感谢...

SELECT
    STOCK_LOCATIONS.*,
    LOCATION_DATA.*,
    STOCK_DATA.PHYSICAL,
    ORDER_DATA.ENTRY,
    ORDER_DATA.STOCK,
    ORDER_DATA.ORDER_QTY,
    (SELECT
        COUNT(STOCK_LOCATIONS.location)
        FROM STOCK_LOCATIONS 
        WHERE ORDER_DATA.STOCK = STOCK_LOCATIONS.sku
        GROUP BY STOCK_LOCATIONS.sku
        HAVING COUNT(STOCK_LOCATIONS.sku) > 1
    ) AS RowCount
FROM ORDER_DATA
INNER JOIN STOCK_DATA
    ON ORDER_DATA.STOCK = STOCK_DATA.STOCK_CODE             
INNER JOIN STOCK_LOCATIONS
    ON ORDER_DATA.STOCK = STOCK_LOCATIONS.sku
INNER JOIN LOCATION_DATA
    ON STOCK_LOCATIONS.location = LOCATION_DATA.location
WHERE ORDER_DATA.ORDER_REF = '21254'    
GROUP BY ORDER_DATA.STOCK
ORDER BY LOCATION_DATA.pick_order ASC

这是一些示例数据

以及所需输出的示例

STOCK_LOCATIONS     
sku         location    
123456      A1  
123457      A2  
123459      C1  
123456      B1  
123457      B2  


LOCATION_DATA       
location    pick    
A1          Y   
A2          Y   
B1          N   
B2          N   
C1          N   


STOCK_DATA      
STOCK_CODE  PHYSICAL    
123456      10  
123457      0   
123459      100 


ORDER_DATA      
ENTRY   STOCK       ORDER_QTY
1       123456      10
2       123457      2
3       123459      1


RETURN DATA     
STOCK_CODE  ENTRY   LOCATION    PICK    ORDER_QTY   PHYSICAL    
123456      1       A1          Y       10          10
123457      2       A2          Y       2           0
123459      3       C1          N       1           100

这是一个更新的尝试，它抛出与未知表相关的错误:

// OPTION 1

SELECT
    STOCK_LOCATIONS.*,
    LOCATION_DATA.*,
    STOCK_DATA.PHYSICAL,
    ORDER_DATA.ENTRY,
    ORDER_DATA.STOCK,
    ORDER_DATA.ORDER_QTY,
    (SELECT
        COUNT(STOCK_LOCATIONS.location)
        FROM STOCK_LOCATIONS 
        WHERE ORDER_DATA.STOCK = STOCK_LOCATIONS.sku
        GROUP BY STOCK_LOCATIONS.sku
        HAVING COUNT(STOCK_LOCATIONS.sku) > 1
    ) AS RowCount,
    (SELECT 
        CASE
            WHEN dat1.pick IS NOT NULL THEN loc1.location
            ELSE loc2.location 
        END
    ) AS location

FROM ORDER_DATA
INNER JOIN STOCK_DATA
    ON ORDER_DATA.STOCK = STOCK_DATA.STOCK_CODE

LEFT JOIN STOCK_LOCATIONS loc1
    ON ORDER_DATA.STOCK = loc1.sku

LEFT JOIN LOCATION_DATA dat1
    ON loc1.location = dat1.location
    AND dat1.pick = 'Y'

LEFT JOIN STOCK_LOCATIONS loc2
    ON ORDER_DATA.STOCK = loc2.sku

LEFT JOIN STOCK_LOCATIONS dat2
    ON loc2.location = dat2.location
    AND dat1.pick = 'N'

WHERE ORDER_DATA.ORDER_REF = '".$_GET_q."'  
GROUP BY ORDER_DATA.STOCK
ORDER BY LOCATION_DATA.pick_order ASC

// OPTION 2

SELECT
    STOCK_LOCATIONS.*,
    LOCATION_DATA.*,
    STOCK_DATA.PHYSICAL,
    ORDER_DATA.ENTRY,
    ORDER_DATA.STOCK,
    ORDER_DATA.ORDER_QTY,
    (SELECT
        COUNT(STOCK_LOCATIONS.location)
        FROM STOCK_LOCATIONS 
        WHERE ORDER_DATA.STOCK = STOCK_LOCATIONS.sku
        GROUP BY STOCK_LOCATIONS.sku
        HAVING COUNT(STOCK_LOCATIONS.sku) > 1
    ) AS RowCount

FROM ORDER_DATA
INNER JOIN STOCK_DATA
    ON ORDER_DATA.STOCK = STOCK_DATA.STOCK_CODE

JOIN STOCK_LOCATIONS loc1
    ON ORDER_DATA.STOCK = loc1.sku

JOIN LOCATION_DATA dat1
    ON loc1.location = dat1.location 
    AND dat1.pick = CASE WHEN EXISTS
    (SELECT
        *
        FROM STOCK_LOCATIONS loc2
        JOIN LOCATION_DATA dat2
            ON loc2.location = dat2.location
            AND dat1.pick = 'Y'
        WHERE ORDER_DATA.STOCK = loc2.sku
    )
    THEN 'Y' ELSE 'N' END

WHERE ORDER_DATA.ORDER_REF = '".$_GET_q."'  
GROUP BY ORDER_DATA.STOCK
ORDER BY LOCATION_DATA.pick_order ASC

// OPTION 3

SELECT
    STOCK_LOCATIONS.*,
    LOCATION_DATA.*,
    STOCK_DATA.PHYSICAL,
    ORDER_DATA.ENTRY,
    ORDER_DATA.STOCK,
    ORDER_DATA.ORDER_QTY,

    (SELECT
        COUNT(STOCK_LOCATIONS.location)
        FROM STOCK_LOCATIONS 
        WHERE ORDER_DATA.STOCK = STOCK_LOCATIONS.sku
        GROUP BY STOCK_LOCATIONS.sku
        HAVING COUNT(STOCK_LOCATIONS.sku) > 1
    ) AS RowCount,

    (SELECT 
            STOCK_LOCATIONS.location
            FROM STOCK_LOCATIONS 
            INNER JOIN LOCATION_DATA
                ON STOCK_LOCATIONS.location = LOCATION_DATA.location
            WHERE ORDER_DATA.STOCK = STOCK_LOCATIONS.sku
            ORDER BY pick DESC, rand()
            LIMIT 1
    ) AS location

FROM ORDER_DATA

INNER JOIN STOCK_DATA
    ON ORDER_DATA.STOCK = STOCK_DATA.STOCK_CODE             

INNER JOIN STOCK_LOCATIONS
    ON ORDER_DATA.STOCK = STOCK_LOCATIONS.sku

INNER JOIN LOCATION_DATA
    ON STOCK_LOCATIONS.location = LOCATION_DATA.location

WHERE ORDER_DATA.ORDER_REF = '".$_GET_q."'  
GROUP BY ORDER_DATA.STOCK
ORDER BY LOCATION_DATA.pick_order ASC   

Answer 1

您可以加入位置表两次，一次是针对 pick = 'Y' 的行，一次是针对 pick = 'N' 的行。然后你可以使用 case 语句来选择第一个，但回退到第二个:

SELECT  case
        when dat1.pick is not null then loc1.location
        else loc2.location 
        end as Location
....
LEFT JOIN 
        STOCK_LOCATIONS loc1
ON      ORDER_DATA.STOCK = loc1.sku
LEFT JOIN
        LOCATION_DATA dat1
ON      loc1.location = dat1.location
        AND dat1.pick = 'Y'
LEFT JOIN
        STOCK_LOCATIONS loc2
ON      ORDER_DATA.STOCK = loc2.sku
LEFT JOIN
        LOCATION_DATA dat2
ON      loc2.location = dat2.location
        AND dat1.pick = 'N'

另一个选项是带有子查询的连接条件。子查询可以在 pick = 'Y' 存在时查找位置，否则返回 pick = 'N'。

JOIN    STOCK_LOCATIONS loc1
ON      ORDER_DATA.STOCK = loc1.sku
JOIN    LOCATION_DATA dat1
ON      loc1.location = dat1.location 
        AND dat1.pick = case when exists
        (
        select  *
        from    STOCK_LOCATIONS loc2
        JOIN    LOCATION_DATA dat2
        ON      loc2.location = dat2.location
                AND dat1.pick = 'Y'
        WHERE   ORDER_DATA.STOCK = loc2.sku
        )
        then 'Y' else 'N' end