mysql – SQL结果表,匹配第二个表的SET类型
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql – SQL结果表,匹配第二个表的SET类型,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4830字,纯文字阅读大概需要7分钟。
内容图文
任何类型的约束都不喜欢以下两个表.
首先,我有一个名为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页面上的响应中进行处理,但我有兴趣知道我是否让数据库引擎输出我想要的结果.
我是否需要在表之间创建一种约束?如果是这样我怎么办?我将非常感谢任何帮助我获得我提供的输出结果的帮助(甚至更好的标题!).
如果有任何不明确的地方,请告诉我,我会澄清它们.
解决方法:
结点/交叉表用法示例.
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
基本上你从联结表插入和删除(在这个例子中没有真正的更新).
清洁和快速连接,而不必陷入没有索引的缓慢,笨拙的集合
没有人可以再乘坐威尔伯的Poney了吗?然后
delete from so_junction
where offerId in (select id from pricing_offers where name='poney ride')
问你是否有任何问题.
还有祝你好运!
内容总结
以上是互联网集市为您收集整理的mysql – SQL结果表,匹配第二个表的SET类型全部内容,希望文章能够帮你解决mysql – SQL结果表,匹配第二个表的SET类型所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。