MySQL 选择行取决于特定列是否存在特定值

Question

我有一个非常具体的问题，我一直在努力解决它，但没有成功。

基本上，我想要实现的是根据货币值(value)从定价表“product_line_price”中获取最低价格。诀窍在于 product_line_price 表并不总是包含特定货币的值，在这种情况下，它必须默认选择默认的“AUD”货币。该表还包含每个产品的多个记录，具有不同的定价方案，但我只对所有这些中最便宜的(最低价格)感兴趣，对于我正在查询的特定货币。

最终目标用于产品的“分面搜索”，用户可以在其中选择价格范围以列出该特定范围内的产品。

这是我尝试过的整体查询，我认为这是最接近的尝试:

SELECT    * 
FROM      product_line 
LEFT JOIN 
          ( 
                   SELECT   idproduct_line, 
                            idprice_group, 
                            CASE 
                                     WHEN EXISTS 
                                              ( 
                                                     SELECT currency_code 
                                                     FROM   product_line_price 
                                                     WHERE  currency_code = 'GBP' 
                                                     AND    idproduct_line = product_line_price.idproduct_line) THEN Min(price)
                                     ELSE Min(price)*0.5098 
                            END AS userprice 
                   FROM     product_line_price 
                   WHERE    !deleted 
                   AND 
                            CASE 
                                     WHEN EXISTS 
                                              ( 
                                                     SELECT currency_code 
                                                     FROM   product_line_price 
                                                     WHERE  currency_code = 'GBP' 
                                                     AND    idproduct_line = product_line_price.idproduct_line) THEN currency_code = 'GBP'
                                     ELSE currency_code = 'AUD' 
                            END 
                   GROUP BY idproduct_line 
                   ORDER BY product_line_price.idproduct_line ASC) AS product_line_price 
ON        product_line.idproduct_line = product_line_price.idproduct_line 
WHERE     !product_line.deleted 
AND       product_line.idproduct_line_status = 1 
AND       product_line.idproduct_line IN 
          ( 
                 SELECT `idproduct_line` 
                 from   `product_line_has_product_category` 
                 where  `idproduct_category` = '103') 
AND       (( 
                              product_line_price.idprice_group IN 
                              ( 
                                     SELECT `idprice_group` 
                                     FROM   `price_group` 
                                     WHERE  !`deleted` 
                                     AND    `PUBLIC` 
                                     AND    `active`)) 
          AND       ( 
                              userprice <= 250)) 
ORDER BY  product_line.idproduct_line ASC

我遇到问题的子查询是这个:

SELECT   idproduct_line, 
                            idprice_group, 
                            CASE 
                                     WHEN EXISTS 
                                              ( 
                                                     SELECT currency_code 
                                                     FROM   product_line_price 
                                                     WHERE  currency_code = 'GBP' 
                                                     AND    idproduct_line = product_line_price.idproduct_line) THEN Min(price)
                                     ELSE Min(price)*0.5098 
                            END AS userprice 
                   FROM     product_line_price 
                   WHERE    !deleted 
                   AND 
                            CASE 
                                     WHEN EXISTS 
                                              ( 
                                                     SELECT currency_code 
                                                     FROM   product_line_price 
                                                     WHERE  currency_code = 'GBP' 
                                                     AND    idproduct_line = product_line_price.idproduct_line) THEN currency_code = 'GBP'
                                     ELSE currency_code = 'AUD' 
                            END 
                   GROUP BY idproduct_line 
                   ORDER BY product_line_price.idproduct_line ASC

因此，我需要能够检索 GBP 货币的值(如果存在)，否则默认为 AUD 货币(并应用货币转换)。这里的一些数字是为了测试而硬编码的，稍后将通过变量提供。

我有一种感觉，对于上述子查询，EXISTS 子查询没有正确使用 idproduct_line 值，因为当我运行它时，我只得到 GBP 结果，即使该表中有很多记录没有 GBP 结果.我说的子查询是这个:

