SQL结果表，与第二个表的SET类型匹配 [英] SQL result table, match in second table SET type
问题描述
以下两个表不受任何类型的约束.
The following two tables are not liked by any type of constraint.
首先，我有一个名为subscription_plans的表，如下所示:
First i have a table called subscription_plans that looks like this:
name | price | ID
-------------------
plan_A | 9.99 | 1
Plan_B | 19.99 | 2
plan_C | 29.99 | 3
我还有第二个表，称为pricing_offers. subscription_plan_ID是类型SET的a，并且只能包含与
I have a second table called pricing_offers. The subscription_plan_ID is a of type SET and can only contain values that match the ID's of the subscription_plans.ID (column from the above table). This table looks like this:
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
我正在尝试查询以从第一个表中选择所有内容(所有字段*)，并从第二个表中选择所有名称，结果行应如下所示:
I'm trying to do a query to select everything (all fields *) from the first table and all names from the second table and the resulting rows should look like this:
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中，并将其值插入匹配的响应行中.
The idea being that it should, for each row in the subscription_plans table, look ID field. Then go trough the second table and see what rows contain in the subscription_plan_ID, the ID of the row above. Gather those into a field caller p_o_name and insert its values to the matching response rows.
我尝试这样做:
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
注意:我得到所有行的响应，但是我只是在这里放了第一个以举例说明区别.
Note: i get a response with all rows, but i just put the first one here to exemplify the difference.
现在，虽然我可以在PHP页面上的响应中进行处理，但我想知道是否可以让数据库引擎输出所需的结果. 我需要在表之间创建一种约束类型吗?如果是这样，我该怎么办?我将不胜感激能帮助我达到预期的输出结果(甚至是该问题的更好标题！).
Now, while i could do the processing in the response on my PHP page, i'm interested in knowing if i get the DB engine to output my desired result. Do i need to create a type of constraint between the tables? If so how would i do it? I would be grateful for any help that would help me get to my proffered output result (even a better title for the question!).
如果有任何不清楚的地方，请告诉我，我将予以澄清.
If there are any unclear points, please let me know and i will clarify them.
推荐答案
联结/相交表用法示例.
Example of junction/intersect table usage.
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
基本上，您可以从联结表中插入和删除(在此示例中，确实没有很好的更新).
Basically you insert and delete from the junction table (no good really updating ever in this example).
干净而快速的连接，而不必弄乱没有索引的缓慢而笨拙的集
Clean and fast joins without having to mess with slow, unwieldy sets without indexes
没有人能再骑威尔伯·庞尼(Wilbur the Poney)吗?然后
No one can ride the Wilbur the Poney anymore? Then
delete from so_junction
where offerId in (select id from pricing_offers where name='poney ride')
如有任何疑问，请询问
祝你好运！
这篇关于SQL结果表，与第二个表的SET类型匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
