将键值表连接到主表

oxiaedzo 于 2021-06-19 发布在 Mysql

关注(0)|答案(1)|浏览(343)

我有两张table：
费用（每笔交易1行）
费用\元数据（每对键值对应一行+每行1列费用\ id）
我当前的代码为元数据中的每个键提供了一个新行

SELECT m.id, m.amount, s.key, s.value
FROM charges AS m
INNER JOIN charges_metadata AS s ON m.id = s.charge_id
WHERE key IN ('Product', 'Plan Type', 'Payment Plan')

我想看到的是：

+----------------+--------+---------+-----------+--------------+
|       id       | amount | Product | Plan Type | Payment Plan |
+----------------+--------+---------+-----------+--------------+
| 908asd7f098sa7 |    150 | T-shirt | Main      | Monthly      |
| 80as9d8f0as9d8 |    200 | Jeans   | Regular   | Yearly       |
+----------------+--------+---------+-----------+--------------+

mysql stripe-payments

来源：https://stackoverflow.com/questions/52587684/joining-key-value-table-to-a-master-table

1条答案

按热度按时间

nfeuvbwi1#

您需要使用键透视出值，这需要聚合：

SELECT
    m.id,
    m.amount,
    MAX(CASE WHEN s.`key` = 'Product'      THEN s.value END) AS Product,
    MAX(CASE WHEN s.`key` = 'Plan Type'    THEN s.value END) AS PlanType,
    MAX(CASE WHEN s.`key` = 'Payment Plan' THEN s.value END) AS PaymentPlan
FROM charges m
INNER JOIN charges_metadata s
WHERE `key` IN ('Product', 'Plan Type', 'Payment Plan')
GROUP BY
    m.id,
    m.amount,
    s.charge_id;

赞(0）回复(0）举报 2021-06-20

我来回答

将键值表连接到主表

1条答案

相关问题

热门标签

最新问答