php - 如何使用右连接与 MYSQL 案例获取两列的数据

Question

我想获取类别定价，如果 customer_category_pricing 表中有数据，则获取该特定客户的定价。否则从类别表中获取默认价格。

我已经尝试使用 mysql 实现这个期望的结果它工作正常，但问题是，它返回两行

• hourly_amount_final 列返回更新后的价格，然后 per_day_amount_final 返回默认价格

• 然后在下一行中，hourly_amount_final 列返回默认价格，然后 per_day_amount_final 返回更新后的价格。

表:pr_categories

---------------------------------------------------------------------------------
| id | title            | hourly_amount | per_day_amount | created_at| updated_at
---------------------------------------------------------------------------------
| 1  | Power Generation | 100.00        | 200.00         | 
| 2  | Local Government | 300.00        | 400.00         |
----------------------------------------------------------

表:pr_customer_categories_pricing

------------------------------------------------------------------------------------
| id | customer_id | category_id | billing_type_id | amount | created_at | updated_at
------------------------------------------------------------------------------------
| 1  |      1      |      1      |         1       |   109  |
| 2  |      1      |      1      |         2       |   600  |
----------------------------------------------------------

表:pr_billing_types

----------------
| id | title   |
--------------
| 1  | Hourly  |
| 2  | Per Day |
----------------

这是我目前正在处理的查询:

SELECT c.id,c.title,
CASE
    WHEN (c.hourly_amount <> (SELECT ccp.amount WHERE ccp.billing_type_id = 1)) 
    THEN (SELECT ccp.amount WHERE ccp.billing_type_id = 1)
    ELSE c.hourly_amount
END
AS hourly_amount_final,

CASE
    WHEN (c.per_day_amount <> (SELECT ccp.amount WHERE ccp.billing_type_id = 2)) 
    THEN  (SELECT ccp.amount WHERE ccp.billing_type_id = 2)
    ELSE c.per_day_amount
END
AS per_day_amount_final

FROM pr_customer_category_pricing AS ccp
RIGHT JOIN pr_categories AS c
ON c.id = ccp.category_id AND ccp.customer_id = 1

在 pr_customer_category_pricing 中没有数据时的预期结果

----------------------------------------------------------------------
| id | title            | hourly_amount_final | per_day_amount_final |
----------------------------------------------------------------------
| 1  | Power Generation |      100.00         |      200.00          | 
| 2  | Local Government |      300.00         |      600.00          |
----------------------------------------------------------------------

预期结果当pr_customer_category_pricing中有数据时

----------------------------------------------------------------------
| id | title            | hourly_amount_final | per_day_amount_final |
----------------------------------------------------------------------
| 1  | Power Generation |      109.00         |      600.00          | 
| 2  | Local Government |      300.00         |      400.00          |
----------------------------------------------------------------------

我得到的实际结果:

----------------------------------------------------------------------
| id | title            | hourly_amount_final | per_day_amount_final |
----------------------------------------------------------------------
| 1  | Power Generation |      109.00         |      200.00          | 
| 1  | Power Generation |      100.00         |      600.00          |
| 2  | Local Government |      300.00         |      400.00          |
----------------------------------------------------------------------

我做错了什么？有什么建议么!帮一个小弟。 :S

Answer 1

由于每种计费类型在 pr_customer_category_pricing 表中只能有一个条目，您可以通过从 pr_customer_category_pricing 创建一个派生数据透视表来简化事情，其中​​包含每个计费类型的值billing_type_id 在单独的列中。然后，您可以简单地将派生表中的值与每个 billing_type_id 的 pr_categories 中的值COALESCE:

SELECT c.id,c.title,
       COALESCE(ccp.hourly_amount, c.hourly_amount) AS hourly_amount_final,
       COALESCE(ccp.per_day_amount, c.per_day_amount) AS per_day_amount_final
FROM (SELECT
          customer_id,
          category_id,
          MAX(CASE WHEN billing_type_id = 1 THEN amount END) AS hourly_amount,
          MAX(CASE WHEN billing_type_id = 2 THEN amount END) AS per_day_amount
     FROM pr_customer_category_pricing
     GROUP BY customer_id, category_id) AS ccp
RIGHT JOIN pr_categories AS c
ON c.id = ccp.category_id AND ccp.customer_id = 1

输出:

id  title               hourly_amount_final per_day_amount_final
1   Power Generation    109                 600
2   Local Government    300                 400

Demo on dbfiddle