mysql - 选择具有关系的多对多数据并在结果中添加新列

Question

我有桌子Service和桌子Service_Payment，每项服务都可以使用一种或多种付款方式支付。

表Service：

service_id `PK`,
service_name,
service_fees

表Service_Payment：

service_payment_id `PK`,
service_id `FK`,
service_payment_method,
service_payment_amount

示例数据：

service_id: 1,
service_name: "Transportation",
service_fees: 1000

service_payment_id: 1,
service_id: 1,
service_payment_method: "Cash",
service_payment_amount: 300

service_payment_id: 2,
service_id: 1,
service_payment_method: "Credit Card",
service_payment_amount: 500

service_payment_id: 3,
service_id: 1,
service_payment_method: "Driver Collection",
service_payment_amount: 200

现在显示该服务有 3 种不同的付款方式（现金、信用卡和司机托收）。

我需要在同一行中根据付款方式选择所有具有相应付款的服务。

例如：

service_id: 1,
service_name: "Transportation",
service_fees: 1000,
cash_value: 300,
credit_card_value: 500,
driver_collection_value: 200,
bank_transfer_value: 0

我尝试加入表格Service_Payment，但显然我得到了重复的行。

我该如何解决这个问题？

标签： mysqlmany-to-manyrelational-database