mysql - SQL结果表，匹配第二个表SET类型

Question

以下两个表不受任何类型的约束。

首先，我有一个名为 subscription_plans 的表，如下所示:

name   | price | ID
-------------------
plan_A | 9.99  | 1
Plan_B | 19.99 | 2
plan_C | 29.99 | 3

我有第二个表，名为 pricing_offers。 subscription_plan_ID 属于 SET 类型，并且只能包含与 subscription_plans.ID(上表中的列)的 ID 匹配的值。该表如下所示:

p_o_name      | subscription_plan_ID | ID
-----------------------------------------
free donuts   | 1                    | 1
extra sauce   | 1,2,3                | 2
pony ride     | 3                    | 3
bus fare -50% | 1,2,3                | 4

我正在尝试执行查询以选择第一个表中的所有内容(所有字段 *)以及第二个表中的所有名称，结果行应如下所示:

name   | price | p_o_name                                | ID
-------------------------------------------------------------
plan_A | 9.99  | free donuts, extra sauce, bus fare -50% | 1
Plan_B | 19.99 | extra_sauce, bus fare -50%              | 2
plan_C | 29.99 | extra_sauce, pony ride, bus fare -50%   | 3

这个想法是，对于 subscription_plans 表中的每一行，应该查看 ID 字段。然后查看第二个表，查看 subscription_plan_ID(上面行的 ID)中包含哪些行。将它们收集到字段调用者 p_o_name 中，并将其值插入到匹配的响应行中。

我尝试这样做:

SELECT subscription_plans.*, pricing_offers.name
FROM subscription_plans INNER JOIN pricing_offers ON
FIND_IN_SET(subscription_plans.ID,subscription_plan_ID)

但我得到的是:

plan_A | 9.99  | free donuts, extra sauce, bus fare -50% | 1 

这个:

plan_A | 9.99  | free donuts   | 1
plan_A | 9.99  | extra sauce   | 1
plan_A | 9.99  | bus fare -50% | 1

注意:我得到了所有行的响应，但我只是将第一行放在这里来举例说明差异。

现在，虽然我可以在 PHP 页面上的响应中进行处理，但我有兴趣知道是否让数据库引擎输出我想要的结果。我需要在表之间创建某种类型的约束吗？如果是这样我会怎么做？如果有任何帮助我获得所提供的输出结果(甚至是更好的问题标题!)，我将不胜感激。

如果有任何不清楚的地方，请告诉我，我会澄清。

Answer 1

交汇点/相交表使用示例。

create table subscription_plans
(
    id int not null auto_increment primary key, -- common practice
    name varchar(40) not null,
    description varchar(255) not null,
    price decimal(12,2) not null
    -- additional indexes:
);

create table pricing_offers
(
    id int not null auto_increment primary key, -- common practice
    name varchar(40) not null,
    description varchar(255) not null
    -- additional indexes:
);

create table so_junction
(   -- intersects mapping subscription_plans and pricing_offers
    id int not null auto_increment primary key, -- common practice
    subId int not null,
    offerId int not null,

    -- row cannot be inserted/updated if subId does not exist in parent table
    -- the fk name is completely made up
    -- parent row cannot be deleted and thus orphaning children
    CONSTRAINT fk_soj_subplans 
        FOREIGN KEY (subId)
        REFERENCES subscription_plans(id),

    -- row cannot be inserted/updated if offerId does not exist in parent table
    -- the fk name is completely made up
    -- parent row cannot be deleted and thus orphaning children
    CONSTRAINT fk_soj_priceoffer 
        FOREIGN KEY (offerId)
        REFERENCES pricing_offers(id),

    -- the below allows for only ONE combo of subId,offerId
    CONSTRAINT soj_unique_ids unique (subId,offerId)
    -- additional indexes:
);

insert into subscription_plans (name,description,price) values ('plan_A','description',9.99);
insert into subscription_plans (name,description,price) values ('plan_B','description',19.99);
insert into subscription_plans (name,description,price) values ('plan_C','description',29.99);
select * from subscription_plans;

insert into pricing_offers (name,description) values ('free donuts','you get free donuts, limit 3');
insert into pricing_offers (name,description) values ('extra sauce','extra sauce');
insert into pricing_offers (name,description) values ('poney ride','Free ride on Wilbur');
insert into pricing_offers (name,description) values ('bus fare -50%','domestic less 50');

select * from pricing_offers;

insert so_junction(subId,offerId) values (1,1); -- free donuts to plans
insert so_junction(subId,offerId) values (1,2),(2,2),(3,2); -- extra sauce to plans
insert so_junction(subId,offerId) values (3,3); -- wilbur
insert so_junction(subId,offerId) values (1,4),(2,4),(3,4); -- bus to plans
select * from so_junction;

-- try to add another of like above to so_junction
-- Error Code 1062: Duplicate entry

-- show joins of all
select s.*,p.*
from subscription_plans s
join so_junction so
on so.subId=s.id
join pricing_offers p
on p.id=so.offerId
order by s.name,p.name

-- show extra sauce intersects
select s.*,p.*
from subscription_plans s
join so_junction so
on so.subId=s.id
join pricing_offers p
on p.id=so.offerId
where p.name='extra sauce'
order by s.name,p.name

基本上，您可以从联结表中插入和删除(在此示例中没有真正的更新)。

干净、快速的连接，而不必弄乱没有索引的缓慢、笨拙的集合

没人能再骑小马威尔伯了吗？然后

delete from so_junction
where offerId in (select id from pricing_offers where name='poney ride')

如果您有任何问题，请询问。

祝你好运!