mysql - 根据价格货币不同的价格要求

Question

我有包含以下数据的元表:

+----+------------+-----------+------------+
| id | product_id | meta_key  | meta_value |
+----+------------+-----------+------------+
|  1 |          1 | currency  | USD        |
|  2 |          1 | price     | 1100       |
|  3 |          2 | currency  | PLN        |
|  4 |          2 | price     | 1300       |
|  5 |          3 | currency  | USD        |
|  6 |          3 | price     | 1200       |
| 11 |          1 | available | 1          |
| 12 |          2 | available | 1          |
| 13 |          3 | available | 0          |
+----+------------+-----------+------------+

现在我想在产品可用且价格高于 1000 时获取 product_id，这可以通过以下方式完成:

SELECT product_id
FROM meta
WHERE meta_key IN ("price", "available") 
GROUP BY product_id 
HAVING 1=1  
    AND SUM(meta_key = "price" AND CAST(meta_value AS DECIMAL(10,2))>=1000) > 0
    AND SUM(meta_key = "available" AND meta_value=1) > 0

下一步是检查产品货币，如果我想获取价格高于 1000 美元的产品，则不应返回 product_id=2。美元兑波兰兹罗提的汇率约为 3.63，因此 1300 波兰兹罗提约为 357.98 美元。

有没有办法检查产品货币然后指定不同的价格要求？如果货币为美元，则该值应高于“1000”，如果货币为“波兰兹罗提”，则该值应高于“3630”。

Answer 1

您可以使用条件聚合

SELECT product_id,
       COUNT(CASE 
                WHEN meta_key = 'currency' AND meta_value= 'USD' > 0  THEN 1 
             END) AS USD,
       COUNT(CASE 
                WHEN meta_key = 'currency' AND meta_value= 'PLN' > 0  THEN 1 
             END) AS PLN,
       SUM(CASE 
              WHEN meta_key = 'price' THEN CAST(meta_value AS DECIMAL(10,2)) ELSE 0 
           END) AS price,
       COUNT(CASE WHEN meta_key = 'available' AND meta_value=1 THEN 1 END) AS available
FROM meta
WHERE meta_key IN ('price', 'available', 'currency') 
GROUP BY product_id;

为了创建以下输出:

product_id  USD PLN price   available
-------------------------------------
1           1   0   1100,00 1
2           0   1   1300,00 1
3           1   0   1200,00 0

现在您可以轻松查询上面的派生表以获得所需的结果:

SELECT product_id
FROM (
   ... above query here ...
) AS t
WHERE available > 0 AND (USD = 1 AND price > 1000   
                        OR
                        PLN = 1 AND price > 1000*3.63); 

输出:

product_id
----------
1

Demo here