SELECT currency_code 
FROM   product_line_price 
WHERE  currency_code = 'GBP' 
AND    idproduct_line = product_line_price.idproduct_line

谁能看出我哪里出错了？我觉得我很接近，但是那个子查询中的某些东西是错误的，SQL 不是我的强项。

编辑 1:

我主要关心的是包含 EXISTS 子句的子查询，我知道它的其余部分可以正常工作，并且提供整个查询纯粹是为了上下文目的。

您可以从以下链接下载相关表的转储和一些示例数据。示例数据包含 3 种产品的定价，一种仅采用澳元定价，一种采用澳元和英镑定价，一种采用多种货币定价。 https://www.dropbox.com/s/h1i23f5ac2jmrvn/product_line_price.sql?dl=0

我针对此数据运行的查询是:

SELECT   idproduct_line, 
                            idprice_group,
                            currency_code, 
                            CASE 
                                     WHEN EXISTS 
                                              ( 
                                                     SELECT currency_code 
                                                     FROM   product_line_price_2 
                                                     WHERE  currency_code = 'GBP' 
                                                     AND    idproduct_line = product_line_price_2.idproduct_line AND !deleted) THEN Min(price)
                                     ELSE Min(price)*0.5098 
                            END AS userprice 
                   FROM     product_line_price_2 
                   WHERE    !deleted 
                   AND 
                            CASE 
                                     WHEN EXISTS 
                                              ( 
                                                     SELECT currency_code 
                                                     FROM   product_line_price_2 
                                                     WHERE  currency_code = 'GBP' 
                                                     AND    idproduct_line = product_line_price_2.idproduct_line AND !deleted) THEN currency_code = 'GBP'
                                     ELSE currency_code = 'AUD' 
                            END 
                   GROUP BY idproduct_line 
                   ORDER BY product_line_price_2.idproduct_line ASC

我的预期输出是 3 条记录，其中 2 条以英镑定价显示最小值，1 条以澳元定价显示最小值，转换为英镑(通过查询中的“*0.5098”)。

我看到的实际输出只有 2 条以英镑定价的记录，因此它忽略了澳元定价。

Answer 1

因此，经过长时间的斗争和同事的帮助，我们采取了不同的方法并找到了解决此问题的方法。

SELECT product_line.idproduct_line, 
       Min(local_price.price)    local_userprice, 
       Min(fallback_price.price) fallback_userprice 
FROM   product_line 
       INNER JOIN product_line_has_product_category 
               ON product_line.idproduct_line = 
                  product_line_has_product_category.idproduct_line 
                  AND 103 = product_line_has_product_category.idproduct_category 
       LEFT JOIN (product_line_price local_price 
                  INNER JOIN price_group local_price_group 
                          ON local_price.idprice_group = 
                             local_price_group.idprice_group 
                             AND ! local_price_group.deleted 
                             AND local_price_group.PUBLIC 
                             AND local_price_group.active) 
              ON product_line.idproduct_line = local_price.idproduct_line 
                 AND 'GBP' = local_price.currency_code 
       LEFT JOIN (product_line_price fallback_price 
                  INNER JOIN price_group fallback_price_group 
                          ON fallback_price.idprice_group = 
                             fallback_price_group.idprice_group 
                             AND ! fallback_price_group.deleted 
                             AND fallback_price_group.PUBLIC 
                             AND fallback_price_group.active) 
              ON product_line.idproduct_line = fallback_price.idproduct_line 
                 AND 'AUD' = fallback_price.currency_code 
WHERE ! product_line.deleted 
      AND product_line.idproduct_line_status = 1 
GROUP  BY product_line.idproduct_line 
HAVING ( local_userprice IS NULL 
         AND fallback_userprice <= 250 ) 
        OR ( local_userprice <= 250 ) 
ORDER  BY product_line.idproduct_line ASC 

使用连接，交叉引用 GBP 和 AUD 的值(这给出了同一行中的 2 列价格和货币)..然后我们可以使用 HAVING 子句比较每一列，结果成功!